13 Oracle SQLファンクションJSON_TRANSFORM

Oracle SQLファンクションjson_transformはJSONデータを変更します。これを使用すると、1回の呼出しで複数の変更操作が可能です。

json_transformでは、(1)実行する変更操作、および(2)変更するデータ(場所)を対象とするSQL/JSONパス式を指定します。

それらの操作は、指定された順序で、対象となっている入力データのコピーに適用されます。各操作は、前の操作からもたらされたデータに対して作用します。つまり、先行する操作すべてを適用した結果であるデータに対して作用します。脚注1

ノート:

ファンクションjson_transformは、一時データに対して作用します。つまり、その入力データのコピーに対して作用します。その元の入力データは変更されません。これは、その一時データを、その引数による操作すべてで更新して返します。永続データを更新するには、SQL UPDATE文内でjson_transformを使用する必要があります。

1回のjson_transform呼出しにおける複数の操作のどれかに失敗すると、エラーが発生します。後続の操作は試みられず、値は返されません。具体的に述べると、これは、json_transformがUPDATE文で使用されておりエラーが発生した場合は永続データは変更されないということです。json_transformの使用による永続データの更新はアトミックです。

ノート:

JSONデータがJSONデータ型として格納されている場合、それに対する更新は、多くの場合、インプレース(つまり、ピース単位)で実行されます。この部分更新は、文書全体を書き換えるのではなく、変更された、文書の特定の部分のみを、永続的に更新する必要があるという意味です。これは、大きな文書内の小さな変更に特に役立ちます。また、部分更新では、データベースのREDOログやレプリケーション・ログに書き込む必要があるデータの量が減ることでパフォーマンスが向上します。

json_transformの様々な操作と全体的な動作の詳細を示す前に、単純な例をいくつか見てみましょう。

例13-1では、json_transformの演算子SETを含むSQL UPDATE文を使用して、コレクションの1つのJSON文書内の1つのフィールドを更新します。json_transformのSETは"アップサート"演算子(更新または挿入)です。つまり、その操作でのパス式で既存フィールドが対象となっている場合はその値が更新され、そのようなフィールドが存在しない場合はそのフィールドが追加されます。

例13-1 JSON_TRANSFORMの使用による1つのJSON文書内のフィールドの更新

UPDATE j_purchaseorder po
  SET data = json_transform(data,
                            SET '$.costCenter' = 'Z20')
  WHERE po.data.User = 'ABULL';

このSQL UPDATE文では、更新演算子SET (この例で最初に出現しているSET)が使用されています。これは、Userフィールドの値が"ABULL"である、列data内の発注書文書を脚注2、json_transformによる変更の結果であるデータ(文書)に更新しています。列dataがJSON型である場合は、更新されたフィールド値のみが書き換えられます(インプレース更新)。文書全体が書き換えられるわけではありません。

json_transformのコールでは、入力文書のコピーであるデータが返されます。ただし、その最上位フィールドcostCenterの値は"Z20"になっています。これを行うために、json_transformの演算子SET (この例で2番目に出現しているSET)が使用されています。SQL/JSONパス式$.costCenterでは、この文書の最上位($)にあるフィールドcostCenterが対象となっています。

(この例では、種類とレベルが異なる2つのSET操作があります。一方は、SQL UPDATE文で、格納されている文書のデータを新しい値に設定するためにあります。もう一方は、json_transform操作で、入力文書のコピーにおいてフィールドcostCenterの値を設定するためにあります。)

例13-2は似ていますが、これは、コレクションのすべての文書内の単一のフィールド値を設定しています。

例13-2 JSON_TRANSFORMの使用によるすべての文書内のフィールド値の設定

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

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

指定された入力文書にそのようなフィールドがない場合は、json_transformの演算子SETによってそのフィールドを追加します。そのフィールドがすでに存在する場合は、その値を変更(更新)します。この"アップサート"動作では、デフォルトの操作ハンドラ(REPLACE ON EXISTINGとCREATE ON MISSING)が使用されていることが前提となっています。

例4-3で作成したj_purchaseorderデータを想定した場合、このようなフィールドはどの文書にもないため、それが各文書に追加されます。

例13-3では、無条件に、コレクションのすべての文書にフィールドを追加しています。挿入するフィールドが文書にすでに存在する場合は、エラーが発生します。

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

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

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

ノート:

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

ファンクションjson_transformは、入力としてJSONデータを受け入れ、出力としてJSONデータを返します。入力または出力として、JSONデータをサポートしている任意のSQLデータ型(JSON、VARCHAR2、CLOBまたはBLOB)を使用できます。脚注3 デフォルトでは、出力のSQL型は入力のSQL型と同じです。脚注4 RETURNING句をjson_transformとともに使用して、別のSQL戻り型を指定できます。

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

  • PASSING句では、SQL/JSON変数へのバインド変数のSQLバインディングを指定します。「JSON_TRANSFORMでのバインド変数の使用」を参照してください。

    これは、SQL/JSON条件json_existsおよびSQL/JSON問合せファンクションの場合と同じです。

  • RETURNING句では、戻りデータ型を指定します。

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

UPDATE文でjson_transformを使用する以外に、SELECTリストでそれを使用して、選択した文書のコピーを変更したものを作成できます。これをさらに返すことや処理することができます。例13-4ではこれを示しています。(RETURNING句の使用も示しています。

例13-4 JSON_TRANSFORMによるJSONデータの即時の変更

この例では、j_purchaseorder.dataにあるすべての文書を選択し、それらのコピーを更新(フィールド"Special Instructions"を削除)し整形して返します。

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

戻りデータ型はCLOBとして指定されています。(キーワードPRETTYはJSONデータ型には使用できません。) 

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

操作の指定の最後の部分は、オプションの一連のハンドラです。演算子によって、使用できるハンドラは異なり、ハンドラのデフォルトが異なります。(ハンドラを、そのハンドラを使用できない演算子に対して指定した場合は、エラーが発生します。)「JSON_TRANSFORM操作のハンドラ」を参照してください。

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

_________________________________________________________

  • JSON_TRANSFORMでのバインド変数の使用
    一般的には、パフォーマンスと柔軟性を高めるためにOracle SQLファンクションjson_transformとともにバインド変数を使用します。
  • JSON_TRANSFORM操作(代入など)
    Oracle SQLファンクションjson_transformは、その入力JSONデータのコピーを変更し、変更した結果を返します。
  • JSON_TRANSFORMの演算子ADD_SET
    JSON_TRANSFORMの演算子ADD_SETは、要素を集合に追加する場合と同様に、欠落している値を配列に追加します。
  • JSON_TRANSFORMの演算子APPEND
    JSON_TRANSFORMの演算子APPENDは、配列に値を追加します。
  • JSON_TRANSFORMの演算子CASE
    JSON_TRANSFORMの演算子CASEは、条件付きで一連の操作を実行します。これは制御操作であり、条件付きでその他のjson_transform操作を適用し、それによってデータを変更できます。
  • JSON_TRANSFORMの演算子COPY
    JSON_TRANSFORMの演算子COPYは、配列の要素を置き換えます。
  • JSON_TRANSFORMの演算子INSERT
    JSON_TRANSFORMの演算子INSERTは、指定された場所(オブジェクト・フィールドまたは配列位置)に値を挿入します。
  • JSON_TRANSFORMの演算子INTERSECT
    JSON_TRANSFORMの演算子INTERSECTは、指定された一連の値にあるもの以外の配列要素を削除します。これは積集合操作です。
  • JSON_TRANSFORMの演算子KEEP
    JSON_TRANSFORMの演算子KEEPは、指定されたどのパス式でも対象となっていない、入力データのすべての部分を削除します。
  • JSON_TRANSFORMの演算子MERGE
    JSON_TRANSFORMの演算子MERGEは、指定されたフィールドをオブジェクトにマージします(場合によってはそのオブジェクトを作成します)。
  • JSON_TRANSFORMの演算子MINUS
    JSON_TRANSFORMの演算子MINUSは、指定された一連の値にある配列要素を削除します。これは差集合操作です。
  • JSON_TRANSFORMの演算子NESTED PATH
    JSON_TRANSFORMの演算子NESTED PATHは、一連の操作を適用する範囲(データの特定の部分)を定義します。ネストしたパスによる操作の主なユースケースは、複数の配列要素の反復処理です。
  • JSON_TRANSFORMの演算子PREPEND
    JSON_TRANSFORMの演算子PREPENDは、値を配列の先頭に付加します。
  • JSON_TRANSFORMの演算子REMOVE
    JSON_TRANSFORMの演算子REMOVEは、指定されたパス式1つ以上で対象となっている、入力データの部分をすべて削除します。
  • JSON_TRANSFORMの演算子REMOVE_SET
    JSON_TRANSFORMの演算子REMOVE_SETは、集合から要素を削除する場合と同様に、指定された値のすべての出現を配列から削除します。
  • JSON_TRANSFORMの演算子RENAME
    JSON_TRANSFORMの演算子RENAMEは、フィールドの名前を変更します。
  • JSON_TRANSFORMの演算子REPLACE
    JSON_TRANSFORMの演算子REPLACEは、LHSのパス式で対象となっているデータを、RHSのSQL式の値に置換します。
  • JSON_TRANSFORMの演算子SET
    JSON_TRANSFORMの演算子SET (1)は、SQL/JSON変数の値を設定するか、(2)指定された場所でデータを置換または挿入します。
  • JSON_TRANSFORMの演算子SORT
    JSON_TRANSFORMの演算子SORTは、配列の要素をソートします。
  • JSON_TRANSFORMの演算子UNION
    JSON_TRANSFORMの演算子UNIONは、指定された一連の値から、欠落している配列要素を追加します。これは和集合操作です。
  • JSON_TRANSFORMの右辺(RHS)のパス式
    json_transformの右辺(RHS)のパス式は、その他の場所で使用できるパス式よりも一般的です。その構文と動作について詳しく説明します。
  • JSON_TRANSFORM操作のハンドラ
    ファンクションjson_transformの操作には、ハンドラ(一般的でない状況や想定外の状況でデフォルト動作をオーバーライドする)が関連付けられています。

関連トピック

関連項目:

『Oracle Database SQL言語リファレンス』のJSON_TRANSFORMに関する項

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


脚注の凡例

脚注1: この手法は、XMLデータを更新するためにXQuery Updateで使用されるスナップショット手法とは設計上異なります。スナップショット手法では、複数の操作が、静的スナップショットで事前に取得したとおりのまったく同じデータに対して作用します。
脚注2: ここでは、j_purchaseorderがJSONコレクション表(例6-2で作成したものなど)であると仮定しており、そのデータは例4-3で作成したとおりのものであることを想定しています。フィールドcostCenterはすべての文書に存在します。
脚注3: データ型JSONは、データベース初期化パラメータcompatibleが20以上である場合のみ使用可能です。
脚注4: ファンクションjson_transformのSQL戻り型と、変更操作での等号(=)の後に続くSQL式の戻り型を混同しないでください(「JSON_TRANSFORMの右辺(RHS)のパス式」を参照)。
脚注5: 初期化パラメータJSON_BEHAVIORを使用してjson_queryのこのデフォルトをオーバーライドできます — 「SQL/JSONファンクションJSON_QUERY」
を参照してください