How to Use json_remove in MySQL: Syntax and Examples

Use the json_remove(json_doc, path[, path] ...) function in MySQL to delete one or more elements from a JSON document by specifying their paths. It returns a new JSON document with the specified elements removed without changing the original data.

Syntax

The json_remove function takes a JSON document and one or more paths to elements you want to remove. Each path points to a key or index inside the JSON structure.

• json_doc: The JSON data you want to modify.
• path: The location of the element to remove, written as a JSON path string (e.g., $.key or $.array[0]).

The function returns a new JSON document with the specified elements removed.

sql

json_remove(json_doc, path[, path] ...)

Example

This example shows how to remove a key and an array element from a JSON document using json_remove.

sql

SELECT
  json_remove(
    '{"name": "Alice", "age": 30, "hobbies": ["reading", "cycling", "swimming"]}',
    '$.age',
    '$.hobbies[1]'
  ) AS updated_json;

Output

{"name": "Alice", "hobbies": ["reading", "swimming"]}

Common Pitfalls

Common mistakes when using json_remove include:

• Using incorrect JSON path syntax, which causes the function to do nothing or return the original JSON.
• Trying to remove a path that does not exist, which leaves the JSON unchanged.
• Expecting json_remove to modify the original JSON column directly; it only returns a new JSON value.

Always verify your JSON paths and update your table data explicitly if needed.

sql

/* Wrong: Incorrect path syntax, no removal happens */
SELECT json_remove('{"a":1}', '$.b') AS result_wrong;

/* Right: Correct path, removes key 'a' */
SELECT json_remove('{"a":1}', '$.a') AS result_right;

Output

result_wrong: {"a": 1}
result_right: {}

Quick Reference

Parameter	Description
json_doc	The JSON document to modify
path	JSON path string to the element to remove
Return	New JSON document with specified elements removed

Key Takeaways

Use json_remove to delete elements from JSON data by specifying their JSON paths.

json_remove returns a new JSON value; it does not change the original data automatically.

Ensure JSON paths are correct to remove the intended elements.

You can remove multiple elements by providing multiple paths.

If a path does not exist, json_remove leaves the JSON unchanged.