MariaDB JSON_ARRAY_INSERT() Explained

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.