MariaDB JSON_ARRAY_INSERT() Function

In MariaDB, JSON_ARRAY_INSERT() is a built-in function that inserts a value into a specified JSON array and returns a new JSON document.

You can also use JSON_ARRAY_APPEND() to append values ​​to the end of a JSON array.

MariaDB JSON_ARRAY_INSERT() Syntax

Here is the syntax for the MariaDB JSON_ARRAY_INSERT() function:

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

Parameters

json

Required. The modified JSON document.

path

Required. The array element position where to insert the new element. A valid path expression that cannot contain * or **. For example $[0] and $.a[0] means to insert a new element at the beginning of the array.

value

Required. The new element value to be inserted into the array.

Return value

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

If the array element indicated by the path expression exceeds the length of the array, the new element will be inserted at the end of the array.

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

The JSON_ARRAY_INSERT() function will return an error in the following cases:

  • MariaDB will give an error if the argument json is not a valid JSON document. You can verify the validity of a JSON document using JSON_VALID().
  • If the path parameter is not a valid path expression or contains * or **, MariaDB will give an error.
  • If the parameter path indicates a path other than an array element, MariaDB will give an error.

MariaDB JSON_ARRAY_INSERT() Examples

Here are some common examples to show the usages of the Mariadb JSON_ARRAY_INSERT() function.

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

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

Insert an element at the specified position in the array

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

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

Output:

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

Let’s insert a new element as the 3rd element of the array:

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

Output:

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

Let’s insert new elements into the end of the array:

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

Output:

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

Insert an element into an embedded array

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

Output:

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

Insert an element into an array in an object

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

Output:

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

Conclusion

In MariaDB, JSON_ARRAY_INSERT() is a built-in function that inserts a value into a specified JSON array and returns a new JSON document.