MySQL JSON_SET

The JSON_SET function in MySQL is used to insert or update values in a JSON document. It allows you to modify a JSON document by adding new key-value pairs or updating existing ones. The function effectively combines the functionality of JSON_INSERT and JSON_REPLACE, simplifying the process of modifying JSON data within a single statement.

Syntax

The syntax for the JSON_SET function is as follows:

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

json_doc: The original JSON document you want to modify.
path: A JSON path expression specifying the location where you want to insert or update the value.
val: The new value you want to set at the specified path.

You can provide multiple pairs of path and val parameters to update or insert multiple values in a single JSON_SET function call.

Example

Here’s an example of how you can use the JSON_SET function:

Let’s say you have a table called employees with a column named info that stores JSON data. The JSON document in the info column looks like this:

{
  "name": "John Doe",
  "age": 30,
  "department": "Engineering"
}

Now, suppose you want to update the employee’s age to 31 and add a new key-value pair for the city. You can use JSON_SET as follows:

UPDATE employees
SET info = JSON_SET(info, '$.age', 31, '$.city', 'New York')
WHERE employee_id = 1;

After running this query, the info column for the employee with employee_id 1 will be updated to:

{
  "name": "John Doe",
  "age": 31,
  "department": "Engineering",
  "city": "New York"
}

In this example:

‘$age’ is the JSON path expression specifying the location of the age key.
31 is the new value for the age key.
‘$city’ is the JSON path expression specifying the location where you want to insert the new city key.
‘New York’ is the value for the new city key.

Keep in mind that JSON_SET doesn’t modify the original JSON document in place; instead, it returns a new JSON document with the specified modifications. If you want to update the original document in a table, you need to use it in combination with an UPDATE statement, as shown in the example above.