How to Use json_set in MySQL: Syntax and Examples

In MySQL, use json_set(json_doc, path, val[, path, val] ...) to update or add values inside a JSON document at specified paths. It returns a new JSON document with the changes without modifying the original. This function is useful for editing JSON data stored in columns.

Syntax

The json_set function takes a JSON document and one or more pairs of path and value. Each path specifies where in the JSON to update or add the value. The function returns a new JSON document with these updates.

• json_doc: The original JSON document.
• path: A string path to the location in the JSON (e.g., $.key or $.array[0]).
• val: The new value to set at the path.

sql

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

Example

This example shows how to update a JSON object by changing the value of a key and adding a new key-value pair.

sql

SELECT json_set('{"name": "Alice", "age": 25}', '$.age', 26, '$.city', 'Paris') AS updated_json;

Output

{"name": "Alice", "age": 26, "city": "Paris"}

Common Pitfalls

Common mistakes include:

• Using incorrect JSON path syntax (paths must start with $ and use dot notation).
• Trying to update a path that does not exist without adding it properly.
• Expecting json_set to modify the original JSON in place (it returns a new JSON value).

sql

/* Wrong: missing $ in path */
SELECT json_set('{"a":1}', '.a', 2);

/* Right: correct path syntax */
SELECT json_set('{"a":1}', '$.a', 2);

Output

NULL
{"a": 2}

Quick Reference

Parameter	Description	Example
json_doc	Original JSON document	'{"key": "value"}'
path	JSON path to update (must start with $)	'$.key'
val	New value to set at path	'new_value'
Return	New JSON document with updates	'{"key": "new_value"}'

Key Takeaways

Use json_set to update or add values inside a JSON document in MySQL.

Paths must start with $ and use dot notation to locate JSON keys or array elements.

json_set returns a new JSON value; it does not change the original data in place.

You can update multiple paths in one call by providing multiple path-value pairs.

Incorrect path syntax or missing $ will cause json_set to return NULL.