How the JSON_ARRAY_APPEND() function works in Mariadb?

The JSON_ARRAY_APPEND() function is a JSON function that appends values to the end of an array within a JSON document.

Posted on

The JSON_ARRAY_APPEND() function is a JSON function that 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.

Syntax

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

JSON_ARRAY_APPEND(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: Appending a value to 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_APPEND() function to append a new number to the end of the array.

SELECT JSON_ARRAY_APPEND(JSON_ARRAY(1, 2, 3), '$', 4) AS result;

The output is:

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

This means that the value 4 is appended to the array at the root path ($).

Example 2: Appending a value to 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_APPEND() function to append a new string to the end of the nested array.

SELECT JSON_ARRAY_APPEND(JSON_OBJECT('name', 'Alice', 'hobbies', JSON_ARRAY('reading', 'writing')), '$.hobbies', 'coding') AS result;

The output is:

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

This means that the value "coding" is appended to the array at the path $.hobbies.

Example 3: Appending multiple values to 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_APPEND() function to append two new objects to the end of the array.

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

The output is:

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

This means that the objects {"id": 3, "name": "Dave"} and {"id": 4, "name": "Eve"} are appended to the array at the root path ($).

This means that a new array with the value 25 is created at the path $.age.

Example 4: Appending a value to a 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_APPEND() function to append a value to a path that points to a non-array value.

SELECT JSON_ARRAY_APPEND(JSON_OBJECT('name', 'Frank'), '$.name', 'Gina') AS result;

The output is:

+-----------------------------+
| result                      |
+-----------------------------+
| {"name": ["Frank", "Gina"]} |
+-----------------------------+

This means that the function change the path $.name points to an array, and append the value to it.

There are some other JSON functions that are related to the JSON_ARRAY_APPEND() 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_INSERT(): This function 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.

Conclusion

The JSON_ARRAY_APPEND() function is a useful JSON function that can append 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. There are also some other related functions that can create, insert, or aggregate JSON arrays, such as JSON_ARRAY(), JSON_ARRAY_INSERT(), and JSON_ARRAYAGG().