In MySQL, the JSON_SET() function inserts or updates values in a JSON document and returns the result.

You provide the JSON document as the first argument, followed by the path to insert into, followed by the value to insert. You can provide multiple path/value pairs if you need to update multiple values.

Syntax

The syntax goes like this:

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

Where:

• json_doc is the JSON document.
• path is the path of the element for which to insert data or update the value at.
• val is the new 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.

An error occurs if the first argument is not a valid JSON document, or if any path argument is not a valid path expression or contains a * or ** wildcard.

Example 1 – Insert a Value

Here’s an example to demonstrate how to insert a value using this function.

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

Result:

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

In this case, the 3rd argument is inserted into the array at the specified path. I use $.c for the path and 3 for the value to be inserted. This results in a key/value pair of "c": 3.

Here’s another example:

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

Result:

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

Example 2 – Update a Value

The previous example inserted a value. This example updates an existing value.

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

Result:

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

Here’s another example:

SELECT 
  JSON_SET('{"Name": "Homer", "Gender": "Male"}', '$.Name', "Bart") AS 'Result';

Result:

+------------------------------------+
| Result                             |
+------------------------------------+
| {"Name": "Bart", "Gender": "Male"} |
+------------------------------------+

Example 3 – Arrays

You can insert values into an array by using a path past the end of the existing array. Example:

SELECT JSON_SET('[1, 2, 3]', '$[3]', 4) AS 'Result';

Result:

+--------------+
| Result       |
+--------------+
| [1, 2, 3, 4] |
+--------------+

However, if you use a path that already exists in the array, the value will be updated:

SELECT JSON_SET('[1, 2, 3]', '$[1]', 4) AS 'Result';

Result:

+-----------+
| Result    |
+-----------+
| [1, 4, 3] |
+-----------+

If the existing value is not an array, it is autowrapped as an array, then extended with the new value. Example:

SELECT JSON_SET('{"a": 1, "b": 2}', '$[2]', 3) AS 'Result';

Result:

+-----------------------+
| Result                |
+-----------------------+
| [{"a": 1, "b": 2}, 3] |
+-----------------------+

Example 4 – Multiple Values

You can insert/update multiple values at once. Simply add each key/value pair separated by a comma. Example:

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

Result:

+-------------------------------+
| Result                        |
+-------------------------------+
| {"a": 9, "b": 2, "c": "Bart"} |
+-------------------------------+

Example 5 – A Larger JSON Document

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

SET @data = '{  
    "Person": {    
       "Name": "Homer", 
       "Hobbies": ["Eating", "Sleeping"]  
    }
 }';
SELECT JSON_SET(@data, '$.Person.Name', 'Bart', '$.Person.Hobbies[1]', "Mischief") AS 'Result';

Result:

+-----------------------------------------------------------------+
| Result                                                          |
+-----------------------------------------------------------------+
| {"Person": {"Name": "Bart", "Hobbies": ["Eating", "Mischief"]}} |
+-----------------------------------------------------------------+

Also see JSON_INSERT() for inserting values without replacing existing values, and JSON_REPLACE() for replacing only existing values.