13.8 JSON_TRANSFORM Operator INTERSECT
JSON_TRANSFORM operator INTERSECT removes
array elements other than those in a specified set of values. This is a
set-intersection operation.
It removes all elements of the array that's targeted by the LHS (left-hand-side) path expression that are not equal to any value specified by the RHS (right-hand-side). The operation can accept a sequence of multiple values matched by the right-hand-side (RHS) path expression.
Note: As this is a set operation, the order of all array elements is undefined after the operation.
Example 13-16 JSON_TRANSFORM: Intersecting Two Arrays As Sets
Array a in the resulting object is the set-intersection
of input arrays $.a and $.b. Array
b in the resulting object is unchanged.
SELECT json_transform('{"a":[ 1, 2, 3 ], "b":[ 2, 3, 4 ]}',
INTERSECT '$.a' = PATH '$.b[*]')Result:
{"a":[ 3,2 ], "b":[ 2,3,4 ]}Note that the RHS path expression uses [*], to target all elements
of array b. Without [*] the example would return
{"a":[], "b":[2,3,4]}, because the entire array
b is treated as a single value. Since array a does not contain
that value ([2,3,4]), the intersection is the empty set
(array).
Note:
PREPEND and INTERSECT are the only LHS
array-targeting operations for which it really matters that multiple values matching
an RHS path expression are handled together, as a block, as opposed to being
handled, in order, individually.
For example, if RHS path-matching values 3 and
4 are APPENDed, together as a unit, to
LHS-targeted array [1,2], the result is the same as if elements
3 and 4 are appended individually, in
turn, to the array. The result is in both cases [1,2,3,4].
Adding 3, then 4 is the same as adding
3 and 4 together, keeping them in sequence
order.
But in the case of PREPEND, if 3 and
4 are prepended together the result is
[3,4,1,2], whereas if they're prepended individually, in
turn, the result is [4,3,1,2].
This is the behavior of PREPEND: values 3 and
4, in that order, prepended together to input array
a ([ 1,2 ]).
SELECT json_transform('{"a":[ 1,2 ], b:[ {c:3}, {c:4} ]}',
PREPEND '$.a' = PATH '$.b[*].c')Result:
{"a":[ 3,4,1,2 ], "b":[ {"c":3}, {"c":4} ]}}The same consideration holds for INTERSECT, compared to
UNION. If multiple RHS values (with at least two that differ),
were handled individually by INTERSECT, instead of together, then
the result would always be the empty array, []. In effect, after
handling the first value in the sequence the resulting intersection would be the
singleton array with that value. Handling the next value in the sequence would
result in an empty intersection.
For example, intersecting [1,2,3,4] with multiple values
3, then 4
individually would first produce [3] (removing all but
3) and then [] (removing all but
4).
These are the handlers allowed for operator INTERSECT:
-
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.