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;

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... 1、拷贝lib_ mysql udf_ json _parsing.dll到 mysql 目录C:\Program Files\MariaDB 5.5\lib\plugin下 2、在数据库 中 执行 DROP FUNCTION json _get; CREATE FUNCTION json _get RETURNS STRING SONAME 'lib_ mysql udf_ json _parsing.dll'; SELECT json _get('{"a":1}', 'a') => 1 SELECT json _get('{"a":1}', 'b') => NULL SELECT json _get('[1,2,3]', 2) => 3 SELECT json _get('{"a":[2]}', 'a', 0) => 2 "count": 1, "skuId": 50, "picture": "http://img.alicdn.com/bao/uploaded/i1/2455464663/TB1.u3oSFXXXXbDXFXXXXXXXXXX_!!0-item_pic.jpg", "realPrice": 0.1, "attribute.