MySQL JSON_REPLACE() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_REPLACE() function to replace existing values in a JSON document and return the updated document.

Introduction to MySQL JSON_REPLACE() function

The JSON_REPLACE() function replaces existing values in a JSON document and returns the updated document.

Here’s the syntax of the JSON_REPLACE() function:

JSON_REPLACE(json_doc, path, value[, path, val] ...)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• json_doc: This is the JSON document you want to replace data.
• path: The JSON path that specifies the positions within the json_doc where you want to update the new value.
• value: The value you want to update.

The JSON_REPLACE() function returns the updated JSON document. It returns NULL if any argument is NULL.

If you have multiple path/value pairs, the function will process them one by one starting from the left. It uses the result of the first evaluation for the next path/value pair.

The function only overwrites the existing document value with a new value for an existing path in the document. If the path doesn’t exist, the function takes no effect.

MySQL JSON_REPLACE() function examples

Let’s take some examples of using the JSON_REPLACE() function.

1) Replacing an existing value

The following example uses the JSON_REPLACE() function to replace an existing value with a new value in a JSON document:

SELECT 
  JSON_REPLACE(
    '{"name": "Jane Doe", "age": 22}', '$.name', 
    'Jane Smith'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------------------------+
| result                            |
+-----------------------------------+
| {"age": 22, "name": "Jane Smith"} |
+-----------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Updating nested objects

The following example uses the JSON_REPLACE() function to update values within nested objects:

SELECT 
  JSON_REPLACE(
    '{"person": {"name": "John", "address": {"city": "New York"}}}', 
    '$.person.address.city', 'Los Angeles'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+------------------------------------------------------------------+
| result                                                           |
+------------------------------------------------------------------+
| {"person": {"name": "John", "address": {"city": "Los Angeles"}}} |
+------------------------------------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, we use the JSON_REPLACE() function to update the city within the nested object.

3) Handling non-existing path example

If you attempt to replace a value at a path that doesn’t exist in the JSON document, the JSON_REPLACE() function will simply leave the document unchanged. For example:

SELECT 
  JSON_REPLACE(
    '{"name": "John", "age": 22}', '$.city', 
    'London'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------------------+
| result                      |
+-----------------------------+
| {"age": 22, "name": "John"} |
+-----------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

4) Replacing with multiple path/value pairs

The following example uses the JSON_REPLACE() function to update the name, and a city in a single operation:

SELECT 
  JSON_REPLACE(
    '{"name": "Jane Doe", "age": 25, "city": "New York"}', 
    '$.name', 'Jane Smith', '$.age', 26, '$.city', 
    'Los Angeles'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------------------------------------------------+
| result                                                   |
+----------------------------------------------------------+
| {"age": 26, "city": "Los Angeles", "name": "Jane Smith"} |
+----------------------------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, the JSON_REPLACE() function replaces the name first, and uses the updated document to replace the age, and uses the modified document to replace the city.

Summary

• Use the MySQL JSON_REPLACE() function to replace existing values in a JSON document.