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?

Why did you add data like this? it should be separate column for key and value. — Pathik Vejani, Jan 25 '16 at 06:29
@PathikVejani Like I mentioned I am trying to utilize mysql 5.7 which provides native json data type. My JSON can be huge. I can not add columns for every key value pair. — ʞɹᴉʞ ǝʌɐp, Jan 25 '16 at 06:34
Check: [12.16 JSON Functions](https://dev.mysql.com/doc/refman/5.7/en/json-functions.html). — wchiquito, Jan 25 '16 at 06:46
@wchiquito Thanks for pointing me in right direction. I have posted my solution, though all credit should go to you. — ʞɹᴉʞ ǝʌɐp, Jan 25 '16 at 07:38
If you just want to change a key, see [MySQL Update or Rename a Key in JSON](https://stackoverflow.com/q/53433285/10204932) — Deepam Gupta, Jan 28 '22 at 08:38

score 164 · Accepted Answer · edited Jun 18 '20 at 21:19

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;

This update working fine for single "key: value" . Then how to update multiple key value?....... — siva, Nov 27 '18 at 06:39
@siva like this - JSON_SET(@j, '$.key1', 10, '$.key2', '[true, false]') — Snigdha Batra, Apr 04 '19 at 07:59
how if I want the key from `"$.(:passedData)"` ? I want to update the key based on the value I get from client, it seems that I cannot pass any value thru :passedData — krehwell, Jul 15 '20 at 17:23
probably better to use set data = IFNULL(JSON_SET(data, "$.key2", "I am ID2"), data) — nvvetal, May 18 '22 at 08:20
what happened if I don't have any `key` in my column? for e.g: I have this : `["31","32"]` — Shibbir, May 20 '22 at 09:04
Ah, this worked a treat. It also works with 2D-3D JSON, just supply a daisy-chained key ex $.key1.key2.key3. — Mayur Chauhan, Jul 13 '23 at 06:56

score 20 · Answer 2 · answered Jul 22 '20 at 14:02

20

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

answered Jul 22 '20 at 14:02

boryn

726
9
10

3

Also it's worth mentioning that this update does not work if the current `data` is null (and as far as I remember prior to MySQL 8.0.13 you could not set default values to JSON columns). So you might want to do something like `update t1 set data = JSON_MERGE_PATCH(COALESCE(\`data\`, "{}"), '{"key2": "I am ID2", "key3": "I am ID3"}') where id = 2;` – Nimrod Shory Jul 15 '21 at 09:41

How to update JSON data type column in MySQL 5.7.10?

2 Answers2

Linked

Related