JSON_ARRAY_INSERT

Description

This function inserts an array into a document and returns the modified document.

Syntax

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

Remarks

The json_doc parameter specifies the JSON document, and path is the path parameter. If any parameter is NULL, the function returns NULL.

The following situations will result in an error:

• The json_doc parameter is not a valid JSON document.

• The path parameter is not a valid path expression.

• The path parameter contains the * or ** wildcard.

The path-value pairs are evaluated from left to right. The document generated by evaluating a path-value pair is used as the new value for evaluating the next pair.

Path-value pairs that do not identify an array in the JSON document are ignored.

If the path identifies an array element, the corresponding value is inserted at that position, and all subsequent values are shifted to the right. If the path identifies a position beyond the end of the array, the value is inserted at the end of the array.

Examples

SET @jn = '["a", {"b": [1, 2]}, [3, 4]]';
Query OK, 0 rows affected (0.000 sec)

SELECT JSON_ARRAY_INSERT(@jn, '$[1]', 'x');
+------------------------------------+
| JSON_ARRAY_INSERT(@jn, '$[1]', 'x') |
+------------------------------------+
| ["a", "x", {"b": [1, 2]}, [3, 4]]  |
+------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_ARRAY_INSERT(@jn, '$[50]', 'x');
+--------------------------------------+
| JSON_ARRAY_INSERT(@jn, '$[100]', 'x') |
+--------------------------------------+
| ["a", {"b": [1, 2]}, [3, 4], "x"]    |
+--------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_ARRAY_INSERT(@jn, '$[1].b[0]', 'x');
+-----------------------------------------+
| JSON_ARRAY_INSERT(@jn, '$[1].b[0]', 'x') |
+-----------------------------------------+
| ["a", {"b": ["x", 1, 2]}, [3, 4]]       |
+-----------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_ARRAY_INSERT(@jn, '$[2][1]', 'y');
+---------------------------------------+
| JSON_ARRAY_INSERT(@jn, '$[2][1]', 'y') |
+---------------------------------------+
| ["a", {"b": [1, 2]}, [3, "y", 4]]     |
+---------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_ARRAY_INSERT(@n, '$[0]', 'x', '$[2][1]', 'y');
+----------------------------------------------------+
| JSON_ARRAY_INSERT(@jn, '$[0]', 'x', '$[2][1]', 'y') |
+----------------------------------------------------+
| ["x", "a", {"b": [1, 2]}, [3, 4]]                  |
+----------------------------------------------------+
1 row in set (0.001 sec)