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

10 Oracle SQLファンクションJSON_TRANSFORM

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

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

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

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

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

デフォルトの戻り型は、入力データ型によって異なります。入力の型がJSONの場合は、デフォルトの戻り型もJSONです。それ以外の場合、デフォルトの戻り型はVARCHAR2(4000)です。

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

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

指定する変更操作の順序に従って、オプションのRETURNING句およびPASSING句を含めることができます。RETURNING句は、SQL/JSONファンクションjson_queryの場合と同じです。PASSING句は、SQL/JSON条件json_existsの場合と同じです。これらは、それぞれ戻りデータ型とSQLバインド変数を指定します。

使用可能な変更操作は次のとおりです。

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

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

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

  • SET: 指定されたパス式で対象となるデータに、指定されたSQL式の値を設定します。デフォルトの動作は、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]}を返します。

  • APPEND: 指定されたSQL式の値を対象となる配列の末尾に追加します。デフォルトでは、パス式が配列を対象としない場合は、エラーが発生します。

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

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

KEEPおよびREMOVEを除くすべての操作では、パス式の後に等号(=)があり、次にSQLの結果式が続きます。

RENAMEを除くすべての操作では、結果式は、JSONデータ型のSQL値、またはJSON値として表すことができるSQL値に評価される必要があります。(これは、SQL/JSON生成関数json_objectに指定する名前と値のペアの値の部分の要件と同じです。)RENAME操作では、結果式はSQL文字列に評価される必要があります。

評価された結果式は、対象となるデータを変更するために使用されます。

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

次の3種類のハンドラがあります。

  • ON EXISTING: パス式がデータと一致した場合に行う動作を指定します。少なくとも1つの値を対象とします。

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

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

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

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

  • ON MISSING: パス式がデータと一致しない場合に行う動作を指定します。少なくとも1つの値を対象としません。

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

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

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

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

  • ON NULL: SQLの結果式の値がNULLの場合に行う動作を指定します。

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

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

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

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

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

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

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

  • KEEP: ハンドラなし

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

  • SET:

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

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

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

  • REPLACE:

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

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

  • INSERT:

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

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

  • APPEND:

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

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

例10-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);

例10-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;

例10-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)

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

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

json_transform(po_document, REMOVE '$.Special Instructions')

例10-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\"}"

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

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

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

例10-7 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"}]

例10-8 JSON_TRANSFORMを使用した配列の先頭への要素の追加

この例では、要素"909-555-1212"が配列Phoneの先頭に追加されます。位置0に挿入すると、既存のすべての要素が右にシフトされます。要素Nは要素N+1になります。 

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

例10-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')

関連項目

関連項目:

Oracle SQLファンクションjson_transformの詳細は、『Oracle Database SQL言語リファレンス』

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