In MariaDB, JSON_INSERT() is a built-in function that inserts data into a JSON document, and returns the result.

Syntax

The syntax goes like this:

JSON_INSERT(json_doc, path, val[, path, val] ...)

Where json_doc is the JSON document, path is the path for where the data should be inserted, and val is the value to be inserted at that path.

Example

Here’s an example to demonstrate.

SET @json_document = '
    { 
        "name": "Wag"
    }
';
SELECT JSON_INSERT(@json_document, '$.type', "Dog");

Result:

+----------------------------------------------+
| JSON_INSERT(@json_document, '$.type', "Dog") |
+----------------------------------------------+
| {"name": "Wag", "type": "Dog"}               |
+----------------------------------------------+

Here I inserted "type": "Dog" into the document.

In this case, I used $.type as the path. Therefore, type is the key and Dog is the value.

When the Path Already Exists

Passing a path that already exists in the JSON document results in the original document being returned unchanged.

Example:

SET @json_document = '
    { 
        "name": "Wag"
    }
';
SELECT JSON_INSERT(@json_document, '$.name', "Bark");

Result:

+-----------------------------------------------+
| JSON_INSERT(@json_document, '$.name', "Bark") |
+-----------------------------------------------+
| {"name": "Wag"}                               |
+-----------------------------------------------+

Inserting Arrays

Here’s an example of inserting an array into a JSON document:

SET @json_document = '
    { 
        "name" : "Wag"
    }
';
SELECT JSON_INSERT(@json_document, '$.scores', '[ 8, 7, 9 ]');

Result:

+--------------------------------------------------------+
| JSON_INSERT(@json_document, '$.scores', '[ 8, 7, 9 ]') |
+--------------------------------------------------------+
| {"name": "Wag", "scores": "[ 8, 7, 9 ]"}               |
+--------------------------------------------------------+

Appending to Arrays

Here’s an example of using JSON_INSERT() to append data to an array:

SET @json_document = '
    { 
        "_id" : 1, 
        "awards" : [ "Top Dog", "Best Dog" ] 
    }
';
SELECT JSON_INSERT(@json_document, '$.awards[2]', "Biggest Dog");

Result:

+--------------------------------------------------------------+
| JSON_INSERT(@json_document, '$.awards[2]', "Biggest Dog")    |
+--------------------------------------------------------------+
| {"_id": 1, "awards": ["Top Dog", "Best Dog", "Biggest Dog"]} |
+--------------------------------------------------------------+

However, although it worked fine for this example, it could easily have failed. For example, if we try to insert the value at a different place in the array, it doesn’t work:

SET @json_document = '
    { 
        "_id" : 1, 
        "awards" : [ "Top Dog", "Best Dog" ] 
    }
';
SELECT JSON_INSERT(@json_document, '$.awards[1]', "Biggest Dog");

Result:

+-----------------------------------------------------------+
| JSON_INSERT(@json_document, '$.awards[1]', "Biggest Dog") |
+-----------------------------------------------------------+
| {"_id": 1, "awards": ["Top Dog", "Best Dog"]}             |
+-----------------------------------------------------------+

To insert values into an array, use the JSON_ARRAY_INSERT() function instead.

Also, although we were able to append a value to an array in the above example, you’re probably better off using the JSON_ARRAY_APPEND() function, as it is designed specifically for that purpose.

Nested Objects

Here’s an example of inserting a value into an object nested inside another object:

SET @json_document = '
    { 
        "_id" : 1, 
        "name" : "Wag", 
        "details" : {
            "type" : "Dog", 
            "weight" : 20,
            "awards" : { 
                "Florida Dog Awards" : "Top Dog", 
                "New York Marathon" : "Fastest Dog"
            }
        }
    }
';
SELECT JSON_INSERT(
    @json_document, 
    '$.details.awards.Sumo 2020',
    'Biggest Dog'
    );

Result:

{"_id": 1, "name": "Wag", "details": {"type": "Dog", "weight": 20, "awards": {"Florida Dog Awards": "Top Dog", "New York Marathon": "Fastest Dog", "Sumo 2020": "Biggest Dog"}}}

Prettify the Result

We can use the JSON_DETAILED() function to make the result easier to read:

SET @json_document = '
    { 
        "_id" : 1, 
        "name" : "Wag", 
        "details" : {
            "type" : "Dog", 
            "weight" : 20,
            "awards" : { 
                "Florida Dog Awards" : "Top Dog", 
                "New York Marathon" : "Fastest Dog"
            }
        }
    }
';
SELECT 
    JSON_DETAILED(
        JSON_INSERT(
            @json_document, 
            '$.details.awards.Sumo 2020',
            'Biggest Dog'
        )
    );

Result:

{
    "_id": 1,
    "name": "Wag",
    "details": 
    {
        "type": "Dog",
        "weight": 20,
        "awards": 
        {
            "Florida Dog Awards": "Top Dog",
            "New York Marathon": "Fastest Dog",
            "Sumo 2020": "Biggest Dog"
        }
    }
}

Null Arguments

If either of the json_document or path arguments are NULL, the result is NULL:

SELECT 
    JSON_INSERT(null, '$.type', 'Dog'),
    JSON_INSERT('{"a":1}', null, 'Dog');

Result:

+------------------------------------+-------------------------------------+
| JSON_INSERT(null, '$.type', 'Dog') | JSON_INSERT('{"a":1}', null, 'Dog') |
+------------------------------------+-------------------------------------+
| NULL                               | NULL                                |
+------------------------------------+-------------------------------------+

However, if the value argument is NULL, the key is added at the specified path, with a value of null:

SELECT JSON_INSERT('{"a":1}', '$.type', null);

Result:

+----------------------------------------+
| JSON_INSERT('{"a":1}', '$.type', null) |
+----------------------------------------+
| {"a": 1, "type": null}                 |
+----------------------------------------+

Incorrect Parameter Count

Providing no arguments results in an error:

SELECT JSON_INSERT();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_INSERT'

It’s the same when you provide too few or too many arguments:

SELECT JSON_INSERT('{ "a": 1}');

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_INSERT'

Similar Functions

The JSON_REPLACE() function can update existing data.

The JSON_SET() function can update existing data and insert new data. So JSON_SET() is like JSON_INSERT() and JSON_REPLACE() in one function.