MySQL JSON_REPLACE() Function

In MySQL, the JSON_REPLACE() function replaces existing data specified by the path in a JSON document and return the modified JSON document.

`JSON_REPLACE()` Syntax

Here is the syntax of the MySQL JSON_REPLACE() function:

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

Parameters

json: Required. The modified JSON document.
path: Required. The path to replace data. A valid path expression that cannot contain * or **.
value: Required. The new data.

Return value

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

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

If value is a string, the JSON_REPLACE() function writes it to the JSON document as a JSON string. To ensure the inserted data’s data type is correct, please convert value as a JSON type value.

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

There will happen an error in the following cases:

MySQL will give an error if the parameter json is not a valid JSON document. You can use JSON_VALID() to verify the JSON document.
If the parameter path is not a valid path expression or contains * or **, MySQL will give an error.

`JSON_REPLACE()` Examples

Replace in array

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

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

Let’s replace two elements of the array with new value:

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

Here, we want to replace the first element of the array with 0 and replace the third element of the array with 6. Let’s take a look at the results:

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

We found that the first element of the array was successfully replaced with 0. And, there is not 6 in the array. This is because the JSON_REPLACE() function only replaces data that already exists.

Write JSON type data

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

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

Now let’s modify x the to true:

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

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

We found that true became "true". The result what we hoped should be {"x": 1, "y": true}.

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

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

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

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

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

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

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

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

Now, the value of x is in the correct data type.

JSON_REPLACE() Syntax