MariaDB JSON_REPLACE() Function

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

You can use this JSON_INSERT() function to insert new data into a JSON document, use this JSON_SET() function to insert new data into a JSON document, or to update existing data.

MariaDB JSON_REPLACE() Syntax

Here is the syntax for the MariaDB JSON_REPLACE() function:

JSON_REPLACE(json, path, value[, path2, value2] ...)

Parameters

json

Required. The modified JSON document.

path

Required. A valid path expression that cannot contain * or **.

value

Required. new data.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_REPLACE'.

Return value

The MariaDB JSON_REPLACE() function replaces existing data in a JSON document and return the new JSON document. You can provide multiple pairs of path-value parameters to replace multiple data at once.

The MariaDB JSON_REPLACE() function cans only replace existing data. If the specified path does not exist in the JSON document, no data will be inserted.

If value is a string, the JSON_REPLACE() function will write it to the JSON document as a string.

This function will return NULL if the JSON document or path is NULL.

JSON_REPLACE() Examples

Arrays

Let’s first create a JSON document to demonstrate the following example:

SET @array = '[1, [2, 3]]';

Let’s replace the first and third elements of the array with new values:

SELECT JSON_REPLACE(@array, '$[0]', 0, '$[2]', 6);

Here, we want to replace the first element of the array and the third element of the array. Let’s see the result:

+--------------------------------------------+
| JSON_REPLACE(@array, '$[0]', 0, '$[2]', 6) |
+--------------------------------------------+
| [0, [2, 3]]                                |
+--------------------------------------------+

We found that the first element of the array was successfully replaced 0. Then, there is no insertion at the end of the array 6. This is because the JSON_REPLACE() function only replaces data that already exists.

JSON type data

Let’s first create a JSON document containing JSON objects:

SET @obj = '{"x": 1}';

Now let’s change x the to true:

SELECT JSON_REPLACE(@obj, '$.x', 'true');

Output:

+-----------------------------------+
| JSON_REPLACE(@obj, '$.x', 'true') |
+-----------------------------------+
| {"x": "true"}                     |
+-----------------------------------+

We found out the result is {"x": "true"}, rather than what we hoped for {"x": true}.

This is because, if the value parameter is a string, the JSON_REPLACE() function writes it to the JSON document as a string. Let’s look at a few more similar examples:

SELECT JSON_REPLACE(@obj, '$.x', '[1, 2]');

Output:

+-------------------------------------+
| JSON_REPLACE(@obj, '$.x', '[1, 2]') |
+-------------------------------------+
| {"x": "[1, 2]"}                     |
+-------------------------------------+

or

SELECT JSON_REPLACE(@obj, '$.x', '{"z": 2}');

Output:

+---------------------------------------+
| JSON_REPLACE(@obj, '$.x', '{"z": 2}') |
+---------------------------------------+
| {"x": "{\"z\": 2}"}                   |
+---------------------------------------+

To solve this problem, we can use the JSON_EXTRACT() function to convert the data into JSON type, for example:

SELECT JSON_REPLACE(@obj, '$.x', JSON_EXTRACT('{"z": 2}', '$'));

Output:

+----------------------------------------------------------+
| JSON_REPLACE(@obj, '$.x', JSON_EXTRACT('{"z": 2}', '$')) |
+----------------------------------------------------------+
| {"x": {"z": 2}}                                          |
+----------------------------------------------------------+

You can also use JSON_QUERY() or JSON_VALUE().

Conclusion

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