In MariaDB, JSON_REPLACE() is a built-in function that replaces existing values in a JSON document and returns the result. 

Syntax

The syntax goes like this:

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

Example

Here’s an example to demonstrate.

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

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

Result:

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

In this case I replaced the value Cat with Dog.

Array Elements

To replace an array element, specify the element’s index:

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

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

Result:

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

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

Multiple Paths

The syntax allows for replacing values at multiple paths with a single call to JSON_REPLACE().

When providing multiple paths, they’re evaluated from left to right. This means that the result from the earlier evaluation is used as the value for the next.

First, here’s an example that replaces multiple values in a document, based on their key:

SET @json = '
    { 
        "name" : "Flutter", 
        "type" : "Bird",
        "weight" : 10
    }
';

SELECT JSON_REPLACE(@json, '$.type', 'Bat', '$.weight', 20);

Result:

+------------------------------------------------------+
| JSON_REPLACE(@json, '$.type', 'Bat', '$.weight', 20) |
+------------------------------------------------------+
| {"name": "Flutter", "type": "Bat", "weight": 20}     |
+------------------------------------------------------+

Each value was replaced as expected.

In the next example we replace multiple elements in an array:

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

SELECT 
    JSON_REPLACE(
        @json, 
        '$.scores[2]', "a", 
        '$.scores[4]', "b"
    ) AS Result;

Result:

+------------------------------------+
| Result                             |
+------------------------------------+
| {"scores": [0, 1, "a", 3, "b", 5]} |
+------------------------------------+

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_REPLACE(null, '$.a', 1) AS a,
    JSON_REPLACE('{"a":1}', null, 1) AS b,
    JSON_REPLACE('{"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_REPLACE(). This results in NULL being returned for both calls:

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

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

Result:

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

Incorrect Parameter Count

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

SELECT JSON_REPLACE();

Result:

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

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

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

Result:

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

Similar Functions

The JSON_INSERT() function can insert new data.

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