Clear empty values from JSON (actually array) field in MYSQL

Question

I have a table, in this table I have a JSON field. In this field I store arrays with some data. For example:

I need to clear this field from empty values and get:

Then update the filed values.

REPLACE('[]',NULL,REPLACE('"", ','',columnJson)) like this? — Roy Bogado
– Roy Bogado, Commented Jan 28, 2020 at 9:47
I try it in this way: REPLACE('[]',NULL,REPLACE('"", ','','["a", "", "c"]')). Gives me syntax error — pepe_botika69
– pepe_botika69, Commented Jan 28, 2020 at 9:54
[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('[]',NULL,REPLACE('"", ','','["a", "", "c"]'))' at line 1. The sentence is just: REPLACE('[]',NULL,REPLACE('"", ','','["a", "", "c"]')) — pepe_botika69
– pepe_botika69, Commented Jan 28, 2020 at 10:08

Roy Bogado · Accepted Answer · 2020-01-28 11:30:13Z

1

REPLACE(REPLACE(emails,'"", ',''), '[""]','null')

Working example here in SQLfiddle

@PaulSpiegel:
Doesn't work for ["", "b", ""]

Solved:

REPLACE(REPLACE(REPLACE(emails,'"", ',''), '[""]','null'),', ""','')

answered Jan 28, 2020 at 11:03

Roy Bogado

4,4721 gold badge18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Doesn't work for ["", "b", ""] - fiddle

That wasn't an option.

If you just want something, that only works for the given sample data, then you could just hardcode the result.