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;