How to update JSON data type column in MySQL 5.7.10?

Question

I have started using MySQL 5.7.10 recently and I am liking the native JSON Data type a lot.

But I ran into a problem when it comes to updating a JSON type value.

Questions:

Below is the table format, here I want to add 1 more key in JSON data column for t1 table. Right now I have to fetch the value modify it and Update the table. So it involves an extra SELECT statement.

I can insert 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 a work around for this?

Answer 1

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: 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_ARRAY like

update t1 set data = JSON_SET(data, "$.key4", JSON_ARRAY('Hello','World!')) where id = 2; 

Answer 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