13.22 JSON_TRANSFORM操作のハンドラ

ファンクションjson_transformの操作には、ハンドラ(一般的でない状況や想定外の状況でデフォルト動作をオーバーライドする)が関連付けられています。

次に、各操作で使用できるハンドラを、よく使用される順に示します。一般に、指定されたタイプのハンドラのデフォルト動作は、関係する演算子によって異なります。

  • ON ERROR — RHSパスを解決しようとしてエラーが発生した場合の動作を指定します。

    • NULL ON ERROR — JSON nullを返します。

    • ERROR ON ERROR — エラーを発生させます。

    • IGNORE ON ERROR — データを変更しないままにします(変更なし)。

    たとえば、このコードでは、ハンドラIGNORE ON ERRORにより、フィールドaの値"cat"を数値に変換できないというエラーが無視されるようになります。このINSERT操作は効果がありません: 操作なしです。(ERROR ON ERRORは、演算子INSERTのデフォルト動作です。) 

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

  • ON EMPTY - RHSのパス式で対象となっている値が存在しなかった場合の動作を指定します。

    • NULL ON EMPTY — JSON nullを返します。

    • ERROR ON EMPTY — エラーを発生させます。

    • IGNORE ON EMPTY — データを変更しないままにします(変更なし)。

    たとえば、このコードでは、RHSでデータが対象となってないため(入力データにフィールドxはない)、ハンドラERROR ON EMPTYによってエラーが発生します。(IGNORE ON EMPTYは、演算子APPENDのデフォルト動作です。) 

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

  • ON EXISTING — LHSのパス式がデータと一致した(つまり、それで少なくとも1つの値が対象となっている)場合の動作を指定します。(SQL/JSON変数であるLHSでは、このハンドラは無関係であるため無視されます)。

    • ERROR ON EXISTING—エラーを発生させます。

    • IGNORE ON EXISTING—データを変更しません(変更なし)。

    • REPLACE ON EXISTING — 対象となっている場所にあるデータを、RHSで指定されている値に置き換えます。

    • REMOVE ON EXISTING—対象となるデータを削除します。

    たとえば、このコードでは、ハンドラIGNORE ON EXISTINGによってフィールドcreatedの値は変更されません。これは、そのフィールドがすでに存在するためです。このSET操作は効果がありません: 操作なしです。(REPLACE ON EXISTINGは、演算子SETのデフォルト動作です。) 

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

  • ON MISSING — LHSのパス式がデータと一致しない(つまり、それで値が1つも対象となっていない)場合の動作を指定します。

    デフォルトのON MISSINGハンドラは、関係する演算子によって異なります。

    • ERROR ON MISSING—エラーを発生させます。

    • IGNORE ON MISSING—データを変更しません(変更なし)。

    • CREATE ON MISSING—対象となる位置にデータを追加します。

      たとえば、このコードでは、対象となっているフィールドaが存在しないため、ハンドラERROR ON MISSINGによってエラーが発生します。(REPLACE ON MISSINGは、演算子RENAMEのデフォルト動作です。) 

      SELECT json_transform('{"x":null}',
                      RENAME '$.a' = 'b'
                      ERROR ON MISSING);
      ORA-40768: field with name 'a' does not exist

  • ON MISMATCH — LHSが対象とするデータのタイプが予期しない場合の動作を指定します。特に、(LHS)対象データがオブジェクトである必要があるMERGE操作や、対象データが配列である必要がある操作に適用されます。

    • NULL ON MISMATCH — JSON nullを返します。

    • ERROR ON MISMATCH — エラーを発生させます。

    • IGNORE ON MISMATCH — データを変更しないままにします(変更なし)。

    • CREATE ON MISMATCH — 対象となっている値を(シングルトン)配列にラップしてから、操作を適用します。

    • REPLACE ON MISMATCH — 対象となっている値を空の配列([])に置き換えてから、操作を適用します。

      たとえば、このコードでは、ハンドラIGNORE ON MISMATCHによって、非オブジェクト・フィールドaとオブジェクト{"b":2}をマージしようとしたときのエラーが無視されるようになります。(ERROR ON MISSINGは、演算子MERGEのデフォルト動作です。) 

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

      結果 — 入力データは変更されません:

      {"a":"notAnObject"}

      次のコードでは、ハンドラCREATE ON MISMATCHによってデフォルト動作(配列でない対象値("dog")に追加しようとしたことによりエラーを発生させる)がオーバーライドされます。この一致しなかった値は、シングルトン配列(["dog"])として扱われ、これに、RHSの値("cat")が要素として追加されます。 

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

      結果 — 変更された入力データ:

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

      次のコードでは、ハンドラREPLACE ON MISMATCHによってもう一度、デフォルト動作(配列でない対象値("dog")に追加しようとしたことによりエラーを発生させる)がオーバーライドされます。ただし、この場合は、一致しなかったLHS値("dog")はRHS値("cat")に置き換えられてから、シングルトン配列([ "cat" ])としてラップされます。 

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

      結果 — 変更された入力データ:

      {"a":[ "cat" ]}

  • ON NULL - RHSのSQL結果式の値がNULLの場合に発生する動作を指定します。(このハンドラは、RHSがSQL式の場合にのみ適用されます。RHSでキーワードPATHが使用されている場合は、ON NULLは無視されます)。

    • NULL ON NULL—対象となる場所にJSONのnull値を使用します。

    • ERROR ON NULL—エラーを発生させます。

    • IGNORE ON NULL—データを変更しません(変更なし)。

    • REMOVE ON NULL—対象となるデータを削除します。

    たとえば、このコードでは、ハンドラREMOVE ON NULLにより、<SQL expression>がNULLと評価された場合にJSON nullに設定されるのではなくフィールドaが削除されるようになります。(NULL ON NULLは、演算子SETのデフォルト動作です。) 

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

  • IGNORE IF配列内容ハンドラ — RHSの値がLHSでの対象となる配列の要素として存在しないか存在している場合の動作を指定します。

    ノート:

    これらのハンドラは、演算子ADD_SETとREMOVE_SET (これらは配列の要素にアクセスする)に固有のものであり、対象となっている要素が想定外に存在しないか存在している場合の実行内容を指示します。これらは、配列要素の有無にのみに関係しています。配列自体が存在しているかどうかはチェックしません。それを行うには、ON MISSINGハンドラを使用してください。

    • IGNORE IF ABSENT — LHSで対象となっている配列にRHSの値が存在しない場合にエラーを発生させません。(デフォルトでは、この場合、エラーが発生します。)

    • IGNORE IF PRESENT — LHSで対象となっている配列にRHSの値がすでに存在する場合にエラーを発生させません。(デフォルトでは、この場合、エラーが発生します。)

    たとえば、このコードでは、ハンドラIGNORE IF ABSENTによって、6が配列aの要素ではないというエラーが無視されることになります。このREMOVE_SET操作は効果がありません: 操作なしです。 

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

    次のコードでは、ハンドラIGNORE ON MISSINGによってデフォルト動作(LHSで対象となっている配列aが存在しないことによりエラーを発生させる)がオーバーライドされます。要素をチェックできる配列aがないため、ハンドラIGNORE IF ABSENTはここでは無関係です(それに効果はありません)。 

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

各種の操作に許可されるハンドラは、次のとおりです。

  • ADD_SET:

    • ERROR ON MISSING (デフォルト)、IGNORE ON MISSING、CREATE ON MISSING。作成は、対象となる場所に単一の配列を挿入することを意味します。単一の配列要素は、SQLの結果式の値です。

    • NULL ON NULL (デフォルト)、IGNORE ON NULL、ERROR ON NULL

    • ERROR ON EMPTY (デフォルト)、IGNORE ON EMPTY、NULL ON EMPTY。

    • IGNORE IF PRESENT。デフォルトでは、対象となっている配列要素がすでに存在する場合はエラーが発生します。

  • APPEND:

    • ERROR ON MISSING (デフォルト)、IGNORE ON MISSING、CREATE ON MISSING、NULL ON MISSING。作成は、対象となる場所に単一の配列を挿入することを意味します。単一の配列要素は、SQLの結果式の値です。

    • ERROR ON MISMATCH (デフォルト)、IGNORE ON MISMATCH、REPLACE ON MISMATCH、CREATE ON MISMATCH。

    • NULL ON NULL (デフォルト)、IGNORE ON NULL、ERROR ON NULL

    • IGNORE ON EMPTY (デフォルト)、ERROR ON EMPTY.

  • CASE: ハンドラなし

  • COPY:

    • CREATE ON MISSING (デフォルト)、IGNORE ON MISSING、ERROR ON MISSING、NULL ON MISSING

    • NULL ON NULL (デフォルト)、IGNORE ON NULL、 ERROR ON NULL

    • IGNORE ON EMPTY (デフォルト)、ERROR ON EMPTY.

  • INSERT:

    • ERROR ON EXISTING (デフォルト)、IGNORE ON EXISTING、REPLACE ON EXISTING

    • CREATE ON MISSING (デフォルト)

    • NULL ON NULL (デフォルト)、IGNORE ON NULL、ERROR ON NULL、REMOVE ON NULL

    • NULL ON EMPTY (デフォルト)、IGNORE ON EMPTY、ERROR ON EMPTY。

    • ERROR ON ERROR (デフォルト)、IGNORE ON ERROR。

  • INTERSECT:

    • ERROR ON MISSING (デフォルト)、IGNORE ON MISSING、CREATE ON MISSING、NULL ON MISSING。

    • ERROR ON MISMATCH (デフォルト)。

    • NULL ON NULL (デフォルト)、IGNORE ON NULL、ERROR ON NULL

  • KEEP: IGNORE ON MISSING (デフォルト)、ERROR ON MISSING

  • MERGE:

    • ERROR ON MISSING (デフォルト)、IGNORE ON MISSING、CREATE ON MISSING、NULL ON MISSING。

    • ERROR ON MISMATCH (デフォルト)、IGNORE ON MISMATCH。

    • NULL ON NULL (デフォルト)、IGNORE ON NULL、ERROR ON NULL

    • ERROR ON EMPTY (デフォルト)、IGNORE ON EMPTY。

  • MINUS:

    • ERROR ON MISSING (デフォルト)、IGNORE ON MISSING、CREATE ON MISSING、NULL ON MISSING。

    • ERROR ON MISMATCH (デフォルト)。

    • NULL ON NULL (デフォルト)、IGNORE ON NULL、ERROR ON NULL

  • NESTED PATH: ハンドラなし

  • PREPEND: APPENDと同じ。

  • REMOVE:

    • REMOVE ON EXISTING (デフォルト)

    • IGNORE ON MISSING (デフォルト)、ERROR ON MISSING

  • REMOVE_SET:

    • ERROR ON MISSING (デフォルト)、IGNORE ON MISSING。

    • NULL ON NULL (デフォルト)、IGNORE ON NULL、ERROR ON NULL

    • ERROR ON EMPTY (デフォルト)、IGNORE ON EMPTY、NULL ON EMPTY。

    • IGNORE IF ABSENT。デフォルトでは、対象となっている配列要素が存在しない場合はエラーが発生します。

  • RENAME:

    • REPLACE ON EXISTING (デフォルト)

    • IGNORE ON MISSING (デフォルト)、ERROR ON MISSING

  • REPLACE:

    • REPLACE ON EXISTING (デフォルト)

    • IGNORE ON MISSING (デフォルト)、ERROR ON MISSING、CREATE ON MISSING

    • NULL ON NULL (デフォルト)、IGNORE ON NULL、ERROR ON NULL、REMOVE ON NULL

    • NULL ON EMPTY (デフォルト)、IGNORE ON EMPTY、ERROR ON EMPTY。

    • ERROR ON ERROR (デフォルト)、IGNORE ON ERROR。

  • SET:

    • REPLACE ON EXISTING (デフォルト)、IGNORE ON EXISTING、ERROR ON EXISTING

    • CREATE ON MISSING (デフォルト)、IGNORE ON MISSING、ERROR ON MISSING

    • NULL ON NULL (デフォルト)、IGNORE ON NULL、ERROR ON NULL、REMOVE ON NULL

    • NULL ON EMPTY (デフォルト)、IGNORE ON EMPTY、ERROR ON EMPTY。

    • ERROR ON ERROR (デフォルト)、IGNORE ON ERROR。

  • SORT:

    • IGNORE ON MISSING (デフォルト)、ERROR ON MISSING、NULL ON MISSING。

    • ERROR ON MISMATCH (デフォルト)、IGNORE ON MISMATCH、NULL ON MISMATCH。

    • ERROR ON EMPTY (デフォルト)、IGNORE ON EMPTY。

    • ERROR ON ERROR (デフォルト)、IGNORE ON ERROR。

  • UNION:

    • ERROR ON MISSING (デフォルト)、IGNORE ON MISSING、CREATE ON MISSING、NULL ON MISSING。

    • ERROR ON MISMATCH (デフォルト)。

    • NULL ON NULL (デフォルト)、IGNORE ON NULL、ERROR ON NULL

  • WHEN: ハンドラなし

親トピック: Oracle SQLファンクションJSON_TRANSFORM