In MySQL, the JSON_REPLACE() function replaces values in a JSON document and returns the result.

You provide the JSON document as the first argument, followed by the path to replace data at, followed with the value to replace the existing value with.

You can replace data at multiple places in the document if required. To do this, simply provide multiple path/value pairs as required.

Syntax

The syntax goes like this:

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

Where json_doc is the JSON document, path is the path to replace the data at, and val is the new value to replace the existing value.

The path/value pairs are evaluated left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.

If the path/value pair doesn’t exist in the JSON document, it is ignored and has no effect.

The first argument must be a valid JSON document, otherwise an error occurs.

Also, the path argument must be a valid path expression, and it cannot contain a * or ** wildcard, otherwise an error will occur.

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT JSON_REPLACE('{"a": 1, "b": 2, "c": 3}', '$.b', 9) AS 'Result';

Result:

+--------------------------+
| Result                   |
+--------------------------+
| {"a": 1, "b": 9, "c": 3} |
+--------------------------+

In this case, we replaced b‘s value with 9. This is because we specified $.b as the second argument, and 9 as the third.

Here’s another example:

SELECT 
  JSON_REPLACE('{"Name": "Bart", "Age": 10}', '$.Name', 'Nelson - Ha Ha!') AS 'Result';

Result:

+----------------------------------------+
| Result                                 |
+----------------------------------------+
| {"Age": 10, "Name": "Nelson - Ha Ha!"} |
+----------------------------------------+

Example 2 – Non-Existent Path

If you specify a path that doesn’t exist, nothing is removed. The original JSON document is returned without modification.

SELECT 
  JSON_REPLACE('{"Name": "Homer", "Age": 39}', '$.Gender', 'Male') AS 'Result';

Result:

+------------------------------+
| Result                       |
+------------------------------+
| {"Age": 39, "Name": "Homer"} |
+------------------------------+

Example 3 – Arrays

Here’s an example using an array.

SELECT JSON_REPLACE('[1, 2, 3]', '$[0]', 9) AS 'Result';

Result:

+-----------+
| Result    |
+-----------+
| [9, 2, 3] |
+-----------+

Arrays use zero-based numbering, so in this case, the first element is replaced.

Here’s another array example. This time we replace a value in a nested array.

SELECT JSON_REPLACE('[1, 2, [3, 4, 5]]', '$[2][1]', 9) AS 'Result';

Result:

+-------------------+
| Result            |
+-------------------+
| [1, 2, [3, 9, 5]] |
+-------------------+

Example 4 – Multiple Paths

You can specify more than one path in order to replace data at multiple places within the JSON document.

Basic example:

SELECT JSON_REPLACE('[1, 2, 3, 4, 5]', '$[1]', 9, '$[3]', 22) AS 'Result';

Result:

+------------------+
| Result           |
+------------------+
| [1, 9, 3, 22, 5] |
+------------------+

Example 5 – A Larger JSON Document

Here’s an example with a (slightly) larger JSON document.

SET @data = '{  
    "Person": {    
       "Name": "Homer", 
       "Age": 39,
       "Hobbies": ["Eating", "Sleeping"]  
    }
 }';
SELECT JSON_REPLACE(@data, '$.Person.Name', 'Bart', '$.Person.Age', 10, '$.Person.Hobbies[1]', 'Base Jumping') AS 'Result';

Result:

+--------------------------------------------------------------------------------+
| Result                                                                         |
+--------------------------------------------------------------------------------+
| {"Person": {"Age": 10, "Name": "Bart", "Hobbies": ["Eating", "Base Jumping"]}} |
+--------------------------------------------------------------------------------+