Purpose
You can call this function to insert an array into a document and return the modified document.
Syntax
JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
Notes
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.
The 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