I recently started using MySQL 5.7.10 and I really like the native JSON data type.
But I have a problem when updating JSON type value.
question:
The following is the table format, here I want to add 1 key to thet1table in thedatacolumn. Now I have to get the value modify it and update the table. So it involves an additionalSELECTstatement.
I can insert it like this
INSERT INTO t1 values ('{"key2":"value2"}', 1); mysql> select * from t1; +--------------------+------+ | data | id | +--------------------+------+ | {"key1": "value1"} | 1 | | {"key2": "value2"} | 2 | | {"key2": "value2"} | 1 | +--------------------+------+ 3 rows in set (0.00 sec) mysql>Show create table t1; +-------+------------------------------------------------------------- -------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `data` json DEFAULT NULL, `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+--------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Is there any solution?
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Now with MySQL 5.7.22, it is very simple and straightforward to update an entire piece of json (multiple key values, 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 visiting this page and looking for a "better"
JSON_SET:) More information aboutJSON_MERGE_PATCHcan be found here:https://dev.mysql .com/doc/refman/5.7/en/json-modification-functions.html#function_json-merge-patchThanks to @wchiquito for pointing me in the right direction. I solved this problem. This is how I do 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: 0 mysql> 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: 0 mysql> 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_ARRAYlikeupdate t1 set data = JSON_SET(data, "$.key4", JSON_ARRAY('Hello','World!')) where id = 2;