The JSON_REPLACE function in MySQL is used to replace a specific value in a JSON document with a new value. It allows you to update the content of a JSON object by specifying the path to the element you want to replace and providing the new value.

Syntax

Here is the syntax for the JSON_REPLACE function:

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

json_doc: The original JSON document.
path: The path to the element you want to replace within the JSON document.
new_value: The new value that you want to replace the existing value with.

The JSON_REPLACE() function returns the updated JSON document. It returns NULL if any of the arguments are NULL or if the path does not exist in the json_doc.

You can also use the JSON_REPLACE() function to replace multiple values in a JSON document by providing multiple path/new_value pairs. The function will process these pairs one by one, starting from the left. The result of the first evaluation is used for the next path/new_value pair.

Example

Now, let’s look at an example to illustrate how to use the JSON_REPLACE function:

Suppose you have the following JSON document stored in a column named json_column in a table called example_table:

{
  "name": "John",
  "age": 30,
  "address": {
    "city": "New York",
    "zip": "10001"
  }
}

If you want to replace the value of the “age” field with a new value, you can use the JSON_REPLACE function as follows:

UPDATE example_table
SET json_column = JSON_REPLACE(json_column, '$.age', 35);

After running this query, the updated JSON document in the json_column will be:

{
  "name": "John",
  "age": 35,
  "address": {
    "city": "New York",
    "zip": "10001"
  }
}

In this example, the JSON_REPLACE function is used to replace the value of the “age” field with the new value of 35. The path ‘$.age’ specifies the location of the element to be replaced within the JSON document.

The JSON_REPLACE function is a powerful tool for working with JSON data in MySQL. It can be used to update existing values, insert new values, and recursively update values in nested JSON structures.