MariaDB JSON_ARRAY_APPEND() Function

In MariaDB, JSON_ARRAY_APPEND() is a built-in function that appends a value to a specified array in a JSON document and returns the modified JSON document.

You can also use JSON_ARRAY_INSERT() to insert values ​​into JSON arrays.

MariaDB JSON_ARRAY_APPEND() Syntax

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

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

Parameters

json

Required. The JSON document to be modified.

path

Required. Path to add new elements. A valid path expression that cannot contain * or **.

value

Required. The new element value to be added to the array.

Return value

The MariaDB JSON_ARRAY_APPEND() function is used to modify the JSON document. It appends an element to the specified array node and returns the modified JSON document.

If the node indicated by the path expression is not an array node, the JSON_ARRAY_APPEND() function will use the value of this node as the first element of the array, and append new elements at the end of the array.

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

The JSON_ARRAY_APPEND() 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.

MariaDB JSON_ARRAY_APPEND() Examples

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

Aappend a value

SELECT JSON_ARRAY_APPEND('[1, 2, 3]', '$', 4);

Output:

+----------------------------------------+
| JSON_ARRAY_APPEND('[1, 2, 3]', '$', 4) |
+----------------------------------------+
| [1, 2, 3, 4]                           |
+----------------------------------------+

Here, the path expression $ represents the JSON document being operated on.

Append multiple values

Multiple values ​​can be appended in a single call to JSON_ARRAY_APPEND().

SELECT JSON_ARRAY_APPEND('[1, 2, 3]', '$', 4, '$', 5);

Output:

+------------------------------------------------+
| JSON_ARRAY_APPEND('[1, 2, 3]', '$', 4, '$', 5) |
+------------------------------------------------+
| [1, 2, 3, 4, 5]                                |
+------------------------------------------------+

Append elements to an embedded array

SELECT JSON_ARRAY_APPEND('[1, [2, 3]]', '$[1]', 4);

Output:

+---------------------------------------------+
| JSON_ARRAY_APPEND('[1, [2, 3]]', '$[1]', 4) |
+---------------------------------------------+
| [1, [2, 3, 4]]                              |
+---------------------------------------------+

Here, the path expression $[1] represents the 2nd element in the array of JSON documents being operated on.

Append an element to an array in an object

SELECT JSON_ARRAY_APPEND('{"name": "Tim", "hobby": ["car"]}', '$.hobby', "food");

Output:

+---------------------------------------------------------------------------+
| JSON_ARRAY_APPEND('{"name": "Tim", "hobby": ["car"]}', '$.hobby', "food") |
+---------------------------------------------------------------------------+
| {"name": "Tim", "hobby": ["car", "food"]}                                 |
+---------------------------------------------------------------------------+

Here, the path expression $.hobby represents the hobby member of the JSON object being operated on.

Append value to non-array

SELECT JSON_ARRAY_APPEND('1', '$', 2);

Output:

+--------------------------------+
| JSON_ARRAY_APPEND('1', '$', 2) |
+--------------------------------+
| [1, 2]                         |
+--------------------------------+

Here, the JSON document 1 is modified into arrays [1, 2].

You can also append to non-array members in the array, such as:

SELECT JSON_ARRAY_APPEND('[1, 2]', '$[1]', 3);

Output:

+----------------------------------------+
| JSON_ARRAY_APPEND('[1, 2]', '$[1]', 3) |
+----------------------------------------+
| [1, [2, 3]]                            |
+----------------------------------------+

You can also append to non-array members in the object, such as:

SELECT JSON_ARRAY_APPEND('{"name": "Tim", "hobby": "car"}', '$.hobby', "food");

Output:

+-------------------------------------------------------------------------+
| JSON_ARRAY_APPEND('{"name": "Tim", "hobby": "car"}', '$.hobby', "food") |
+-------------------------------------------------------------------------+
| {"name": "Tim", "hobby": ["car", "food"]}                               |
+-------------------------------------------------------------------------+

NULL parameter

If either argument is NULL, MariaDB JSON_ARRAY_APPEND will return NULL:

SELECT JSON_ARRAY_APPEND(NULL, '$', 0);

Output:

+---------------------------------+
| JSON_ARRAY_APPEND(NULL, '$', 0) |
+---------------------------------+
| NULL                            |
+---------------------------------+

Conclusion

In MariaDB, JSON_ARRAY_APPEND() is a built-in function that appends a value to a specified array in a JSON document and returns the modified JSON document.