13.6 JSON_TRANSFORM Operator COPY

JSON_TRANSFORM operator COPY replaces the elements of an array.

It replaces the elements of the array that's targeted by the LHS path expression with the values that are specified by the RHS. The operation can accept a sequence of multiple values matched by the RHS path expression. (An error is raised if the LHS path expression does not target an array.)

Example 13-12 JSON_TRANSFORM: Copying Multiple RHS Values To an LHS Array

This example transforms the input object by using the value of field b to change the value of field a. It then removes field b from the resulting object.

Operation COPY copies the multiple values from path $.b.x (which are 1, 2, and 3, in sequence) to the array that's the value of field a. 

SELECT json_transform('{"a":[], "b":[ {"x":1}, {"x":2}, {"x":3} ]}',
                      COPY '$.a' = PATH '$.b.x',
                      REMOVE '$.b');

Result:

{"a":[ 1, 2, 3 ]}

These are the handlers allowed for operator COPY:

  • CREATE ON MISSING (default), IGNORE ON MISSING, ERROR ON MISSING, NULL ON MISSING.

  • NULL ON NULL (default), IGNORE ON NULL, ERROR ON NULL.

  • IGNORE ON EMPTY (default), ERROR ON EMPTY.

Parent topic: Oracle SQL Function JSON_TRANSFORM