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?
解决方案
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;