MySQL JSON_ARRAY_INSERT() Function

In MySQL, the JSON_ARRAY_INSERT() function inserts a value into a specified array in a given JSON document and returns a new JSON document.

`JSON_ARRAY_INSERT()` Syntax

Here is the syntax of the MySQL JSON_ARRAY_INSERT() function:

JSON_ARRAY_INSERT(json, path, value[, path2, value2] ...)

Parameters

json: Required. The JSON document that will be modified.
path: Required. The path expression that points to a position in an array where to insert the new element. A valid path expression that cannot contain * or **. For example $[0] and $.a[0] means inserting a new element at the beginning of the array.
value: Required. The value to be inserted into the array.

Return value

The JSON_ARRAY_INSERT() function is used to modify the JSON document, it inserts a value into the specified array in the JSON document and returns the new JSON document.

If the position indicated by the path expression exceeds the length of the array, the value will be the last element of the array.

This function will return NULL if the JSON document or path is NULL.

There will happen an error in the following cases:

MySQL will give an error if the parameter json is not a valid JSON document. You can use JSON_VALID() to verify the JSON document.
If path is not a valid path expression or contains * or **, MySQL will give an error.
If path does not indicate an array position, MySQL will give an error.

`JSON_ARRAY_INSERT()` Examples

Here are some examples of JSON_ARRAY_INSERT().

Let’s first create a JSON document to demonstrate the following example:

SET @json = '[1, [2, 3], {"a": [4, 5]}]';

Insert an element

Let’s insert an element at the beginning of the array:

SELECT JSON_ARRAY_INSERT(@json, '$[0]', 0);

+-------------------------------------+
| JSON_ARRAY_INSERT(@json, '$[0]', 0) |
+-------------------------------------+
| [0, 1, [2, 3], {"a": [4, 5]}]       |
+-------------------------------------+

Let’s insert an element at the position of the 3rd element of the array:

SELECT JSON_ARRAY_INSERT(@json, '$[2]', 4);

+-------------------------------------+
| JSON_ARRAY_INSERT(@json, '$[2]', 4) |
+-------------------------------------+
| [1, [2, 3], 4, {"a": [4, 5]}]       |
+-------------------------------------+

Let’s insert an element at the end of the array:

SELECT JSON_ARRAY_INSERT(@json, '$[3]', 'x');

+---------------------------------------+
| JSON_ARRAY_INSERT(@json, '$[3]', 'x') |
+---------------------------------------+
| [1, [2, 3], {"a": [4, 5]}, "x"]       |
+---------------------------------------+

Insert into an nested array

SELECT JSON_ARRAY_INSERT(@json, '$[1][0]', 'x');

+------------------------------------------+
| JSON_ARRAY_INSERT(@json, '$[1][0]', 'x') |
+------------------------------------------+
| [1, ["x", 2, 3], {"a": [4, 5]}]          |
+------------------------------------------+

Insert into an array in an object

SELECT JSON_ARRAY_INSERT(@json, '$[2].a[0]', 'x');

+--------------------------------------------+
| JSON_ARRAY_INSERT(@json, '$[2].a[0]', 'x') |
+--------------------------------------------+
| [1, [2, 3], {"a": ["x", 4, 5]}]            |
+--------------------------------------------+

JSON_ARRAY_INSERT() Syntax