Skip to main content

JSON_REMOVE

Removes specified elements from a JSON document using path expressions. If a specified path does not exist in the document, it is ignored. Sequential processing of paths can affect the structure for subsequent removals.

See also:
[SELECT] [JSON_EXTRACT]

Syntax​

SELECT JSON_REMOVE(json_document, path1, path2, ...) FROM <multipartIdentifier>;

Arguments​

json_document
The JSON document from which elements are to be removed.

path1, path2, ...
One or more path expressions pointing to elements in the JSON document to be removed.

Return Value(s)​

Returns a copy of the JSON document with the specified elements removed. If no paths are provided, returns the input JSON reformatted with excess whitespace removed.

Examples​

Remove an Element from a JSON Array​

-- Example: Remove the third element from a JSON array
SELECT JSON_REMOVE('[0,1,2,3,4]', '$[2]') AS modified_json;

Output: '[0,1,3,4]'

Remove Multiple Elements from a JSON Array​

-- Example: Remove the first and third elements from a JSON array
SELECT JSON_REMOVE('[0,1,2,3,4]', '$[2]', '$[0]') AS modified_json;

Output: '[1,3,4]'

Remove Last Element Using Negative Index​

-- Example: Remove the last and first elements from a JSON array
SELECT JSON_REMOVE('[0,1,2,3,4]', '$[#-1]', '$[0]') AS modified_json;

Output: '[1,2,3]'

Remove Non-existent Path​

-- Example: Attempt to remove a non-existent key from a JSON object
SELECT JSON_REMOVE('{"x":25,"y":42}', '$.z') AS modified_json;

Output: '{"x":25,"y":42}'

Remove Existing Key from JSON Object​

-- Example: Remove the 'y' key from a JSON object
SELECT JSON_REMOVE('{"x":25,"y":42}', '$.y') AS modified_json;

Output: '{"x":25}'

Edge Case: Removal with Root Path​

-- Example: Attempt to remove using the root path
SELECT JSON_REMOVE('{"x":25,"y":42}', '$') AS modified_json;

Output: NULL

These examples illustrate how json_remove() can be used to manipulate JSON data dynamically, addressing various scenarios from simple deletions to handling edge cases in data structure modifications.