How to Use json_replace in MySQL: Syntax and Examples

Use json_replace(json_doc, path, new_value, ...) in MySQL to update existing keys in a JSON document. It replaces values only if the specified path exists; otherwise, it leaves the JSON unchanged.

Syntax

The json_replace function updates values in a JSON document at specified paths only if those paths exist.

• json_doc: The original JSON document.
• path: The JSON path to the key you want to update, e.g., $.key.
• new_value: The new value to set at the path.
• You can specify multiple path, new_value pairs to update several keys at once.

sql

json_replace(json_doc, path, new_value, ...)

Example

This example shows how to replace the value of the key "name" in a JSON document. It updates the value only if the key exists.

sql

SELECT json_replace('{"name": "Alice", "age": 25}', '$.name', 'Bob') AS updated_json;

Output

{"name": "Bob", "age": 25}

Common Pitfalls

1. Path must exist: If the JSON path does not exist, json_replace returns the original JSON unchanged.

2. Use correct JSON path syntax: Paths must start with $. For example, $.key targets the key named "key".

3. To add new keys, use json_set instead: json_replace only updates existing keys and does not add new ones.

sql

SELECT json_replace('{"name": "Alice"}', '$.age', 30) AS result_wrong,
       json_set('{"name": "Alice"}', '$.age', 30) AS result_right;

Output

{"name": "Alice"}
{"name": "Alice", "age": 30}

Quick Reference

Function	Purpose	Behavior
json_replace(json_doc, path, value, ...)	Replace values at existing paths	Only updates if path exists; else returns original JSON
json_set(json_doc, path, value, ...)	Add or update values	Adds new keys or updates existing ones
json_remove(json_doc, path, ...)	Remove keys or values	Deletes specified paths from JSON

Key Takeaways

Use json_replace to update values only if the JSON path exists.

Paths must be valid JSON paths starting with $.

json_replace does not add new keys; use json_set for that.

If the path does not exist, json_replace returns the original JSON unchanged.

You can update multiple keys by providing multiple path-value pairs.