In MariaDB, JSON_ARRAY_APPEND()
is a built-in function that appends values to the end of the specified array/s within a JSON document, and returns the result.
Syntax
The syntax goes like this:
JSON_ARRAY_APPEND(json_doc, path, value[, path, value] ...)
Where json_doc
is the JSON document, path
is the path to where you want to append the value/s, and value
is the value to append.
Example
Here’s an example to demonstrate the function.
SET @json_doc = '[0, 1, 2, 3]';
SELECT JSON_ARRAY_APPEND(@json_doc, '$', 4);
Result:
+--------------------------------------+ | JSON_ARRAY_APPEND(@json_doc, '$', 4) | +--------------------------------------+ | [0, 1, 2, 3, 4] | +--------------------------------------+
In this case, the value 4
was appended to the end of the array.
Append Multiple Values
You can append multiple values within a single call to JSON_ARRAY_APPEND()
.
Example:
SET @json_doc = '[0, 1, 2, 3]';
SELECT JSON_ARRAY_APPEND(@json_doc, '$', 4, '$', 5);
Result:
+----------------------------------------------+ | JSON_ARRAY_APPEND(@json_doc, '$', 4, '$', 5) | +----------------------------------------------+ | [0, 1, 2, 3, 4, 5] | +----------------------------------------------+
Multiple Arrays
You can append values to more than one array within the same call to JSON_ARRAY_APPEND()
.
Example:
SET @json_doc = '{"a": [0, 1], "b": [2, 3]}';
SELECT JSON_ARRAY_APPEND(@json_doc, '$.a', 4, '$.b', 5);
Result:
+--------------------------------------------------+ | JSON_ARRAY_APPEND(@json_doc, '$.a', 4, '$.b', 5) | +--------------------------------------------------+ | {"a": [0, 1, 4], "b": [2, 3, 5]} | +--------------------------------------------------+
Nested Arrays
Here’s an example of appending a value to a nested array:
SET @json_doc = '[0, 1, [2, 3]]';
SELECT JSON_ARRAY_APPEND(@json_doc, '$[2]', 4);
Result:
+-----------------------------------------+ | JSON_ARRAY_APPEND(@json_doc, '$[2]', 4) | +-----------------------------------------+ | [0, 1, [2, 3, 4]] | +-----------------------------------------+
And in the following example, the original JSON document doesn’t contain a nested array, but JSON_ARRAY_APPEND()
creates a nested array based on our path:
SET @json_doc = '[0, 1, 2, 3]';
SELECT JSON_ARRAY_APPEND(@json_doc, '$[3]', 4);
Result:
+-----------------------------------------+ | JSON_ARRAY_APPEND(@json_doc, '$[3]', 4) | +-----------------------------------------+ | [0, 1, 2, [3, 4]] | +-----------------------------------------+
Larger JSON Document
Here’s an example with a slightly larger JSON document.
I also use JSON_DETAILED()
to prettify the result:
SET @json_doc = '{
"pet": {
"name": "Fluffy",
"diet": ["Fish", "Chicken"]
}
}';
SELECT JSON_DETAILED(
JSON_ARRAY_APPEND(
@json_doc,
'$.pet.diet',
'Water')
);
Result:
{ "pet": { "name": "Fluffy", "diet": [ "Fish", "Chicken", "Water" ] } }
And here’s one that creates a nested array:
SET @json_doc = '{
"pet": {
"name": "Scratch",
"diet": ["Beef", "Water"]
}
}';
SELECT JSON_DETAILED(
JSON_ARRAY_APPEND(
@json_doc,
'$.pet.diet[1]',
'Beer')
);
Result:
{ "pet": { "name": "Scratch", "diet": [ "Beef", [ "Water", "Beer" ] ] } }
Null Arguments
If the first argument is NULL
, the result is NULL
:
SELECT JSON_ARRAY_APPEND(null, '$', 4);
Result:
+---------------------------------+ | JSON_ARRAY_APPEND(null, '$', 4) | +---------------------------------+ | NULL | +---------------------------------+
The same applies to the path
argument:
SET @json_doc = '[0, 1, 2, 3]';
SELECT JSON_ARRAY_APPEND(@json_doc, null, 4);
Result:
+---------------------------------------+ | JSON_ARRAY_APPEND(@json_doc, null, 4) | +---------------------------------------+ | NULL | +---------------------------------------+
However, if the value
argument is NULL
, then NULL
is appended to the array:
SET @json_doc = '[0, 1, 2, 3]';
SELECT JSON_ARRAY_APPEND(@json_doc, '$', null);
Result:
+-----------------------------------------+ | JSON_ARRAY_APPEND(@json_doc, '$', null) | +-----------------------------------------+ | [0, 1, 2, 3, null] | +-----------------------------------------+
You can also use JSON_ARRAY_INSERT()
to insert values into an array.