MySQL JSON_SET() Function

In MySQL, the JSON_SET() function inserts or updates data in a JSON document and return a new JSON document. It is equivalent to a combination of JSON_INSERT() and JSON_REPLACE().

JSON_SET() Syntax

Here is the syntax of the MySQL JSON_SET() function:

JSON_SET(json, path, value[, path2, value2] ...)

Parameters

json

Required. The JSON document to modify.

path

Required. The path to set data. A valid path expression that cannot contain * or **.

value

Required. The data to set.

Return value

The JSON_SET() function inserts or updates data in a JSON document and returns a new JSON document. You can provide multiple pairs of path-value parameters to set multiple data at once.

If the specified path exists in the JSON document, update the value matching the path; if not, insert data at the corresponding path.

If value is a string, the JSON_REPLACE() function writes it to the JSON document as a JSON string. To ensure the inserted data’s data type is correct, please convert value as a JSON type value.

This function will return NULL if the JSON document or path is NULL.

There will happen an error in the following cases:

• MySQL will give an error if the parameter json is not a valid JSON document. You can use JSON_VALID() to verify the JSON document.
• If the parameter path is not a valid path expression or contains * or **, MySQL will give an error.

JSON_SET() Examples

Let’s first create a JSON document that is a JSON object:

SET @obj = '{"x": 1}';

Now let’s set some data:

SELECT JSON_SET(@obj, '$.x', '10', '$.y', '[1, 2]');

+----------------------------------------------+
| JSON_SET(@obj, '$.x', '10', '$.y', '[1, 2]') |
+----------------------------------------------+
| {"x": "10", "y": "[1, 2]"}                   |
+----------------------------------------------+

We found that although we has inserted or updated the data, there are still some problems, that is, the array [1, 2] became "[1, 2]" that is a JSON string not a JSON array.

This is because if the value parameter is a string, the JSON_SET() function writes it as a JSON string to the JSON document. Let’s look at a similar example:

SELECT JSON_SET(@obj, '$.x', '10', '$.y', '{"z": 2}');

+------------------------------------------------+
| JSON_SET(@obj, '$.x', '10', '$.y', '{"z": 2}') |
+------------------------------------------------+
| {"x": "10", "y": "{\"z\": 2}"}                 |
+------------------------------------------------+

To solve this problem, we can use the CAST() function to convert the data to JSON type, for example:

SELECT JSON_SET(@obj, '$.x', '10', '$.y', CAST('[1, 2]' AS JSON));

+------------------------------------------------------------+
| JSON_SET(@obj, '$.x', '10', '$.y', CAST('[1, 2]' AS JSON)) |
+------------------------------------------------------------+
| {"x": "10", "y": [1, 2]}                                   |
+------------------------------------------------------------+