How to update JSON data type column in MySQL 5.7.10?
Thanks @wchiquito for pointing me right direction. I solved the problem. Here is how I did it.
mysql> select * from t1;+----------------------------------------+------+| data | id |+----------------------------------------+------+| {"key1": "value1", "key2": "VALUE2"} | 1 || {"key2": "VALUE2"} | 2 || {"key2": "VALUE2"} | 1 || {"a": "x", "b": "y", "key2": "VALUE2"} | 1 |+----------------------------------------+------+4 rows in set (0.00 sec)mysql> update t1 set data = JSON_SET(data, "$.key2", "I am ID2") where id = 2;Query OK, 1 row affected (0.04 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from t1;+----------------------------------------+------+| data | id |+----------------------------------------+------+| {"key1": "value1", "key2": "VALUE2"} | 1 || {"key2": "I am ID2"} | 2 || {"key2": "VALUE2"} | 1 || {"a": "x", "b": "y", "key2": "VALUE2"} | 1 |+----------------------------------------+------+4 rows in set (0.00 sec)mysql> update t1 set data = JSON_SET(data, "$.key3", "I am ID3") where id = 2;Query OK, 1 row affected (0.07 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from t1;+------------------------------------------+------+| data | id |+------------------------------------------+------+| {"key1": "value1", "key2": "VALUE2"} | 1 || {"key2": "I am ID2", "key3": "I am ID3"} | 2 || {"key2": "VALUE2"} | 1 || {"a": "x", "b": "y", "key2": "VALUE2"} | 1 |+------------------------------------------+------+4 rows in set (0.00 sec)
EDIT:If you want to add an array, use JSON_ARRAY
like
update t1 set data = JSON_SET(data, "$.key4", JSON_ARRAY('Hello','World!')) where id = 2;
Now with MySQL 5.7.22+ it is very easy and straightforward to update the whole fragment of json (multiple key values, or even nested) in a single query like this:
update t1 set data = JSON_MERGE_PATCH(`data`, '{"key2": "I am ID2", "key3": "I am ID3"}') where id = 2;
Hope it helps someone visiting this page and looking for a "better" JSON_SET
:)More about JSON_MERGE_PATCH
here:https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-merge-patch