1. JSON開発者ガイド
  2. JSONデータの挿入、更新およびロード
  3. Oracle SQLファンクションJSON_TRANSFORM

12 Oracle SQLファンクションJSON_TRANSFORM

Oracle SQLファンクションjson_transformはJSON文書を変更します。実行する操作と、変更する場所を示すSQL/JSONパス式を指定します。操作は、指定した順序で入力データに適用されます。それぞれの操作は、その前のすべての操作が適用された結果に作用します。

ファンクションjson_transformはアトミック型です。操作を試行してエラーが発生した場合、その操作は一切有効になりません。json_transformは、完全に成功してデータが要求されたとおりに変更されるか、データは変更されません。json_transformは、引数での指定どおりに変更された元のデータを返します。

SQL UPDATE文でjson_transformを使用すると、JSON列のドキュメントを更新できます。例12-1に、これを示します。

SELECTのリストでそれを使用すると、選択した文書を変更できます。変更したドキュメントは、後で返したり、処理できます。例12-2に、これを示します。

ファンクションjson_transformは、JSONデータをサポートするSQLデータ型(JSON、VARCHAR2、CLOBまたはBLOB)を入力として受け取り、出力として返すことができます。(JSONデータ型は、データベース初期化パラメータcompatibleが20以上の場合にのみ使用できます)。

デフォルトの戻り(出力)データ型は、入力データ型と同じです。脚注1

制限された適用性を持つOracle SQLファンクションjson_mergepatch (主に構造にオブジェクトを使用し、明示的なnull値を使用しないJSON文書を更新する場合に適しています)とは異なり、json_transformは一般的な変更関数です。

json_transformの1回の呼出しで複数の操作を実行するように指定すると、指定した順序で操作が順番に実行されます。そのため、それぞれの操作は、その前のすべての操作が適用された結果に作用します。

指定する操作のシーケンスに続けて、オプションのRETURNING句およびPASSING句を含めることができます。

  • RETURNING句では、戻りデータ型を指定します。これは、SQL/JSONファンクションjson_queryの場合と同じです。(ただし、json_queryのデフォルトの戻り型は異なります。JSON型の入力ではjson_queryのデフォルトの戻り型もJSONですが、他の入力の型ではVARCHAR2(4000)です。)

  • PASSING句では、SQL/JSON変数へのバインド変数のSQLバインディングを指定します。これは、SQL/JSON条件json_existsおよびSQL/JSON問合せファンクションの場合と同じです。

操作の指定の最後の部分は、オプションの一連のハンドラです。各操作に対して異なるハンドラが許可され、ハンドラのデフォルトが異なります。(その操作を許可しないハンドラを操作に指定した場合は、エラーが発生します。)

ほとんどのjson_transform操作では、データが直接変更されます。操作NESTED PATHおよびCASEは、別の操作のパフォーマンスを制御することで、データを間接的に変更できます。SQL/JSON変数の設定に使用されるSET操作についても同様です。変数値は、データを直接変更する操作の動作に影響を与えることがあります。

次に、json_transform操作の簡単な説明を示します。詳細な説明は、「JSON_TRANSFORM操作」および「NESTED PATH操作: 操作のシーケンスのスコープ設定」を参照してください。

  • APPEND - 配列の末尾に値を追加します。

  • CASE - 条件付きでjson_transform操作を実行します。

  • COPY - 配列の要素を置き換えます。

  • INSERT — 指定の場所(オブジェクト・フィールド値または配列位置)にデータを挿入します。

  • INTERSECT - 指定したセット内の配列要素以外の配列要素を削除します(積集合)。重複を削除します。この操作は、RHSパス式で一致する複数の値のシーケンスを受け入れることができます。

  • KEEP - 指定したパス式で対象とされないデータを削除します。

  • MERGE - 指定したフィールドをオブジェクトにマージします(オブジェクトを作成する可能性があります)。

  • MINUS - 配列要素のセットを削除します(差集合)。重複を削除します。この操作は、RHSパス式で一致する複数の値のシーケンスを受け入れることができます。

  • NESTED PATH - 操作のセットを適用するスコープ(データの特定の部分)を定義します。

  • PREPEND - 配列の先頭に値を追加します。

  • REMOVE - パス式で指定されたデータを削除します。

  • RENAME - フィールド名を変更します。

  • REPLACE - 指定の場所のデータを置換します。

  • SET - SQL/JSON変数を指定された値に設定するか、指定の場所でデータを挿入または置換します。

  • SORT - 配列の要素をソートします(要素の順序を変更します)。

  • UNION - 指定されたセットから欠落している配列要素を追加します(和集合)。重複を削除します。

JSON_TRANSFORMパス式: 対象となるデータとRHS

各種の操作のキーワードの直後には、その操作で対象となるデータのパス式を指定します。

KEEP操作は、キーワードの後に1つ以上のパス式が続く例外です。このパス式は、維持するデータを指定します。これらのパス式の少なくとも1つの対象となっていないすべてのデータは削除されます。

すべての変更操作(KEEP、REMOVEおよびSORTを除く)では、パス式の後に等号(=)と結果式が順に続きます。これが評価され、結果値を使用して対象となるデータが変更されます。脚注1

  • RENAME,操作では、結果式はSQL文字列に評価される必要があります。そうでない場合は、エラーが発生します。

  • すべての変更操作(RENAMEを除く)について、結果式は、JSONデータ型のSQL値、またはJSON値として表すことができるSQL値に評価される必要があります。そうでない場合は、不適切なSQLデータ型が原因でエラーが発生します。(これは、SQL/JSON生成関数json_objectに指定する名前と値のペアの値の部分の要件と同じです。)

  • 結果の式がJSON型でないSQL値に評価された場合は、式の直後にキーワードFORMAT JSONを続けることで、結果の式をJSONデータに変換できます。これは、SQL文字列'true'または'false'を、対応するJSON言語値trueまたはfalseに変換する場合に特に便利です。例12-7に、これを示します。

更新するJSONデータを対象とする等号の左辺のパス式は、左辺またはLHSと呼ばれることがあります。LHSは、常にSQL/JSONパス式になります。

結果式は、操作の右辺またはRHSと呼ばれることがあります。

RHSは、SQL式またはキーワードPATHに続く一重引用符(')でラップされたSQL/JSONパス式のどちらか脚注2になります。

json_transformのRHSのパス式は、その他の場所で許可されているパス式よりも一般的です。これは、基本的な算術演算の+ (加算)、- (減算)、* (乗算)および/ (除算)を使用して複数のパス式を結合できる式です。また、こうした演算はネストやグループ化が可能です。算術演算の使用に関する唯一の制限は、それらが述語内では使用できないことです。たとえば、次のように述語で+操作を使用すると、コンパイル時にエラーが発生します。 

$.a?(@.x == (@.y + 4)).b - 2

RHSのパス式には、SQL/JSON変数を含めることもできます。この変数は、前のjson_transform操作で割り当てられるか、RHSのパス式に続くPASSING句を使用してSQLから渡されます。

たとえば、このSET操作では、次に示す値の合計としてフィールドcompensationの値を更新します。ここでは、変数値がPASSING句を使用して渡されます。

  • フィールドsalaryに0.02を乗算したものが、SQL/JSON変数$factorとしてパス式に渡されます

  • フィールドcommission

  • 1000 (SQL/JSON変数$bonusとして渡されます)

SET '$.compensation' = PATH '($.salary * $factor) + $.commission + $bonus'
                       PASSING 1000 AS "bonus", 0.02 AS "factor"

この例は、同等のものです。これは、SET操作を使用して、次の2つの変数を割り当てます。 


SET '$bonus' = 1000,
SET '$factor' = 0.02,
SET '$.compensation' = PATH '($.salary * $factor) + $.commission + $bonus'

RHSでは、1つ以上の相対パス式を使用できます。この式のアットマーク文字(@)は、最も内側のNESTED PATHコンテキストで定義されたものとして現行のノードを参照します。

RHSの$は、最上位コンテキストの現行のノードを参照します。内側のNESTED PATH式が存在しない場合、@は、RHSパス式の$と同じになります。

RHSパス式では、単一の値または値のシーケンスを指定できます。それ以外のものはエラーになります。ほとんどの操作では、単一のJSON値を対象にする必要があります。(ただし、そのような単一の値は、$.a[*].sum()などの複数の値を集計した結果でもかまいません)。

ただし、配列を対象とし、RHSパス式を受け入れるLHSを期待する(必須とする)操作では、そのパス式で複数の値のシーケンスを生成できます。(単一の一致する値は、その単一の値のシーケンスとして扱われます。)これらの操作は、APPEND、PREPEND、COPY、MINUS、UNIONおよびINTERSECTです。

たとえば、これらの操作のいずれかでは、RHSパス式$.b[0 to 2]は、フィールドbの値である配列の1番目から3番目までの要素をシーケンスとして生成します。

RHSパス式が配列を対象とする場合は、LHS配列と組み合せる(単一の)値として配列全体が使用されます。脚注3

ただし、RHSが配列の要素の一部またはすべてを明示的に対象とする場合、それらの要素は複数の値のシーケンスとして使用されます。これらの値は、対象となる配列全体と単一の操作で結合されます。APPENDやPREPENDなど、RHSシーケンス値をLHS配列に追加する操作では、結果として得られる配列にシーケンス値の順序が保持されます。

たとえば、配列aの値が[30,20]で、配列bの値が[2,4,6,8]であるとします。

  • この操作は、配列bを単一の要素としてaに付加します。 

    PREPEND '$.a' = PATH '$.b'

    配列aが値[[2,4,6,8],30,20]に設定されます。

  • この操作は、配列bの2番目と4番目の要素を配列aに付加します。 

    PREPEND '$.a' = PATH '$.b[2,4]'

    RHSパス式に一致する複数の値は、配列a togetherの前に付加され、個別には付加されません(1つの動作ですべての先頭に付加されます)。したがって、シーケンスの順序は結果の配列に反映されます。配列aは、[4,8,30,20]に設定されます。[8,4,30,20]ではありません。

  • この操作は、配列bのすべての要素を配列aにまとめて付加します。 

    PREPEND '$.a' = PATH '$.b[*]'

    配列aが[[2,4,6,8],30,20]に設定されます。

ノート:

PREPENDおよびINTERSECTは、RHSパス式に一致する複数の値が個別に処理されるのではなく、ブロックとして完全に処理されることが本当に重要な唯一のLHS配列対象操作です。

たとえば、RHSパス・マッチング値3および4が、1つの単位としてLHSターゲット配列[1,2]にAPPENDされた場合、結果は、要素3および4が配列に個別に付加される場合と同じになります。結果は、どちらの場合も[1,2,3,4]です。3を追加すると、4は、3と4をまとめて追加することと同じになり、順番に並べられます。

INTERSECTの場合、複数のRHS値(2つ以上の異なる値)が個別に処理された場合、結果は空の配列[]になります。実際には、シーケンス内の最初の値を処理した後、結果の交差はその値を持つ単一の配列になります。シーケンス内の別の値を処理すると、空の交差が発生します。

JSON_TRANSFORM操作

NESTED PATH操作については、「NESTED PATH操作: 操作のシーケンスのスコープ設定」を参照してください。この項では、その他のjson_transform操作について説明します。

  • REMOVE - 指定されたパス式で対象となる入力データを削除します。すべてのデータを削除しようとすると、エラーが発生します。つまり、REMOVE '$'は使用できません。デフォルトでは、対象となるデータが存在しない場合、エラーは発生しません(IGNORE ON MISSING)。

  • KEEP - 少なくとも1つの指定されたパス式で対象とされていない入力データのすべての部分を削除します。最上位のオブジェクトまたは配列は削除されません。それらは空にされ、空のオブジェクト({})または配列([])になります。

    KEEP操作の使用は、NESTED PATH操作内で使用することで有効範囲を限定できます。ネストされたパスで定義されたスコープの外側のデータは、KEEPプルーニングによる影響を受けません。例12-15に、これを示します。

  • RENAME - 指定されたパス式で対象となるフィールドの名前を、等号(=)の後ろに続くSQL式の値に変更します。デフォルトでは、対象となるフィールドが存在しない場合、エラーは発生しません(IGNORE ON MISSING)。

  • SET - LHSが指定する値を等号(=)の後ろで指定する値に設定します。LHSは、SQL/JSON変数またはデータを対象とするパス式のいずれかになります。

    • LHSでSQL/JSONの変数が指定されると、その変数はRHSで指定されたものに動的に割り当てられます。(まだ存在していない場合は変数が作成されます)。変数は、後続のSET操作によって(同じjson_tranform呼出し内で)異なる値に設定されるまで、その値を保持し続けます。

      RHSがSQL式の場合は、その値がLHSの変数に割り当てられます。RHSがパス式の場合は、その式が対象とするデータが変数に割り当てられます。

      変数の設定は制御操作であり、後続の操作によるデータの変更方法に影響することがありますが、それ自体がデータを直接変更することはありません。

    • LHSにパス式が指定されているときのデフォルトの動作は、SQLのUPSERTと同様であり、対象となる既存のデータが新しい値に置き換えられます。また、パス式に一致するものがない場合は、対象となる場所に新しい値が挿入されます。(配列の末尾を超える配列要素の挿入については、演算子INSERTを参照してください。)

  • REPLACE - 指定されたパス式で対象となるデータを、等号(=)の後ろに指定されたSQL式の値に置き換えます。デフォルトでは、対象となるデータが存在しない場合、エラーは発生しません(IGNORE ON MISSING)。

    (REPLACEには、IGNORE ON MISSING句を指定したSETと同じ効果があります。)

  • INSERT - 指定されたパス式で対象となる場所に、等号(=)の後ろに指定されたSQL式の値を挿入します。この場所は、オブジェクトのフィールドまたは配列の位置のいずれかであることが必要です(それ以外の場合は、エラーが発生します)。デフォルトでは、対象となるオブジェクトのフィールドがすでに存在する場合、エラーが発生します。

    (オブジェクトのフィールドのINSERTは、CREATE ON MISSING句(SETのデフォルト)を指定したSETと同じ効果があります。ただし、ON EXISTINGのデフォルトの動作は、REPLACEではなくERRORです。)

    配列の現在の末尾より後の配列の位置を指定できます。その場合、配列は指定された位置への値の挿入に対応するように長さが調整され、間にある位置にはJSONのnull値が設定されます。

    たとえば、入力のJSONデータが{"a":["b"]}の場合、INSERT '$.a[3]'=42は変更されたデータとして{"a":["b", null, null 42]}を返します。配列位置1および2の要素は、nullです。

  • APPEND - RHSで指定された値をLHSのパス式で対象とする配列の末尾に追加します。この操作は、RHSパス式で一致する複数の値のシーケンスを受け入れることができます。

    LHSのパス式が、配列でない値の既存のフィールドを対象としている場合はエラーが発生します。

    APPENDは、last+1の配列位置へのINSERTの効果があります。

    RHSが配列を対象としている場合は、RHSの配列の要素を順番に追加することでLHSの配列が更新されます。例12-10を参照してください。

    ヒント:

    ハンドラCREATE ON MISSINGを使用すると、LHSが対象とする欠落している配列値フィールドを作成して、RHSで指定された値から入力できます。次に例を示します。 

    SELECT json_transform('{"a":[1,2,3]}',
                      APPEND '$.b' = PATH '$.a[0,2]'
                      CREATE ON MISSING)
  FROM DUAL;

    その結果のデータは次のとおりです。

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

  • PREPEND - RHSで指定された値をLHSパス式で対象とする配列の末尾に追加します。この操作は、RHSパス式で一致する複数の値のシーケンスを受け入れることができます。

    LHSのパス式が、配列でない値の既存のフィールドを対象としている場合はエラーが発生します。

    単一の値を先頭に付加する場合、PREPENDには、配列位置0に対するINSERTの効果があります。

    RHSが配列を対象としている場合は、RHSの配列の要素を先頭に順番に追加することでLHSの配列が更新されます。

    関連項目:

    • ノート: 個別にではなく、一緒に処理されるPREPEND操作のRHSパス式に一致する複数の値について

    • 例12-11.

  • COPY - LHSのパス式で対象とする配列の要素を、RHSで指定された値に置き換えます。LHSのパス式が配列を対象としていない場合は、エラーが発生します。この操作は、RHSパス式で一致する複数の値のシーケンスを受け入れることができます。

  • MINUS - LHSのパス式で対象とされる配列要素のうちRHSで指定された値と等しいすべての要素を削除します。重複している要素を削除します。ノート: これは、積集合操作です。操作後のすべての配列要素の順序は定義されていません。

  • INTERSECT - LHSのパス式で対象とする配列要素のうちRHSで指定された値と等しくないすべての要素を削除します。重複している要素を削除します。ノート: これは、積集合操作です。操作後のすべての配列要素の順序は定義されていません。

    関連項目: ノート: 個別にではなく、一緒に処理されるINTERSECT操作のRHSパス式に一致する複数の値について。

  • UNION - RHSで指定された値をLHSのパス式で対象とする配列に追加します。重複している要素を削除します。この操作は、RHSパス式で一致する複数の値のシーケンスを受け入れることができます。

    ノート: これは、積集合操作です。操作後のすべての配列要素の順序は定義されていません。

  • SORT - 指定されたパスで対象となる配列の要素をソートします。結果には、配列のすべての要素が含まれます(何も削除されません)。可能性のある唯一の変更は、順序が変更されることです。

    ソートには次の2つの方法があります。どちらの方法でも、キーワードREMOVE NULLSを使用して、ソートする値からJSONのnull値を削除できます。

    • 基本的な要素のソート: 正規のJSON型のソート順に従って、配列要素を値でソートします。

      対象とする配列へのパスの後ろに昇順ソートまたは降順ソートに対応するオプションのキーワードASC (デフォルト)またはDESCを続けるか、または要素順序の逆転を意味するキーワードREVERSEを続けます。

      パスおよびオプションのキーワードASCまたはDESCの後ろに、重複する配列要素の削除を意味するオプションのキーワードUNIQUEを続けます。

    • パス指示型ソート: 配列に相対する1つ以上のパスを指定するORDER BY句で対象とされた値によって配列要素をソートします。

      各パスの後ろに、昇順ソートまたは降順ソートに対応するオプションのキーワードASC (デフォルト)またはDESCを続けます。

      まず、最初のORDER BYパスで対象とされた値を比較し、次に2番目のORDER BYパスで対象とされた値を比較し、その後も同様の比較を繰り返して各要素のペアをソートします。

      パス内の各ステップは単純であることが必要です。つまり、単一のJSON値を対象とする必要があり、配列ステップは単一の配列要素を対象にする必要があります。ステップは子孫ステップにすることも、述語、ワイルドカードまたは項目メソッドを含めることもできません。それ以外の場合は、コンパイル時にエラーが発生します。

      最も簡単な例では、ORDER BYの後に単一パス@を続けます。これは、配列に対して相対的であるため、それぞれの配列要素を相互に比較して順序付けします。つまり、ORDER BY '@'は、単に基本的な要素のソートを指定するための別の方法ということです。

      それぞれのORDER BYパスは、配列要素の各ペアに対して順次チェックされます。

      • ペアの1つの要素がパスで一致し、もう1つの要素が一致しない場合、一致しない要素は一致する要素の前に(キーワードDESCでは後に)ソートされます。

      • それ以外の場合(どちらの要素もパスと一致しないまたは両方の要素が一致する場合):

        • これがチェックされる最後のパスである場合、2つの要素はそれらの値に応じてJSON型の正規のソート順序を使用してソートされます(深い比較が実施されます)。低い値の要素は、もう一方の前に(キーワードDESCでは後に)ソートされます。

        • それ以外の場合、このパスのみでは2つの要素の順序は決定されず、その次のORDER BYパスを使用して同じペアをチェックします。

      特定の要素に対してORDER BYパスが一致しない理由として、不正な型の値および欠落している値があります。次に、配列要素Eと一致しない例を示します。

      • パスは文字列を対象としていますが、E内の対象とされる値は数値です(不正な型)。

      • パスはオブジェクトのフィールドを対象としていますが、E内には、該当するオブジェクトまたは該当するフィールドが存在していません(値の欠落)。

      • パスは範囲外の配列要素を対象としています(値の欠落)。

    単一パス句のORDER BY '@.name'は、次の要素を保持している配列を次のようにソートします。 

    [ "cat", "dog", {"animal":cat"}, {"name":"cow"}, {"name":"horse"} ]

    • 要素"cat"、"dog"および{"animal":cat"}はパスと一致しません。これらは、その他の要素の前にソートされ、正規の値でソートされます。

    • 要素{"name":"cow"}および{"name":"horse"}はパスと一致します。これらは、その他の要素の後にソートされ、フィールドnameの値でソートされます。

    2パス句のORDER BY '@.name', '@.age' DESCは、次の要素を保持している配列を次のようにソートします。 

    [ "cat", "dog", {"animal":cat"},
  {"name":"cow"}, {"name":"cow", "age":2},
  {"name":"horse", "age":6, "color":"black"},
  {"name":"horse", "age":3} ]

    • 要素"cat"、"dog"および{"animal":cat"}は、どちらのパスとも一致しません。これらは、その他の要素の前にソートされ、正規の値でソートされます。

    • 要素{"name":"cow"}は、最初のパスにのみ一致します。これは、両方のパスと一致する要素の前にソートされます。

    • 要素{"name":"cow", "age":2}、{"name":"horse", "age":6, "color":"black"}および{"name":"horse", "age":3}は、両方のパスと一致します。これは、その他の要素の後にソートされ、最初にフィールドnameの値(昇順)、次にフィールドageの値(降順)でソートされます。

  • MERGE - RHSパス式で一致するフィールド(名前と値)をLHSパス式で対象とするオブジェクトに追加します。対象となるLHSのオブジェクトにすでに含まれている、RHSで指定されたフィールドを無視します。RHSで同じフィールドが複数回指定されている場合は、一致するフィールドのシーケンスの最後のフィールドのみを使用します。

    ヒント:

    ハンドラCREATE ON MISSINGを使用すると、LHSが対象とする欠落しているオブジェクトを作成して、RHSで指定されたフィールドから入力できます。

  • CASE - json_transform操作のシーケンスを条件付きで実行します。

    これは制御操作であり、条件付きで別の操作を適用し、それによってデータを変更できます。

    構文は、キーワードCASEの後に0個以上のWHEN句を続け、その後にオプションのELSE句を続け、その後にENDを続けます。

    • WHEN句は、キーワードWHENの後にパス式を続け、その後にTHEN句を続けます。

      パス式にはフィルタ条件を含めて、それによってデータが存在するかどうかをチェックします。

    • THEN句またはELSE句は、それぞれキーワードTHENまたはELSEの後に、0個以上のjson_transform操作が含まれたカッコ(())を続けます。

      THEN句の操作は、そのWHEN句の条件が満たされた場合に実行されます。オプションのELSE句の操作は、WHEN句なしの条件が満たされた場合に実行されます。

    したがって、json_transform CASE操作の構文は、基本的にOracle SQLの検索CASE式と同じであり、その違いは(1)テストされる述語と、(2)各THEN/ELSE分岐の結果の効果にあります。

    • SQLの場合、テストされる述語はSQL比較です。json_transformの場合、述語は、なんらかのデータの存在をチェックするパス式です。(基本的に、このチェックにはjson_existsが使用されます)。

    • SQLの場合、各THEN/ELSE分岐は評価対象のSQL式を保持し、その値がCASE式の結果として返されます。json_transformの場合、各THEN/ELSE分岐は、順次実行されるjson_transform操作の(カッコで囲まれた)シーケンスを保持します。

    WHEN句の条件付きパス式は、成功するまで順次テストされます(成功後のパス式はテストされません)。その後で、成功したWHENテストのTHEN操作が順次実行されます。

    いずれのWHENテストも成功しない場合に、ELSE句が存在していると、その操作が順次実行されます。

NESTED PATH操作: 操作のシーケンスのスコープ設定

ネストしたパス操作では、操作のシーケンスを適用するデータの特定部分であるスコープを定義します。ネストしたパス操作の主なユースケースは、配列要素の反復です。

有効範囲の限定のための構成として、ネストしたパス操作によって変更の対象をデータのサブセットに制限します。それ自体は、変更操作ではありません。

ネストしたパス操作の有効範囲内で実行される操作には、別のネストしたパス操作を含めることができます。そのため、ネストしたパス内でネストしたパスを使用でき、それを繰り返すことで、より広い有効範囲内に狭い有効範囲を定義して任意のレベルでデータを処理できます。特に、ネストしたパスを使用すると、任意の場所にネストした配列の要素を処理できます。

ネストした有効範囲は、キーワードNESTED PATHの直後に続くターゲット・パスによって定義されます(キーワードPATHは省略できます)。そのパスの後に、カッコ((、))で囲んだ有効範囲指定操作のシーケンスを続けます。

ターゲット・パスのコンテキスト項目は、そのターゲットによるNESTED PATH操作が最上位(最も外側)のコンテキストにある場合は、そのパスで$を使用して指定します。それ以外の場合、つまり、その対象によるNESTED PATH操作が別のNESTED PATHの内側にある場合は、@を使用して指定します。

ターゲット・パスによって指定された対象となるデータは、有効範囲指定操作のコンテキスト項目になります。そうした操作では、$ではなく@で示されます。

たとえば、'$.employees[*]'は最上位コンテキストのターゲット・パスとして使用できます。'@.employees[*]'はネストした有効範囲のターゲット・パスとして使用できます。対象とされるフィールドemployeesを持つオブジェクトは、最初のケースではトップ・レベルであり、2番目のケースではそれよりも下位のレベルです。

いずれのケースも、ターゲット・パスにより、配列employeesの各要素を有効範囲指定操作のコンテキスト項目として定義します。各操作は、これらの要素の1つずつに配列順に適用されます。

同様に、'$.employees[2 to 10]'は、有効範囲指定操作を3番目から11番目の従業員に適用し、'$.employees[3,7]'は、それらの操作を4番目と7番目の従業員に順に適用します。(同様に、$のかわりに@を使用します)。

次のコードでは、ターゲット・パスは$.LineItems[*]であるため、カッコで囲まれた操作のシーケンスに現れる@は$.LineItems[*]の略語になります。このコードは、配列LineItemsの各要素のUnitPriceを1.02で乗算して変更します。 

NESTED PATH '$.LineItems[*]'
  (SET '@.UnitPrice' = PATH '@.UnitPrice * 1.02'

配列の各要素を対象とするには、配列自体ではなく、値が配列である対象フィールドの名前の後に明示的に[*]を含める必要があります。暗黙的な反復はありません。有効範囲が設定された操作では、特定の配列要素の参照(3番目の要素@[2]など)が必要な場合は、配列自体('$.employees'など)を対象にできます。ただし、これは一般的なユースケースではありません。

ネストしたパス有効範囲内の操作のLHSでは、$を使用できません。そのかわりに、@を使用する必要があります。これは、ネストした有効範囲の変換/変更が、その有効範囲に限定されるという別の方法であり、実行される操作はその範囲外では動作できません。

ただし、有効範囲指定操作のRHSでは$を使用できます。たとえば、このコードでは、まず各従業員に10%の昇給(*係数1.1)を与えてから、各従業員に同じ賞与($.department.bonusの値)を割り当てます。 

NESTED PATH '$.employees[*]'
  (SET '@.salary' = PATH '@.salary * 1.1',
   SET '@.bonus'  = PATH '$.department.bonus')

有効範囲指定操作のRHSに現れる$は、常にjson_transform呼出しの最上位(最も外側)のコンテキストを参照します。

ネストした操作内(再掲):

  • @は、ネストしたパスによって対象とされるデータを参照します。

  • $は、最上位(最も外側)のコンテキスト項目を参照し、操作のRHSでのみ使用できます。

JSON_TRANSFORM操作のハンドラ

json_transform操作のハンドラは次のとおりです:

  • ON EMPTY - RHSが対象とする値がJSON nullであるか欠落している場合の動作を指定します。

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

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

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

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

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

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

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

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

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

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

    • REPLACE ON EXISTING—対象となる場所のデータをSQLの結果式の値に置き換えます。

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

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

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

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

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

    • CREATE ON MISMATCH — 対象となる値を(単一の)配列にラップします。

    • REPLACE ON MISMATCH — 対象値を空配列([])に置き換えます。

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

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

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

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

    パス式配列ステップでは、ON MISSINGハンドラは、対象となる配列自体がデータから欠落していることを意味しません。それはかわりにON EMPTYハンドラによって対応されます。ON MISSINGハンドラは、配列ステップによって指定された1つ以上の位置がデータと一致しない場合に対応します。たとえば、配列ステップ[2]はデータ配列["a", "b"]と一致しません。この配列には位置2に要素がないためです。

  • 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—対象となるデータを削除します。

    ON NULLを許可するすべてのハンドラのデフォルトの動作は、NULL ON NULLです。

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

  • 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

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

    • 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

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

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

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

  • 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: ハンドラなし

例12-1 JSON_TRANSFORMを使用したJSON列の更新

この例では、lastUpdatedフィールドの値に現在のタイムスタンプを設定して、j_purchaseorder.po_document内のすべての文書を更新します。

フィールドがすでに存在する場合はその値が置き換えられ、存在しない場合はフィールドとその値が追加されます。(つまり、デフォルトのハンドラ(REPLACE ON EXISTINGおよびCREATE ON MISSING)が使用されます。) 

UPDATE j_purchaseorder SET po_document =
  json_transform(po_document, SET '$.lastUpdated' = SYSTIMESTAMP);

例12-2 JSON_TRANSFORMによるJSONデータの即時の変更

この例では、j_purchaseorder.po_document内のすべてのドキュメントを選択し、フィールド"Special Instructions"が削除されたドキュメントのフォーマット出力の更新済のコピーを返します。

フィールドが存在しない場合は、何も行われません(エラーは発生しません)。IGNORE ON MISSINGがデフォルトの動作です。

戻りデータ型はCLOBです。(キーワードPRETTYはJSON型には使用できません。) 

SELECT json_transform(po_document,
                      REMOVE '$."Special Instructions"'
                      RETURNING CLOB PRETTY)
  FROM j_purchaseorder;

例12-3 JSON_TRANSFORMを使用したフィールドの追加

次の2つのjson_tranformの使用方法は同等です。これらはどちらも、Commentsフィールドとその値"Helpful"を追加します。フィールドがすでに存在する場合、エラーが発生します。フィールド値の入力は、リテラルのSQL文字列'Helpful'です。SETのデフォルトの動作は、CREATE ON MISSINGです。 

json_transform(po_document, INSERT '$.Comments' = 'Helpful')
json_transform(po_document, SET '$.Comments' = 'Helpful'
                                ERROR ON EXISTING)

例12-4 JSON_TRANSFORMを使用したフィールドの削除

この例では、フィールドLineItems.Part.UPCCodeの値が85391628927であるフィールドLineItemsの出現を削除します。フィールドが存在しない場合は、何も行われません(エラーは発生しません)。IGNORE ON MISSINGがデフォルトの動作です。 

json_transform(po_document,
               REMOVE '$.LineItems?(@.Part.UPCCode == $v1)')
               PASSING 85391628927 AS "v1")

フィルタリングに使用されるUPCコードは、PASSING句のSQLバインド変数v1に対応する、SQL/JSON変数$v1の値として提供されます。この手法により、頻繁に使用する問合せでは重要になる問合せの再コンパイルを回避できます。

例12-5 JSON_TRANSFORMを使用したフィールド値の作成または置換

この例では、Addressフィールドの値にJSONオブジェクト{"street":"8 Timbly Lane", "city":"Penobsky", "state":"Utah"}が設定されます。フィールドが存在しない場合はフィールドが作成されます。フィールドの既存の値が置き換えられます。フィールド値の入力はリテラルのSQL文字列です。入力値にFORMAT JSONが指定されているため、更新されたフィールド値はJSONオブジェクトです。 

json_transform(po_document,
               SET '$.Address' =
                   '{"street":"8 Timbly Rd.",
                     "city":"Penobsky",
                     "state":"UT"}'
                   FORMAT JSON)

データベース初期化パラメータcompatibleが20以上の場合、キーワードFORMAT JSONを使用するかわりの方法としては、JSONデータ型のコンストラクタJSONをフィールド値の入力データに適用する方法があります。 

json_transform(po_document,
               SET '$.Address' = 
                   JSON('{"street":"8 Timbly Rd.",
                          "city":"Penobsky",
                          "state":"UT"}'))

FORMAT JSONまたはコンストラクタJSONを使用しない場合、Addressフィールドの値は、SQL入力文字列に対応するJSON文字列になります。入力の各二重引用符(")文字は、JSON文字列ではエスケープされます。 

"{\"street\":\"8 Timbly Rd.\","city\":\"Penobsky\",\"state\":\"UT\"}"

例12-6 JSON_TRANSFORMを使用した既存のフィールド値の置換

この例では、Addressフィールドの値にJSONオブジェクト{"street":"8 Timbly Lane", "city":"Penobsky", "state":"Utah"}が設定されます。フィールドの既存の値が置き換えられます。フィールドが存在しない場合は何も行われません。この例と例12-5の違いは、IGNORE ON MISSINGハンドラが存在することのみです。 

json_transform(po_document,
               SET '$.Address' =
                   '{"street":"8 Timbly Rd.",
                     "city":"Penobsky",
                     "state":"UT"}'
                   FORMAT JSON
                   IGNORE ON MISSING)

例12-7 FORMAT JSONを使用したJSONのブール値の設定

この例では、フィールドAllowPartialShipmentの値をJSON言語のブール値trueに設定します。キーワードFORMAT JSONを指定しないと、かわりにフィールドにJSON言語の文字列"true"が設定されます。 

json_transform(po_document,
               SET '$.AllowPartialShipment' = 'true' FORMAT JSON)

例12-8 JSON_TRANSFORMを使用した配列要素の設定

この例では、配列Phoneの先頭の要素にJSON文字列"909-555-1212"が設定されます。 

json_transform(po_document,
               SET '$.ShippingInstructions.Phone[0]' = '909-555-1212')

操作の前の配列Phoneの値が次の値の場合: 

[ {"type":"Office","number":"909-555-7307"},
  {"type":"Mobile","number":"415-555-1234"} ]

変更後の値は次のようになります。

[ "909-555-1212",
  {"type":"Mobile","number":415-555-1234"} ]

例12-9 JSON_TRANSFORMを使用した配列の末尾への要素の追加

次の2つのjson_tranformの使用方法は同等です。これらはどちらも、要素"909-555-1212"を配列Phoneの末尾に追加します。 

json_transform(po_document,
               APPEND '$.ShippingInstructions.Phone' =
                      '909-555-1212')
json_transform(po_document,
               INSERT '$.ShippingInstructions.Phone[last+1]' =
                      '909-555-1212')

例12-10 JSON_TRANSFORMを使用した配列の末尾への複数要素の追加

この例では、その値が配列である各Phoneフィールドに、電話番号"415-555-1234"と"909-555-1212"を順に追加します。非配列のPhoneフィールドは無視されます(一致しません)。そのため、生成される各Phoneフィールドは、末尾が[ ..., "415-555-1234", "909-555-1212" ]になります。

追加する要素と追加する順序は、配列によって提供されます。この場合は、JSONコンストラクタを使用してSQLで構築されるリテラルJSON値[ "415-555-1234", "909-555-1212" ]です。

この配列は、SQL/JSON変数$newの値として、APPEND操作に渡されます。追加される要素は、すべての配列要素を順番に並べたものであり、RHSのパス式で[*]を使用して指定します。 

json_transform(po_document,
               SET '$new' = JSON('[ "415-555-1234", "909-555-1212" ]'),
               APPEND '$.ShippingInstructions.Phone' =
                       PATH '$new[*]')

JSONコンストラクタを使用するかわりに、FORMAT JSONを使用することもできます。 

json_transform(po_document,
               SET '$new' = '[ "415-555-1234", "909-555-1212" ]' FORMAT JSON,
               APPEND '$.ShippingInstructions.Phone' =
                       PATH '$new[*]')

例12-11 JSON_TRANSFORMを使用した配列の先頭への複数要素の追加

この例は、例12-10に似ています。その値が配列である各Phoneフィールドの先頭に、電話番号"415-555-1234"と"909-555-1212"を順に追加します。そのため、結果の各Phoneフィールドの先頭は、["415-555-1234", "909-555-1212",...]になります。 

json_transform(po_document,
               SET '$new' = JSON('[ "415-555-1234", "909-555-1212" ]'),
               PREPEND '$.ShippingInstructions.Phone' =
                       PATH '$new[*]')

例12-12 JSON_TRANSFORMを使用した述語を満たす配列要素の削除

この例では、UPCCodeが85391628927であるLineItems配列内のすべてのオブジェクトを削除します。これらは、指定された述語を満たす配列要素で、値85391628927のフィールドUPCCodeを持つオブジェクトであるフィールドPartを持つオブジェクトが必要です。 

json_transform(po_document,
               REMOVE '$.LineItems[*]?(@.Part.UPCCode == 85391628927)')

例12-13 JSON_TRANSFORMを使用したフィールド値による配列内の要素のソート

この例では、配列LineItems内のオブジェクトを最初にフィールドPart.UnitPriceでソートし、次にフィールドItemNumberでソートします。どちらの場合も、高い数値から低い数値にソートします(キーワードDESC)。(昇順のASCがデフォルトです)。 

SELECT json_transform(po_document,
                      SORT '$.LineItems'
                        ORDER BY '$.Part.UnitPrice' DESC,
                                 '$.ItemNumber' DESC
                      RETURNING VARCHAR2(4000))
  FROM j_purchaseorder;

次に、この結果の1行を示します。要素は、降順のUnitPrice値でソートされています。UnitPrice値が同じ要素は、降順のItemNumber値でソートされます。 

{"LineItems" :
 [ {"ItemNumber" : 1,
    "Part" :
    {"Description" : "Making the Grade",
     "UnitPrice"   : 20,
     "UPCCode"     : 27616867759},
    "Quantity" : 8},
   {"ItemNumber" : 3,
    "Part" :
    {"Description" : "Eric Clapton: Best Of 1981-1999",
     "UnitPrice"   : 19.95,
     "UPCCode"     : 75993851120},
    "Quantity" : 5},
   {"ItemNumber" : 2,
    "Part" :
    {"Description" : "Nixon",
     "UnitPrice"   : 19.95,
     "UPCCode"     : 717951002396},
    "Quantity" : 5} ]}

例12-14 JSON_TRANSFORMを使用した配列の各要素の更新

この例では、NESTED PATH操作を使用して、各明細項目の単価を1.2倍に増やし、更新した単価から計算される新しいフィールドTotalPriceを各配列要素(オブジェクト)に追加します。 

json_transform(po_document,
               NESTED PATH '$.LineItems[*]'
                 (SET '@.TotalPrice' = PATH '@.Quantity * @.Part.UnitPrice'))

例12-15 NESTED PATHを使用した有効範囲の限定、KEEPによるJSON_TRANSFORMプルーニングの制限

この例では、KEEP操作の有効範囲を特定のネストしたパスに制限します。その有効範囲外のデータはプルーニングされません。その結果、配列LineItemsの要素のみで、UnitPriceおよびQuantity以外のフィールドが削除されています。 

json_transform(po_document,
               NESTED PATH '$.LineItems[*]'
                 (KEEP '@.UnitPrice', '@.Quantity')))

例12-16 JSON_TRANSFORM: SETおよびCASEによる変更の制御

この例では、CASEおよびSETを使用して、条件付きでフィールドTotalPriceを設定し、そのフィールドが存在しない場合は作成します。データに適用すると、それぞれのWHENテストは成功するまで順番に試行されます。その後、成功したテストに対応するSET操作が実行されます。2番目は、Quantityが5以上7未満の場合に適用されます。WHEN句が適用されない場合(Quantityが7未満でない場合)

  • 最初のWHEN句は、5未満のQuantityフィールドのデータに適用されます。フィールドTotalPriceは、割引なしで計算されます。

  • 2番目のWHEN句は、5以上7未満のQuantityフィールドのデータに適用されます。フィールドTotalPriceは、10%の割引で計算されます。

  • どちらのWHENテストにも成功しない場合(ELSE句)、TotalPriceは15%の割引で計算されます。

    この句は、フィールドQuantityが存在しない場合や、7未満の比較が実施されない数値以外のJSON値である場合にも適用されます。 

json_transform(
  po_document,
  NESTED PATH '$.LineItems[*]'
    ( CASE WHEN '@?(@.Quantity < 5)' THEN
             ( -- No discount
               SET '@.TotalPrice' = PATH '@.Quantity * @.UnitPrice' )
           WHEN '@?(@.Quantity < 7)' THEN
             ( -- 10% discount
               SET '@.TotalPrice' = PATH '@.Quantity * @.UnitPrice * 0.9' )
           ELSE
             ( -- 15% discount
               SET '@.TotalPrice' = PATH '@.Quantity * @.UnitPrice * 0.85' )
      END ))

関連項目

関連項目:

親トピック: JSONデータの挿入、更新およびロード


脚注の凡例

脚注1: ファンクションjson_transformのSQL戻り型と、変更操作の等号(=)に続くSQL結果式のタイプを混同しないでください。
脚注2: SQL式とパス式の両方を含めるとエラーが発生します。
脚注3: これは実際には、単一のRHSパス・マッチング値(この場合は配列)を、その値の単一シーケンスであるかのように処理する場合にすぎません。