Update operation with Mysql JSON data

2017-01-13 10:48:10来源:oschina作者:ShaoMing人点击

Since the initial value of '$.categories' would be a empty string due to a application bug, we have to remove it before appendingany item into the categories array.

UPDATE subscriber SET attributes = JSON_REMOVE(attributes, '$.categories') WHERE mail_address = 'test_user@mail.com' AND attributes->'$.categories' = '' ;

UsingJSON_MERGE becauseJSON_ARRAY_APPEND couldn'twork with a non-existing field.


UPDATE subscriber SET attributes = JSON_MERGE(attributes, '{"categories": ["mem", "boy"]}') WHERE mail_address = 'test_user@mail.com' AND attributes->'$.categories' is NULL


UPDATE subscriber SET attributes = JSON_ARRAY_APPEND(attributes, '$.categories', 'women') WHERE mail_address = 'test_user@mail.com' AND attributes->'$.categories' <> '' ;

https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台