13.3 JSON_TRANSFORM Operator ADD_SET
JSON_TRANSFORM operator ADD_SET adds a
missing value to an array, as if adding an element to a set.
It adds the value that's specified by the RHS of the operation to the array that's targeted by the LHS path expression, but only if the value is not already one of its elements. That is, it treats the array as if it were a set, so that the value is not added as a duplicate.
Note: As this is a set operation, the order of all array elements is undefined after the operation.
Example 13-6 JSON_TRANSFORM: Adding an Element To an Array As a Set
This code adds element 4 to the input array, treated as a set.
SELECT json_transform('{"a":[1,2,3]}',
ADD_SET '$.a' = 4)Result:
{"a":[ 1,2,3,4 ]}Example 13-7 JSON_TRANSFORM: Failing to Add an Existing Element To an Array As a Set
This code tries to add element 2 to the input array, treated as a
set. The operation fails because the input array already has an element
2.
SELECT json_transform('{"a":[1,2,3]}',
ADD_SET '$.a' = 2)This error is raised:
ORA-40766: error in JSON_TRANSFORM execution
JZN-00419: value for ADD already in target setExample 13-8 JSON_TRANSFORM: Using Handler IGNORE IF PRESENT To Prevent ADD_SET Failure For Pre-Existing Elements
The code here is the same as that in Example 13-7, except that it uses json_transform handler IGNORE IF
PRESENT to ignore elements to be added if they're already present in
the input array.
SELECT json_transform('{"a":[1,2,3]}',
ADD_SET '$.a' = 2 IGNORE IF PRESENT)Result: the array is not changed; attempt to add pre-existing element
2 is ignored.
{"a":[ 1,2,3,4 ]}These are the handlers allowed for operator ADD_SET:
-
ERROR ON MISSING(default),IGNORE ON MISSING,CREATE ON MISSING. Create means insert a singleton array at the targeted location. The single array element is the value of the SQL result expression. -
NULL ON NULL(default),IGNORE ON NULL,ERROR ON NULL. -
ERROR ON EMPTY(default),IGNORE ON EMPTY,NULL ON EMPTY. -
IGNORE IF PRESENT.