In MariaDB, JSON_ARRAY_INSERT()
is a built-in function that inserts a value into a JSON document and returns the result.
Syntax
The syntax goes like this:
JSON_ARRAY_INSERT(json_doc, path, value[, path, value] ...)
Where json_doc
is the JSON document, path
is the path to where you want to insert the value/s, and value
is the value to insert.
Example
Here’s an example to demonstrate.
SELECT JSON_ARRAY_INSERT('[0, 1, 2]', '$[1]', 3);
Result:
+-------------------------------------------+ | JSON_ARRAY_INSERT('[0, 1, 2]', '$[1]', 3) | +-------------------------------------------+ | [0, 3, 1, 2] | +-------------------------------------------+
In this case, the value 3
was inserted into the the array at position 1
. Arrays are zero based, so it becomes the second element in the array.
Insert Multiple Values
You can insert multiple values within a single call to JSON_ARRAY_INSERT()
.
Example:
SELECT JSON_ARRAY_INSERT('[0, 1, 2]', '$[0]', 4, '$[2]', 5);
Result:
+------------------------------------------------------+ | JSON_ARRAY_INSERT('[0, 1, 2]', '$[0]', 4, '$[2]', 5) | +------------------------------------------------------+ | [4, 0, 5, 1, 2] | +------------------------------------------------------+
Evaluation is performed from left to right.
Here’s what happens if we switch the path
/value
arguments around:
SELECT JSON_ARRAY_INSERT('[0, 1, 2]', '$[2]', 5, '$[0]', 4);
Result:
+------------------------------------------------------+ | JSON_ARRAY_INSERT('[0, 1, 2]', '$[2]', 5, '$[0]', 4) | +------------------------------------------------------+ | [4, 0, 1, 5, 2] | +------------------------------------------------------+
Multiple Arrays
You can insert values into more than one array within the same call to JSON_ARRAY_INSERT()
.
Example:
SET @json_doc = '{"a": [0, 1], "b": [2, 3]}';
SELECT JSON_ARRAY_INSERT(@json_doc, '$.a[1]', 4, '$.b[2]', 5);
Result:
+--------------------------------------------------------+ | JSON_ARRAY_INSERT(@json_doc, '$.a[1]', 4, '$.b[2]', 5) | +--------------------------------------------------------+ | {"a": [0, 4, 1], "b": [2, 3, 5]} | +--------------------------------------------------------+
Nested Arrays
Here’s an example of inserting a value into a nested array:
SET @json_doc = '[0, 1, [2, 3]]';
SELECT JSON_ARRAY_INSERT(@json_doc, '$[2][1]', 4);
Result:
+--------------------------------------------+ | JSON_ARRAY_INSERT(@json_doc, '$[2][1]', 4) | +--------------------------------------------+ | [0, 1, [2, 4, 3]] | +--------------------------------------------+
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", "Water"]
}
}';
SELECT JSON_DETAILED(
JSON_ARRAY_INSERT(
@json_doc,
'$.pet.diet[1]',
'Chicken')
);
Result:
{ "pet": { "name": "Fluffy", "diet": [ "Fish", "Chicken", "Water" ] } }
Null Arguments
If the first argument is NULL
, the result is NULL
:
SELECT JSON_ARRAY_INSERT(null, '$[1]', 4);
Result:
+------------------------------------+ | JSON_ARRAY_INSERT(null, '$[1]', 4) | +------------------------------------+ | NULL | +------------------------------------+
The same applies to the path
argument:
SELECT JSON_ARRAY_INSERT('[0, 1, 2, 3]', null, 4);
Result:
+--------------------------------------------+ | JSON_ARRAY_INSERT('[0, 1, 2, 3]', null, 4) | +--------------------------------------------+ | NULL | +--------------------------------------------+
However, if the value
argument is NULL
, then NULL
is inserted into to the array:
SELECT JSON_ARRAY_INSERT('[0, 1, 2]', '$[1]', null);
Result:
+----------------------------------------------+ | JSON_ARRAY_INSERT('[0, 1, 2]', '$[1]', null) | +----------------------------------------------+ | [0, null, 1, 2] | +----------------------------------------------+
You can also use JSON_ARRAY_APPEND()
to append values to an array.