mysql json null值处理,JSON_SET未更新MySQL中的空JSON字段

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;