MySQL JSON_SET, JSON_INSERT, JSON_REPLACE and JSON

You can change a JSON document inside the database, one value at a time, with an ordinary UPDATE. No need to read the whole document into the application, change it and write it back (which also risks overwriting somebody else’s change).

query.sql

SQL

CREATE TABLE settings (
    user_id INT PRIMARY KEY,
    prefs JSON NOT NULL
);
INSERT INTO settings VALUES
    (1, '{"theme": "light", "language": "en", "notifications": {"email": true, "sms": false}, "favorites": ["sql", "php"]}');

The three setters

Function	If the path exists	If the path does not exist
`JSON_SET`	Replaces the value	Adds it
`JSON_INSERT`	Leaves it unchanged	Adds it
`JSON_REPLACE`	Replaces the value	Does nothing

query.sql

SQL

UPDATE settings
SET prefs = JSON_SET(prefs, '$.theme', 'dark', '$.timezone', 'Asia/Kolkata')
WHERE user_id = 1;
SELECT prefs FROM settings;

Output

OUTPUT

+----------------------------------------------------------------------------------------------------------------------------------------------+
| prefs                                                                                                                                        |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| {"theme": "dark", "language": "en", "timezone": "Asia/Kolkata", "favorites": ["sql", "php"], "notifications": {"sms": false, "email": true}} |
+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

JSON_SET changed the existing theme and added a new timezone. You can pass several path-value pairs in one call.

query.sql

SQL

1
2
3

SELECT JSON_INSERT('{"a": 1}', '$.a', 99, '$.b', 2) AS insert_keeps_existing,
       JSON_REPLACE('{"a": 1}', '$.a', 99, '$.b', 2) AS replace_ignores_missing,
       JSON_SET('{"a": 1}', '$.a', 99, '$.b', 2) AS set_does_both;

Output

OUTPUT

+-----------------------+-------------------------+-------------------+
| insert_keeps_existing | replace_ignores_missing | set_does_both     |
+-----------------------+-------------------------+-------------------+
| {"a": 1, "b": 2}      | {"a": 99}               | {"a": 99, "b": 2} |
+-----------------------+-------------------------+-------------------+
1 row in set

Nested paths

query.sql

SQL

1
2

UPDATE settings SET prefs = JSON_SET(prefs, '$.notifications.sms', true, '$.notifications.push', true) WHERE user_id = 1;
SELECT prefs->'$.notifications' AS notifications FROM settings;

Output

OUTPUT

+--------------------------------------------+
| notifications                              |
+--------------------------------------------+
| {"sms": true, "push": true, "email": true} |
+--------------------------------------------+
1 row in set

A missing parent is not created: JSON_SET(doc, '$.a.b.c', 1) does nothing if $.a.b does not exist. Create the object first (JSON_SET(doc, '$.a', JSON_OBJECT('b', JSON_OBJECT('c', 1)))).

Values: strings, numbers, JSON

A value you pass is turned into JSON by its SQL type: a string becomes a JSON string, a number a JSON number. To store a JSON array or object, pass a JSON value (from JSON_ARRAY, JSON_OBJECT or CAST(... AS JSON)), not text:

query.sql

SQL

SELECT JSON_SET('{}', '$.as_text', '[1, 2]', '$.as_json', JSON_ARRAY(1, 2), '$.as_cast', CAST('[1, 2]' AS JSON)) AS result;

Output

OUTPUT

+-------------------------------------------------------------+
| result                                                      |
+-------------------------------------------------------------+
| {"as_cast": [1, 2], "as_json": [1, 2], "as_text": "[1, 2]"} |
+-------------------------------------------------------------+
1 row in set

as_text became a string that looks like an array (it is wrapped in quotes, with escapes). as_json and as_cast are real arrays. This is a very common mistake.

Remove

query.sql

SQL

1
2

UPDATE settings SET prefs = JSON_REMOVE(prefs, '$.language', '$.notifications.push') WHERE user_id = 1;
SELECT prefs FROM settings;

Output

OUTPUT

+---------------------------------------------------------------------------------------------------------------------------+
| prefs                                                                                                                     |
+---------------------------------------------------------------------------------------------------------------------------+
| {"theme": "dark", "timezone": "Asia/Kolkata", "favorites": ["sql", "php"], "notifications": {"sms": true, "email": true}} |
+---------------------------------------------------------------------------------------------------------------------------+
1 row in set

Arrays

query.sql

SQL

1
2
3

UPDATE settings SET prefs = JSON_ARRAY_APPEND(prefs, '$.favorites', 'mysql') WHERE user_id = 1;
UPDATE settings SET prefs = JSON_ARRAY_INSERT(prefs, '$.favorites[0]', 'html') WHERE user_id = 1;
SELECT prefs->'$.favorites' AS favorites FROM settings;

Output

OUTPUT

+---------------------------------+
| favorites                       |
+---------------------------------+
| ["html", "sql", "php", "mysql"] |
+---------------------------------+
1 row in set

Removing one element of an array uses JSON_REMOVE with an index ('$.favorites[1]'). To remove by value, find its position with JSON_SEARCH (for strings), or rebuild the array with JSON_TABLE and JSON_ARRAYAGG.

query.sql

SQL

1
2

UPDATE settings SET prefs = JSON_REMOVE(prefs, JSON_UNQUOTE(JSON_SEARCH(prefs, 'one', 'php')));
SELECT prefs->'$.favorites' AS favorites FROM settings;

Output

OUTPUT

+--------------------------+
| favorites                |
+--------------------------+
| ["html", "sql", "mysql"] |
+--------------------------+
1 row in set

Merging documents

Function	Behaviour
`JSON_MERGE_PATCH(a, b)`	RFC 7396 merge: keys of `b` replace those in `a`, and a `null` in `b` removes the key. The one to use for “apply these changes”
`JSON_MERGE_PRESERVE(a, b)`	Keeps both: same keys become arrays of both values. (`JSON_MERGE` is its deprecated old name)

query.sql

SQL

1
2

SELECT JSON_MERGE_PATCH('{"a": 1, "b": 2, "c": 3}', '{"b": 20, "c": null, "d": 4}') AS patch,
       JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"b": 20, "d": 4}') AS preserve;

Output

OUTPUT

+---------------------------+--------------------------------+
| patch                     | preserve                       |
+---------------------------+--------------------------------+
| {"a": 1, "b": 20, "d": 4} | {"a": 1, "b": [2, 20], "d": 4} |
+---------------------------+--------------------------------+
1 row in set

JSON_MERGE_PATCH is a clean way to apply a partial update from an API request: it changes the keys you send, removes those set to null, and leaves the others alone.

query.sql

SQL

1
2

UPDATE settings SET prefs = JSON_MERGE_PATCH(prefs, '{"theme": "solarized", "notifications": {"email": false}}') WHERE user_id = 1;
SELECT prefs FROM settings;

Output

OUTPUT

+-------------------------------------------------------------------------------------------------------------------------------------------+
| prefs                                                                                                                                     |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| {"theme": "solarized", "timezone": "Asia/Kolkata", "favorites": ["html", "sql", "mysql"], "notifications": {"sms": true, "email": false}} |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

Note that JSON_MERGE_PATCH merges objects recursively but replaces arrays whole.

Partial updates are efficient

For JSON columns, MySQL can update part of a document in place (a “partial update”) when you use JSON_SET, JSON_REPLACE or JSON_REMOVE and the new value is not larger than the old one, and binary logging uses the binlog_row_value_options = PARTIAL_JSON option. For big documents, this saves a lot of I/O compared with rewriting the whole value.

Concurrency

UPDATE settings SET prefs = JSON_SET(prefs, '$.counter', JSON_VALUE(prefs, '$.counter' RETURNING UNSIGNED) + 1) is atomic: the read and the write happen in one statement, so two sessions cannot lose each other’s change. Reading the document in the application, changing it and writing it back is not atomic, and overwrites concurrent changes (Updating Under Concurrency).

query.sql

SQL

1
2
3

UPDATE settings SET prefs = JSON_SET(prefs, '$.visits', COALESCE(JSON_VALUE(prefs, '$.visits' RETURNING UNSIGNED), 0) + 1) WHERE user_id = 1;
UPDATE settings SET prefs = JSON_SET(prefs, '$.visits', COALESCE(JSON_VALUE(prefs, '$.visits' RETURNING UNSIGNED), 0) + 1) WHERE user_id = 1;
SELECT prefs->'$.visits' AS visits FROM settings;

Output

OUTPUT

+--------+
| visits |
+--------+
| 2      |
+--------+
1 row in set

dangerous

Do not store the same fact in two places

If a value is also a real column, keep one source of truth. Updating a copy inside JSON and forgetting the column (or the reverse) is how data drifts apart.