How to Use json_insert in MySQL: Syntax and Examples

Use json_insert(json_doc, path, value[, path, value] ...) in MySQL to add new key-value pairs or elements into a JSON document only if the specified path does not exist. It inserts data at the given JSON path without overwriting existing values.

Syntax

The json_insert function takes a JSON document and one or more pairs of JSON paths and values. It inserts the value at the specified path only if that path does not already exist in the JSON document.

• json_doc: The original JSON document.
• path: The location in the JSON document where you want to insert the value. It uses JSON path syntax like $.key or $.array[0].
• value: The value to insert at the given path.

sql

json_insert(json_doc, path, value[, path, value] ...)

Example

This example shows how to insert a new key-value pair into a JSON document only if the key does not exist. It also shows that existing keys are not overwritten.

sql

SELECT json_insert('{"name": "Alice", "age": 25}', '$.city', 'New York') AS result;

SELECT json_insert('{"name": "Alice", "age": 25}', '$.age', 30) AS result;

Output

{"name": "Alice", "age": 25, "city": "New York"}
{"name": "Alice", "age": 25}

Common Pitfalls

One common mistake is expecting json_insert to overwrite existing keys. It does not change values if the path already exists. To update existing keys, use json_set instead.

Another pitfall is incorrect JSON path syntax, which causes errors or no changes.

sql

/* Wrong: expecting overwrite */
SELECT json_insert('{"key": "old"}', '$.key', 'new') AS wrong_result;

/* Right: use json_set to overwrite */
SELECT json_set('{"key": "old"}', '$.key', 'new') AS right_result;

Output

{"key": "old"}
{"key": "new"}

Quick Reference

Function	Purpose	Overwrite Existing Keys?
json_insert(json_doc, path, value)	Insert value only if path does not exist	No
json_set(json_doc, path, value)	Insert or update value at path	Yes
json_replace(json_doc, path, value)	Update value only if path exists	Yes

Key Takeaways

Use json_insert to add new keys or elements only if they do not exist in the JSON document.

json_insert does not overwrite existing values; use json_set to update or insert.

Always use correct JSON path syntax starting with $. for paths.

json_insert can take multiple path-value pairs to insert several values at once.

Check your JSON document structure before inserting to avoid errors.