Purpose
This function inserts an array into a document and returns the modified document.
Syntax
JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
Considerations
The json_doc parameter specifies the JSON document, and path specifies the path. If any parameter is NULL, the function returns NULL.
The following situations will result in an error:
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 a path-value pair is used as the new value for 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 element's 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
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