MySQL JSON_SET() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_SET() function to replace existing values or add non-existing values to a JSON document.

Introduction to MySQL JSON_SET() function

The JSON_SET() function allows you to replace existing values or add non-existing values to a JSON document.

Here’s the syntax of the JSON_SET() 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 or add data.
• path: The JSON path that specifies the positions within the json_doc where you want to replace or add data.
• value: The value you want to replace or add.

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

When you use multiple path/value pairs, the JSON_SET() function will process them one by one starting from the left. It uses the updated JSON document of the first evaluation for the next one till the last one.

MySQL JSON_SET() function examples

We’ll take some examples of using the JSON_SET() function.

1) Replacing an existing value

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

SELECT 
  JSON_SET(
    '{"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)

In this example, we use the JSON_SET() function to replace the name in the object of the JSON document with a new one.

The path expression $.name specifies the name property of the object.

2) Replacing values in nested objects

The following example uses the JSON_SET() function to replace value within a nested object:

SELECT 
  JSON_SET(
    '{"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_SET() function to update the city within the nested object.

The path expression $.person.address.city specifies the city property of the address object of the person object.

3) Adding a nonexisting value

If you set a value at a path that doesn’t exist in the JSON document, the JSON_SET() function will add the element. For example:

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

Output:

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

4) Replacing/adding multiple path/value pairs

The following example uses the SET() function to both update an existing value and add a new value to a JSON document:

SELECT 
  JSON_SET(
    '{"name": "Jane Doe", "age": 25, "city": "New York"}', 
    '$.name', 'Jane Smith', 
    '$.job', 'MySQL Specialist'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------------------------------------------------------------------------+
| result                                                                           |
+----------------------------------------------------------------------------------+
| {"age": 25, "job": "MySQL Specialist", "city": "New York", "name": "Jane Smith"} |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example:

• The path $.name locates the name property that exists in the JSON document, therefore, the function replaces the current name "Jane Doe" with the new one "Jane Smith".
• The path $.job locates the job property that doesn’t exist in the JSON document so the function inserts the new property job with the value "MySQL Specialist".

Summary

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