13.10 JSON_TRANSFORM Operator MERGE

JSON_TRANSFORM operator MERGE merges specified fields into an object (possibly creating the object).

It adds fields (name and value) matched by the RHS path expression as members of the object that's targeted by the LHS path expression.

If a field specified by the RHS is already present in the LHS then the field value from the RHS is used in the result. If the same field is specified more than once by the RHS then the last one in the sequence of matches is used in the result.

Tip:

You can use handler CREATE ON MISSING to create a missing object targeted by the LHS, filling it from the fields specified by the RHS.

Example 13-19 Merging Two Arrays As Sets

Array a in the object resulting from this MERGE operation is the merging of the arrays $.a and $.b (that is, input arrays a and b). Field z from input array b is added to array a in the result. Field y from array b is used in array a of the result; that is, array b's value of y is used as the new value of array a's field y.

Array b in the resulting object is unchanged. 

SELECT json_transform('{"a":{"x":1, "y":2}, "b":{"y":3, "z":4}}',
                      MERGE '$.a' = PATH '$.b')

Result:

{"a":{"x":1, "y":3, "z":4}, "b":{"y":3, "z":4}}

These are the handlers allowed for operator MERGE:

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

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

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

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

Parent topic: Oracle SQL Function JSON_TRANSFORM