13.13 JSON_TRANSFORM Operator PREPEND
JSON_TRANSFORM operator PREPEND prepends
values to an array.
It prepends the values that are specified by the RHS to the array that's targeted by the LHS path expression. It can accept a sequence of multiple values matched by the RHS path expression.
An error is raised if the LHS path expression targets an existing field whose value is not an array.
When prepending a single value, PREPEND has the effect of
INSERT for an array position of 0.
If the RHS targets an array then the LHS array is updated by prepending the elements of the RHS array to it, in order.
Example 13-23 JSON_TRANSFORM: Prepending an Element To an Array
This code prepends the string "hello" to input array
a ([1,2]):
SELECT json_transform('{"a":[1,2]}',
INSERT '$.a' = '0')Result:
{"a":[ 0,1,2 ]}Example 13-24 Using a Variable to Prepend Multiple Elements To an Array with JSON_TRANSFORM
This example prepends values 5 and
"cat", in that order, to each array a.b, that
is, to arrays [ 1,2 ] and [ 3,4 ]. (This is
similar to Example 13-10.)
The elements to prepend, and the order in which to prepend them, are
provided here by an array, [ 5,"cat" ], which in this case
is the result of evaluating an RHS SQL expression that invokes SQL/JSON function
json_array.
This array is passed to operator PREPEND as the value
of SQL/JSON variable $var. The elements to be prepended are all of
the array elements, in order; they are specified in the RHS path expression using
[*]. The elements are prepended together, so their order
in the RHS array is reflected in the result.
SELECT json_transform('{"a": [ {"b": [ 1,2 ]},
{"b": [ 3,4 ]} ]}',
SET '$var' = json_array(5, 'cat'),
PREPEND '$.a[*].b' = PATH '$var[*]');Result — modified input data:
{"a":[ {"b":[ 5,"cat",1,2 ]},
{"b":[ 5,"cat",3,4 ]} ]}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 PREPEND (they are the same
as for operator APPEND):
-
ERROR ON MISSING(default),IGNORE ON MISSING,CREATE ON MISSING,NULL ON MISSING. Create means insert a singleton array at the targeted location. The single array element is the value of the SQL result expression. -
ERROR ON MISMATCH(default),IGNORE ON MISMATCH,REPLACE ON MISMATCH,CREATE ON MISMATCH. -
NULL ON NULL(default),IGNORE ON NULL,ERROR ON NULL. -
IGNORE ON EMPTY(default),ERROR ON EMPTY.