13.20 JSON_TRANSFORM Operator UNION
JSON_TRANSFORM Operator UNION adds missing
array elements from a specified set of values. This is a set-union
operation.
It adds the values specified by the RHS (right-hand-side) of the operation to the array that's targeted by the LHS (left-hand-side) path expression, unless they are already present anywhere in the array. That is, it treats the array as a set, ensuring that there are no duplicate elements. The operation can accept a sequence of multiple values matched by the RHS path expression.
Example 13-41 JSON_TRANSFORM: Union of Two Arrays As Sets
Array a in the resulting object is the set-union of
input arrays $.a and $.b. Array b
in the resulting object is unchanged.
SELECT json_transform('{"a":[ 1, 2, 3 ], "b":[ 2, 5, 3, 4 ]}',
UNION '$.a' = PATH '$.b')Result:
{"a":[ 1,2,3,5,4 ], "b":[ 2,5,3,4 ]}Note: As this is a set operation, the order of all array elements is undefined after the operation.
These are the handlers allowed for operator UNION:
-
ERROR ON MISSING(default),IGNORE ON MISSING,CREATE ON MISSING,NULL ON MISSING. -
ERROR ON MISMATCH(default). -
NULL ON NULL(default),IGNORE ON NULL,ERROR ON NULL.