MariaDB JSON_ARRAY_APPEND() Explained

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.