MySQL JSON_INSERT() Function

In MySQL, the JSON_INSERT() function inserts data into a JSON document and return a new JSON document.

`JSON_INSERT()` Syntax

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

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

Parameters

json: Required. The JSON document.
path: Required. The path expression. A valid path expression that cannot contain * or **.
value: Required. The data that will be inserted.

Return value

The JSON_INSERT() function inserts data into a JSON document and returns a new JSON document. You can provide multiple pairs of path-value parameters to insert multiple data at once.

If the JSON document already have the specified path, the data will not be inserted.

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.

If path is $, the JSON_INSERT() function returns the original JSON document.

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_INSERT()` Examples

Insert into array

Let’s first create a JSON document to demonstrate the following example:

SET @array = '[1, [2, 3], {"a": [4, 5]}]';

Let’s insert two elements into the array:

SELECT JSON_INSERT(@array, '$[0]', 0, '$[3]', 6);

Here, we want to insert 0 at the position 0 of the array and insert 6 at the position 3 of the array. Let’s take a look at the result:

+-------------------------------------------+
| JSON_INSERT(@array, '$[0]', 0, '$[3]', 6) |
+-------------------------------------------+
| [1, [2, 3], {"a": [4, 5]}, 6]             |
+-------------------------------------------+

We found that the data 0 is not inserted into the array. This is because the array already has a value at $[0], so it will not be inserted. The array has no data at $[3], so 6 is inserted at the end of the array.

You can also insert data into an array by using JSON_ARRAY_APPEND().

Insert JSON type data

In addition to inserting simple literal values, we can also insert complex JSON elements such as arrays and objects.

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

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

Now let’s insert a member y that has a value true:

SELECT JSON_INSERT(@obj, '$.y', 'true');

+----------------------------------+
| JSON_INSERT(@obj, '$.y', 'true') |
+----------------------------------+
| {"x": 1, "y": "true"}            |
+----------------------------------+

We found that true became "true". The result what we hoped should be {"x": 1, "y": true}.

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

SELECT JSON_INSERT(@obj, '$.y', '[1, 2]');

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

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

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

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

SELECT JSON_INSERT(@obj, '$.y', CAST('{"z": 2}' AS JSON));

+----------------------------------------------------+
| JSON_INSERT(@obj, '$.y', CAST('{"z": 2}' AS JSON)) |
+----------------------------------------------------+
| {"x": 1, "y": {"z": 2}}                            |
+----------------------------------------------------+

JSON_INSERT() Syntax