JSON_TRANSFORM Operator REPLACE

13.17 JSON_TRANSFORM Operator REPLACE

JSON_TRANSFORM operator REPLACE replaces the data that's targeted by the LHS path expression with the value of the RHS SQL expression.

(REPLACE has the effect of SET with clause IGNORE ON MISSING.)

Example 13-32 JSON_TRANSFORM: Replacing a JSON Value

This code replaces the value of field a with the value 2. There is no field b in the input data, so the second REPLACE operation is ignored (because the default handler is IGNORE ON MISSING).

SELECT json_transform('{"a":1}',
                      REPLACE '$.a' = 2,
                      REPLACE '$.b' = 3);

Result:

{"a":2}

These are the handlers allowed for operator REPLACE:

REPLACE ON EXISTING (default).
IGNORE ON MISSING (default), ERROR ON MISSING, CREATE ON MISSING.
NULL ON NULL (default), IGNORE ON NULL, ERROR ON NULL, REMOVE ON NULL.
NULL ON EMPTY (default), IGNORE ON EMPTY, ERROR ON EMPTY.
ERROR ON ERROR (default), IGNORE ON ERROR.

Parent topic: Oracle SQL Function JSON_TRANSFORM