JSON_SET isn't updating null JSON field in MySQL

Question

I have a nullable JSON MySQL 5.7 field which I am finding almost impossible to get working.

Example query:

UPDATE `json_test` SET `data` = JSON_SET(`data`, '$.a', 1)

If the field data is NULL already, it won't update.

If it's { "a" : 2 }, then it'll update correctly to 1. I need it to set if not set already, which is what JSON_SET is supposed to do.

Any ideas what's happening?

Community · Accepted Answer · 2020-06-20 09:12:55Z

19

1) An alternative is to check for null and return an valid empty JSON set ({}) to JSON_SET in those situations, so it just puts in the new data.

UPDATE json_test SET data = JSON_SET(IFNULL(data,'{}' ), '$.a', 1)

2) Finally, another option would be for the data specification to have a default value of {}, ex.

`dataJson TEXT DEFAULT '{}',`

I prefer the first option I presented as I like leaving fields NULL until I need them to have data but then I expect them to start packing in the JSON data immediately!

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 21, 2019 at 22:38

codeHot

4815 silver badges6 bronze badges

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

1 Comment

Nandakishore Over a year ago

This is brilliant. Its simple trick but very very helpful. Now with this, we can simply update existing or insert a new one using JSON_SET.

John · Accepted Answer · 2020-07-11 16:32:14Z

9

Updating the entire table for that is an overkill and changing the table definition as well.
This should have no noticeable performance impact:

UPDATE `json_test` SET `data` = JSON_SET(COALESCE(`data`,'{}'), '$.a', 1)

Explanation:
JSON_SET needs a full processing of the column in any case, so it will be evaluated for validity, parsed, etc.
The COALESCE changes a NULL field to an empty JSON object, so the json will be valid and the SET will be successful.
You probably won't be able to measure a performance difference.

answered Jul 11, 2020 at 16:32

John

7,9143 gold badges58 silver badges57 bronze badges

Comments

e4c5 · Accepted Answer · 2016-12-20 05:43:12Z

7

it's not supposed to work with nulls

Otherwise, a path/value pair for a nonexisting path in the document is ignored and has no effect.

Now mysql doesn't let you use a subquery on the same table that's being updated, but you could probably stil solve this with an UPDATE JOIN using CASE/WHEN but I am too lazy so I leave you with a two query solution.

UPDATE `json_test` SET `data` = JSON_SET(`data`, '$.a', 1) WHERE data IS NOT NULL;

UPDATE `json_test` SET `data` = JSON_OBJECT('$.a', 1) WHERE data IS NULL;

answered Dec 20, 2016 at 5:43

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

1 Comment

Jonathan Bird Over a year ago

That's great. The only bug you have in that is JSON_OBJECT you simply set 'a', 1, you don't access using $.a as you're setting the key.

Collectives™ on Stack Overflow

JSON_SET isn't updating null JSON field in MySQL

3 Answers 3

1) An alternative is to check for null and return an valid empty JSON set ({}) to JSON_SET in those situations, so it just puts in the new data.

2) Finally, another option would be for the data specification to have a default value of {}, ex.

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1) An alternative is to check for null and return an valid empty JSON set ({}) to JSON_SET in those situations, so it just puts in the new data.

2) Finally, another option would be for the data specification to have a default value of {}, ex.

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related