13.4 JSON_TRANSFORM Operator APPEND
JSON_TRANSFORM operator APPEND appends
values to an array.
It appends the values that are specified by the RHS of the operation 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.
If the RHS targets an array then the LHS array is updated by appending the elements of the RHS array to it, in order. See Example 13-10.
Example 13-9 Appending an Element To an Array Inserts It at the End
This code adds string "hello" to the end of array
a.
SELECT json_transform('{"a":[1,2,3]}',
APPEND '$.a' = 'hello');Result — modified input data:
{"a":[ 1,2,3,"hello" ]}This code using operator INSERT is equivalent;
APPEND has the effect of INSERT for an array
position of last+1:
SELECT json_transform('{"a":[1,2,3]}',
INSERT '$.a[last+1]' = 'hello');Example 13-10 JSON_TRANSFORM: Using a Variable to Append Multiple Elements To an Array
This example appends 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-24.)
The elements to append, and the order in which to append 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 APPEND as the value of
SQL/JSON variable $var. The elements to be appended are all of the
array elements, in order; they are specified in the RHS path expression using
[*].
SELECT json_transform('{"a": [ {"b": [ 1,2 ]},
{"b": [ 3,4 ]} ]}',
SET '$var' = json_array(5, 'cat'),
APPEND '$.a[*].b' = PATH '$var[*]');Result — modified input data:
{"a":[ {"b":[ 1,2,5,"cat" ]},
{"b":[ 3,4,5,"cat" ]} ]}Tip:
You can use handler CREATE ON MISSING to create a
missing array-valued field targeted by the LHS, filling it from the values specified
by the RHS. For example:
SELECT json_transform('{"a":[1,2,3]}',
APPEND '$.b' = PATH '$.a[0,2]'
CREATE ON MISSING);Result — modified input data:
{"a":[ 1,2,3 ], "b":[ 1,3 ]}These are the handlers allowed for operator APPEND (they are the same as
for operator PREPEND):
-
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.