Purpose
This function inserts an array into a document and returns the modified document.
Syntax
JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
Description
The json_doc parameter specifies the JSON document, and path is a path parameter. If either parameter is NULL, NULL is returned.
An error occurs in the following scenarios:
The
json_docparameter is not a valid JSON document.The
pathparameter is not a valid path expression.The
pathparameter contains the * or ** wildcard.
Path-value pairs are evaluated from left to right. The document generated by evaluating the current path-value pair becomes the new value for evaluating the next pair.
In a JSON document, path-value pairs that are not identified by a path and belong to an array will be ignored.
If the path identifies an array element, the corresponding value is inserted at that element's position, shifting all subsequent values to the right. If the path identifies an array position beyond the end of the 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