Purpose
JSON_ARRAY_INSERT() inserts an array into a document and returns the modified document.
Syntax
JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
Note
json_doc specifies the name of the JSON document, and path specifies the path. If any argument is NULL, the return value is NULL.
An error occurs in the following cases:
The JSON document specified for
json_docis invalid.The path expression specified for
pathis invalid.The specified
pathcontains a * or ** wildcard.
Path-value pairs are evaluated from left to right. The document generated by using an evaluated path-value pair is used as a new value to evaluate the next pair.
The path-value pairs for which the path does not identify any array in the JSON document are ignored.
If a path identifies an array element, the corresponding value is inserted at the position of the element. The following values are moved to the right. If the position identified by a path exceeds the end of an array, the value is inserted at the end of the array.
Examples
obclient> SET @jn = '["a", {"b": [1, 2]}, [3, 4]]';
Query OK, 0 rows affected
obclient> SELECT JSON_ARRAY_INSERT(@jn, '$[1]', 'x');
+------------------------------------+
| JSON_ARRAY_INSERT(@jn, '$[1]', 'x') |
+------------------------------------+
| ["a", "x", {"b": [1, 2]}, [3, 4]] |
+------------------------------------+
1 row in set
obclient> SELECT JSON_ARRAY_INSERT(@jn, '$[50]', 'x');
+--------------------------------------+
| JSON_ARRAY_INSERT(@jn, '$[100]', 'x') |
+--------------------------------------+
| ["a", {"b": [1, 2]}, [3, 4], "x"] |
+--------------------------------------+
1 row in set
obclient> 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
obclient> 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
obclient> 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