How the JSON_ARRAY_INSERT() function works in Mariadb?

The JSON_ARRAY_INSERT() function is a JSON function that inserts values into an array within a JSON document.

Posted on

The JSON_ARRAY_INSERT() function is a JSON function that inserts values into an array within a JSON document. The function takes a JSON document and one or more pairs of arguments that consist of a path and a value. The function returns a modified JSON document with the values inserted into the array at the specified path. If the path does not exist, an error occurs. If the path points to a non-array value, an error occurs.

Syntax

The syntax of the JSON_ARRAY_INSERT() function is as follows:

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

Where json_doc is a valid JSON document, path is a valid JSON path expression, and val is a JSON value.

Examples

Example 1: Inserting a value into an existing array

In this example, we create a JSON document that contains an array of numbers using the JSON_ARRAY() function. Then we use the JSON_ARRAY_INSERT() function to insert a new number into the array at a specified position.

SELECT JSON_ARRAY_INSERT(JSON_ARRAY(1, 2, 3), '$[1]', 4) AS result;

The output is:

+-----------------+
| result          |
+-----------------+
| [1, 4, 2, 3]    |
+-----------------+

This means that the value 4 is inserted into the array at the position 1 (the second element).

Example 2: Inserting a value into a nested array

In this example, we create a JSON document that contains a nested array of strings using the JSON_OBJECT() and JSON_ARRAY() functions. Then we use the JSON_ARRAY_INSERT() function to insert a new string into the nested array at a specified position.

SELECT JSON_ARRAY_INSERT(JSON_OBJECT('name', 'Alice', 'hobbies', JSON_ARRAY('reading', 'writing')), '$.hobbies[0]', 'coding') AS result;

The output is:

+----------------------------------------------------------------+
| result                                                         |
+----------------------------------------------------------------+
| {"name": "Alice", "hobbies": ["coding", "reading", "writing"]} |
+----------------------------------------------------------------+

This means that the value "coding" is inserted into the array at the path $.hobbies[0] (the first element).

Example 3: Inserting multiple values into an array

In this example, we create a JSON document that contains an array of objects using the JSON_ARRAY() and JSON_OBJECT() functions. Then we use the JSON_ARRAY_INSERT() function to insert two new objects into the array at different positions.

SELECT JSON_ARRAY_INSERT(JSON_ARRAY(JSON_OBJECT('id', 1, 'name', 'Bob'), JSON_OBJECT('id', 2, 'name', 'Carol')), '$[0]', JSON_OBJECT('id', 3, 'name', 'Dave'), '$[3]', JSON_OBJECT('id', 4, 'name', 'Eve')) AS result;

The output is:

+-------------------------------------------------------------------------------------------------------------+
| result                                                                                                      |
+-------------------------------------------------------------------------------------------------------------+
| [{"id": 3, "name": "Dave"}, {"id": 1, "name": "Bob"}, {"id": 2, "name": "Carol"}, {"id": 4, "name": "Eve"}] |
+-------------------------------------------------------------------------------------------------------------+

This means that the objects {"id": 3, "name": "Dave"} and {"id": 4, "name": "Eve"} are inserted into the array at the positions 0 and 3, respectively.

Example 4: Getting an error if the path does not exist

In this example, we create a JSON document that contains an object with a single key-value pair using the JSON_OBJECT() function. Then we use the JSON_ARRAY_INSERT() function to insert a value into a non-existent path.

SELECT JSON_ARRAY_INSERT(JSON_OBJECT('name', 'Frank'), '$.age[0]', 25) AS result;

The output is:

+-------------------+
| result            |
+-------------------+
| {"name": "Frank"} |
+-------------------+

This means that the function does nothing because the path $.age[0] does not exist in the JSON document.

Example 5: Getting an error if the path points to a non-array value

In this example, we create a JSON document that contains an object with a single key-value pair using the JSON_OBJECT() function. Then we use the JSON_ARRAY_INSERT() function to insert a value into a path that points to a non-array value.

SELECT JSON_ARRAY_INSERT(JSON_OBJECT('name', 'Frank'), '$.name[0]', 'Gina') AS result;

The output is:

+-------------------+
| result            |
+-------------------+
| {"name": "Frank"} |
+-------------------+

This means that the function does nothing because the path $.name[0] points to a string value, not an array.

There are some other JSON functions that are related to the JSON_ARRAY_INSERT() function. Here are some of them:

  • JSON_ARRAY(): This function creates a JSON array from a list of values. For example, JSON_ARRAY(1, 2, 3) returns [1, 2, 3].
  • JSON_ARRAY_APPEND(): This function appends values to the end of an array within a JSON document. The function takes a JSON document and one or more pairs of arguments that consist of a path and a value. The function returns a modified JSON document with the values appended to the array at the specified path. If the path does not exist, a new array is created. If the path points to a non-array value, an error occurs.

Conclusion

The JSON_ARRAY_INSERT() function is a useful JSON function that can insert values into an array within a JSON document. The function takes a JSON document and one or more pairs of arguments that consist of a path and a value. The function returns a modified JSON document with the values inserted into the array at the specified path. If the path does not exist, an error occurs. If the path points to a non-array value, an error occurs. There are also some other related functions that can create, append, or aggregate JSON arrays, such as JSON_ARRAY(), JSON_ARRAY_APPEND(), and JSON_ARRAYAGG().