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


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

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


Required. The modified JSON document.
Required. The path to replace data. A valid path expression that cannot contain * or **.
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.


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.