MySQL JSON_ARRAY_APPEND() Function

In MySQL, the JSON_ARRAY_APPEND() function appends a value to a array in a JSON document, specified by a path, and returns the modified JSON document.

`JSON_ARRAY_APPEND()` Syntax

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

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

Parameters

json: Required. The JSON document that will be modified.
path: Required. The path expression that points to an array. A valid path expression cannot contain * or **.
value: Required. The value to be appended to an array specified by the path.

Return value

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

If the node indicated by the path expression is not an array, the JSON_ARRAY_APPEND() function will use the value of this node as the first element of the array and append the value to the end 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 the parameter path is not a valid path expression or contains * or **, MySQL will give an error.

`JSON_ARRAY_APPEND()` Examples

Here are some examples of JSON_ARRAY_APPEND().

Append a value to an array

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

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

Here, the path expression $ represents the JSON document [1, 2, 3].

Append a value to an nested array

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

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

Here, the path expression $[1] represents the second element of the array [1, [2, 3]], that is [2, 3].

Append a value to an array in an object

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

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

Here, the path expression $.hobby represents the member of the JSON object, that is "hobby": ["car"]. So, the value "food" was appended to ["car"].

Append a value to non-array

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

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

Here, the path expression $ represents 1 that is not a JSON array. So JSON_ARRAY_APPEND() wrapped 1 to an array [1]. Then, append 2 to [1] and the array is [1, 2].

You can also append values to non-array elements in an array, for example:

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

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

You can also append values to non-array members in an object, for example:

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

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

JSON_ARRAY_APPEND() Syntax