13.15 JSON_TRANSFORM Operator REMOVE_SET

JSON_TRANSFORM operator REMOVE_SET removes all occurrences of a given value from an array, as if removing an element from a set.

It removes all occurrences of the value that's specified by the RHS from the array that's targeted by the LHS path expression.

Note: As this is a set operation, the order of all array elements is undefined after the operation.

Example 13-28 JSON_TRANSFORM: Removing an Element From an Array As a Set

This code removes element 2 from the input array, treated as a set.

SELECT json_transform('{"a":[1,2,3]}',
                      REMOVE_SET '$.a' = 2)

Result:

{"a":[ 1,3 ]}

Example 13-29 JSON_TRANSFORM: Failing to Remove an Existing Element From an Array As a Set

This code tries to remove element 5 from the input array, treated as a set. The operation fails because the input array has no element whose value is 5.

SELECT json_transform('{"a":[1,2,3]}',
                      REMOVE_SET '$.a' = 5)

This error is raised:

ORA-40766: error in JSON_TRANSFORM execution
JZN-00420: value for DELETE not found in target set

Example 13-30 JSON_TRANSFORM: Using Handler IGNORE ON ABSENT To Prevent REMOVE_SET Failure For Nonexisting Elements

The code here is the same as that in Example 13-29, except that it uses json_transform handler IGNORE ON ABSENT to ignore elements to be removed if they aren't present in the input array.

SELECT json_transform('{"a":[1,2,3]}',
                      REMOVE_SET '$.a' = 5 IGNORE ON ABSENT)

Result: the array is not changed; attempt to remove nonexisting element 5 is ignored.

{"a":[ 1,2,3 ]}

These are the handlers allowed for operator REMOVE_SET:

• ERROR ON MISSING (default), IGNORE ON MISSING.

• NULL ON NULL (default), IGNORE ON NULL, ERROR ON NULL.

• ERROR ON EMPTY (default), IGNORE ON EMPTY, NULL ON EMPTY.

• IGNORE IF ABSENT.