SQL - JSON_SET() in SQL

JSON_SET() is a SQL function used to update or add values inside a JSON column. It allows you to modify JSON data without replacing the entire object.

Syntax

JSON_SET(json_column, '$.key', new_value, '$.key2', new_value2, ...)

• json_column – the column containing JSON data
• $.key – path to the key to update or add
• new_value – new value to assign

Example Table

CREATE TABLE users (

  id INT,

  profile JSON

); 

INSERT INTO users VALUES

(1, '{"name": "Ashika", "age": 22}');

Update Existing Value

UPDATE users

SET profile = JSON_SET(profile, '$.age', 23)

WHERE id = 1;

Result in profile column:

{"name": "Ashika", "age": 23}

Add New Key-Value Pair

UPDATE users

SET profile = JSON_SET(profile, '$.city', 'Bangalore')

WHERE id = 1;

Result in profile column:

{"name": "Ashika", "age": 23, "city": "Bangalore"}

Key Points

• Updates existing keys or adds new keys in JSON
• Keeps other data intact
• Can handle multiple keys in one statement
• Useful for dynamic JSON updates