Operations for function json_transform have associated handlers that override the default behavior in some uncommon or unexpected situations.

These are the handlers allowed for each operation, in descending order of how commonly they're likely to be used. In general, the default behavior for a given type of handler depends on the operator involved.

• ON ERROR — Specifies what happens if trying to resolve an RHS path results in an error.

  • NULL ON ERROR — Return JSON null.

  • ERROR ON ERROR — Raise an error.

  • IGNORE ON ERROR — Leave the data unchanged (no modification).

  For example, in this code, handler IGNORE ON ERROR causes the error to be ignored of not being able to convert the value "cat" of field a to a number. The INSERT operation has no effect: it is a no-op. (ERROR ON ERROR is the default behavior for operator INSERT.)

  SELECT json_transform('{"a":"cat"}',
                        INSERT '$.b' = PATH '$.a.number()'
                        IGNORE ON ERROR);

• ON EMPTY — Specifies what happens if a value targeted by an RHS path expression is missing.

  • NULL ON EMPTY — Return JSON null.

  • ERROR ON EMPTY — Raise an error.

  • IGNORE ON EMPTY — Leave the data unchanged (no modification).

  For example, in this code, handler ERROR ON EMPTY causes an error to be raised because the RHS targets no data; there's no field x in the input data. (IGNORE ON EMPTY is the default behavior for operator APPEND.)

  SELECT json_transform('{"a":null, "b":[ 1,2,3 ]}',
                        APPEND '$.b' = PATH '$.x'
                        ERROR ON EMPTY);

• ON EXISTING — Specifies what happens if an LHS path expression matches the data; that is, it targets at least one value. (This handler is irrelevant, and so is ignored, for an LHS that is a SQL/JSON variable.)

  • ERROR ON EXISTING — Raise an error.

  • IGNORE ON EXISTING — Leave the data unchanged (no modification).

  • REPLACE ON EXISTING — Replace data at the targeted location with the value specified by the RHS.

  • REMOVE ON EXISTING — Remove the targeted data.

  For example, in this code, handler IGNORE ON EXISTING does not change the value of field created, because that field already exists. The SET operation has no effect: it is a no-op. (REPLACE ON EXISTING is the default behavior for operator SET.)

  SELECT json_transform('{"created":"2025-04-09T22:07:06"}',
                        SET '$.created' = SYSDATE
                        IGNORE ON EXISTING);

• ON MISSING — Specifies what happens if a LHS path expression does not match the data; that is, it does not target at least one value.

  The default ON MISSING handler depends on the operator involved.

  • ERROR ON MISSING — Raise an error.

  • IGNORE ON MISSING — Leave the data unchanged (no modification).

  • CREATE ON MISSING — Add data at the targeted location.

    For example, in this code, handler ERROR ON MISSING causes an error to be raised because targeted field a doesn't exist. (REPLACE ON MISSING is the default behavior for operator RENAME.)

    SELECT json_transform('{"x":null}',
                          RENAME '$.a' = 'b'
                          ERROR ON MISSING);

    ORA-40768: field with name 'a' does not exist

• ON MISMATCH — Specifies what happens if the type of the data targeted by the LHS is unexpected. It applies, in particular, to a MERGE operation, which requires the (LHS) targeted data to be an object, and to operations that require the targeted data to be an array.

  • NULL ON MISMATCH — Return JSON null.

  • ERROR ON MISMATCH — Raise an error.

  • IGNORE ON MISMATCH — Leave the data unchanged (no modification).

  • CREATE ON MISMATCH — Wrap the targeted value in a (singleton) array, then apply the operation.

  • REPLACE ON MISMATCH — Replace the targeted value with the empty array ([]), then apply the operation.

    For example, in this code, handler IGNORE ON MISMATCH causes the error to be ignored of trying to merge nonobject field a with the object {"b":2}. (ERROR ON MISSING is the default behavior for operator MERGE.)

    SELECT json_transform('{"a":"notAnObject"}',
                          MERGE '$.a' = PATH '$var'
                          IGNORE ON MISMATCH
                          PASSING JSON('{"b":2}') AS "var");

    Result — the input data is unchanged:

    {"a":"notAnObject"}

    In the following code, handler CREATE ON MISMATCH overrides the default behavior of raising an error because of trying to append to a targeted value ("dog") that's not an array. That mismatched value is treated as a singleton array (["dog"])), to which the RHS value ("cat") is appended as an element.

    SELECT json_transform('{"a":"dog"}',
                          APPEND '$.a' = 'cat'
                          CREATE ON MISMATCH);
    

    Result — modified input data:

    {"a":[ "dog","cat" ]}

    In the following code, handler REPLACE ON MISMATCH again overrides the default behavior of raising an error because of trying to append to a targeted value ("dog") that's not an array. But in this case the mismatched LHS value ("dog") is replaced by the RHS value ("cat") and then wrapped as a singleton array ([ "cat" ])).

    SELECT json_transform('{"a":"dog"}',
                          APPEND '$.a' = 'cat'
                          REPLACE ON MISMATCH);

    Result — modified input data:

    {"a":[ "cat" ]}

• ON NULL — Specifies what happens if the value of the RHS SQL result expression is NULL. (This handler applies only when the RHS is a SQL expression. If the RHS uses keyword PATH then ON NULL is ignored.)

  • NULL ON NULL — Use a JSON null value for the targeted location.

  • ERROR ON NULL — Raise an error.

  • IGNORE ON NULL — Leave the data unchanged (no modification).

  • REMOVE ON NULL — Remove the targeted data.

  For example, in this code, handler REMOVE ON NULL causes field a to be removed if the <SQL expression> evaluates to NULL, instead of being set to JSON null. (NULL ON NULL is the default behavior for operator SET.)

  SELECT json_transform('{"a":1}',
                        SET '$.a' = <SQL expression>
                        REMOVE ON NULL);

• IGNORE IF array-content handlers — Specify what happens if the RHS values are absent or present as elements of the array targeted by the LHS.

  Note:

  These handlers are specific to operators ADD_SET and REMOVE_SET, which access elements of an array; they say what to do if targeted elements are unexpectedly absent or present. They're concerned only with the absence or presence of array elements. They don't check whether an array itself is absent or present — for that you use an ON MISSING handler.

  • IGNORE IF ABSENT — Do not raise an error if the RHS values are absent from the array targeted by the LHS. (By default an error is raised in this case.)

  • IGNORE IF PRESENT — Do not raise an error if the RHS values are already present in the array targeted by the LHS. (By default an error is raised in this case.)

  For example, in this code, handler IGNORE IF ABSENT causes the error to be ignored of 6 not being an element of array a. The REMOVE_SET operation has no effect: it is a no-op.

  SELECT json_transform('{"a":[ 1,2,3 ]}',
                        REMOVE_SET '$.a' = PATH '6'
                        IGNORE IF ABSENT);

  In the following code, handler IGNORE ON MISSING overrides the default behavior of raising an error because the array a targeted by the LHS does not exist. Handler IGNORE IF ABSENT is irrelevant here (it has no effect), because there is no array a whose elements can be checked by it.

  SELECT json_transform('{"b":[ 1,2,3 ]}',
                        REMOVE_SET '$.a' = PATH '6'
                        IGNORE IF MISSING
                        IGNORE IF ABSENT);

The handlers allowed for the various operations are as follows.

• ADD_SET:

  • ERROR ON MISSING (default), IGNORE ON MISSING, CREATE ON MISSING. Create means insert a singleton array at the targeted location. The single array element is the value of the SQL result expression.

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

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

  • IGNORE IF PRESENT. By default an error is raised if the targeted array element is already present.

• 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.

• CASE: no handlers.

• 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.

• INSERT:

  • ERROR ON EXISTING (default), IGNORE ON EXISTING, REPLACE ON EXISTING.

  • CREATE ON MISSING (default).

  • 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.

• INTERSECT:

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

  • ERROR ON MISMATCH (default).

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

• KEEP: IGNORE ON MISSING (default), ERROR ON MISSING.

• 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.

• MINUS:

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

  • ERROR ON MISMATCH (default).

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

• NESTED PATH: no handlers.

• PREPEND: Same as APPEND.

• REMOVE:

  • REMOVE ON EXISTING (default).

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

• REMOVE_SET:

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

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

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

  • IGNORE IF ABSENT. By default an error is raised if the targeted array element is absent.

• RENAME:

  • REPLACE ON EXISTING (default).

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

• 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.

• SET:

  • REPLACE ON EXISTING (default), IGNORE ON EXISTING, ERROR ON EXISTING.

  • CREATE ON MISSING (default), IGNORE ON MISSING, ERROR 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.

• SORT:

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

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

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

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

• UNION:

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

  • ERROR ON MISMATCH (default).

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

• WHEN: no handlers