JSON_REMOVE

Removes one or more elements from a JSON document at specified paths.

This function returns a new JSON document that is a copy of the input json with the elements at the given paths removed. Any paths that do not exist in the original JSON are ignored.

Removing an element from a JSON array causes the elements to its right to be shifted to the left to fill the vacancy. Removing a key/value pair from a JSON object removes both the key and its value.

Example:

val jsonCol = jsonColumn("data") // Contains '{"a": [1, 2, "3", 4], "b": 5}'

// Remove the element at index 2 of array 'a' and the key 'b'
// SELECT JSON_REMOVE(data, '$.a[2]', '$.b')
// Result: '{"a":[1,2,4]}'
select(JSON_REMOVE(jsonCol, "$.a[2]", "$.b")).from(MyTable).execute()

// Attempt to remove a non-existent path (will be ignored)
// SELECT JSON_REMOVE(data, '$.c')
// Result: '{"a":[1,2,"3",4],"b":5}'
select(JSON_REMOVE(jsonCol, "$.c")).from(MyTable).execute()

Parameters

See also