MariaDB JSON_SET() Explained

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

JSON_SET() can update and insert data, whereas JSON_INSERT() can only insert data, and JSON_REPLACE() can only update data.

Syntax

The syntax goes like this:

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

Where:

• json_doc is the JSON document.
• path is the path of the element for which to insert data or update the value at.
• val is the new value.

Example

Here’s an example to demonstrate.

SET @json = '
    { 
        "name" : "Wag", 
        "type" : "Cat" 
    }
';

SELECT JSON_SET(@json, '$.type', 'Dog');

Result:

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

In this case I updated the value of the type member Cat to Dog.

Array Elements

To update or insert an array element, specify the element’s index:

SET @json = '
    { 
        "name" : "Wag", 
        "scores" : [8, 0, 9] 
    }
';

SELECT JSON_SET(@json, '$.scores[1]', 7);

Result:

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

In this case, the second array element was updated with the new value. Arrays are zero based, and so $.scores[1] refers to the second element in the array.

Insert Data

The previous examples updated existing data. Here’s an example of inserting new data:

SET @json = '
    { 
        "name" : "Wag", 
        "type" : "Dog" 
    }
';

SELECT JSON_SET(@json, '$.weight', 10);

Result:

+----------------------------------------------+
| JSON_SET(@json, '$.weight', 10)              |
+----------------------------------------------+
| {"name": "Wag", "type": "Dog", "weight": 10} |
+----------------------------------------------+

Here, we inserted a new key/value pair ("weight": 10).

Below is an example of adding a new element to an array:

SET @json = '
    { 
        "name" : "Wag", 
        "scores" : [8, 0, 9] 
    }
';

SELECT JSON_SET(@json, '$.scores[3]', 7);

Result:

+-----------------------------------------+
| JSON_SET(@json, '$.scores[3]', 7)       |
+-----------------------------------------+
| {"name": "Wag", "scores": [8, 0, 9, 7]} |
+-----------------------------------------+

This array example could also be done with JSON_ARRAY_INSERT() or JSON_ARRAY_APPEND().

Multiple Paths

The syntax allows for setting/updating values at multiple paths with a single call to JSON_SET().

Example:

SET @json = '
    { 
        "name" : "Scratch", 
        "type" : "Rat"
    }
';

SELECT JSON_SET(@json, '$.type', 'Cat', '$.weight', 10);

Result:

+--------------------------------------------------+
| JSON_SET(@json, '$.type', 'Cat', '$.weight', 10) |
+--------------------------------------------------+
| {"name": "Scratch", "type": "Cat", "weight": 10} |
+--------------------------------------------------+

In the next example we insert a new value into an array, and update another:

SET @json = '{ "scores" : [ 0, 1, 2 ] }';

SELECT 
    JSON_SET(
        @json, 
        '$.scores[1]', "a", 
        '$.scores[3]', "b"
    ) AS Result;

Result:

+------------------------------+
| Result                       |
+------------------------------+
| {"scores": [0, "a", 2, "b"]} |
+------------------------------+

Null Arguments

If the json_doc or any of the path arguments is null, the result is NULL. But if the value argument is null, then the value is replaced to null.

SELECT 
    JSON_SET(null, '$.a', 1) AS a,
    JSON_SET('{"a":1}', null, 1) AS b,
    JSON_SET('{"a":1}', '$.a', null) AS c;

Result:

+------+------+-------------+
| a    | b    | c           |
+------+------+-------------+
| NULL | NULL | {"a": null} |
+------+------+-------------+

In the following example, one of the path arguments is missing from each call to JSON_SET(). In both cases, the result is NULL:

SET @json = '
    { 
        "name" : "Wag", 
        "type" : "Cat" 
    }
';

SELECT 
    JSON_SET(@json, null, 'Bark', '$.type', 'Dog') AS a,
    JSON_SET(@json, '$.name', 'Bark', null, 'Dog') AS b;

Result:

+------+------+
| a    | b    |
+------+------+
| NULL | NULL |
+------+------+

Incorrect Parameter Count

Calling JSON_SET() without an argument results in an error:

SELECT JSON_SET();

Result:

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

It’s the same when not enough arguments are passed:

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

Result:

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