How to update JSON columns in MySQL

Use JSON_SET to add or update Fields

JSON_SET adds a new key-value pair if the key doesn’t exist, or updates the value if the key already exists.

UPDATE your_table
SET json_column = JSON_SET(json_column, '$.field_name', 'new_value')
WHERE id = 1;

$.field_name: Represents the path to the JSON field you want to update (use $ for the root, followed by .field_name for nested fields).

• ‘new_value’: The new value for the field (you can also update it with JSON objects, numbers, etc.).

Example

Assume you have a table called employees with a details column that stores JSON data:

{
  "name": "John Doe",
  "age": 30,
  "address": {
    "city": "New York",
    "state": "NY"
  }
}

To update the age field and the city inside the address, you can use:

UPDATE employees
SET details = JSON_SET(details, '$.age', 31, '$.address.city', 'Los Angeles')
WHERE id = 1;