Oracle SQLファンクションJSON

14 Oracle SQLファンクションJSON_MERGEPATCH

Oracle SQLファンクションjson_mergepatchを使用して、JSONドキュメントの特定の部分を更新できます。指定したJSONドキュメントに対する変更を指定するJSONマージ・パッチ・ドキュメントを渡します。JSONマージ・パッチはIETF標準です。

ファンクションjson_mergepatchは、変更されたJSONデータを返します。

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

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

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

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

JSONマージ・パッチは、主に構造にオブジェクトを使用し、明示的なnull値を使用しないJSONドキュメントの更新に適しています。配列要素を追加、削除または変更することはできません(配列全体を明示的に置き換える場合を除く)。また、フィールドの値をnullに設定することはできません。

JSONマージ・パッチは、UNIXのpatchユーティリティと同様に動作します。パッチを適用するソース・ドキュメントおよび変更を指定するパッチ・ドキュメントを指定すると、更新された(パッチが適用された)ソース・ドキュメントのコピーが返されます。パッチ・ドキュメントでは、ソースと結果のドキュメントの違いを指定します。UNIXのpatchの場合、差異はUNIX diffユーティリティ出力の形式になります。JSONマージ・パッチの場合、ソースと宛先の両方がJSONドキュメントです。

JSONマージ・パッチは、ソースとパッチのコンテンツのマージとして考えることができます。2つのオブジェクト(ソースとパッチから1つずつ)をマージする場合、一方のオブジェクトにあるが他方には存在しないフィールドを持つメンバーは結果に保持されます。例外として、フィールド値がnullであるパッチ・メンバーは、ソース・オブジェクトにそのようなフィールドがない場合には無視されます。

同じフィールドを持つオブジェクト・メンバーをマージする場合:

パッチ・フィールドの値がNULLの場合、フィールドはソース・ホースからドロップされ、結果に含まれません。
それ以外の場合、フィールドは結果に保持されますが、その値はソース・フィールド値とパッチ・フィールド値をマージした結果になります。つまり、この場合のマージ操作は、値自体がオブジェクトであるフィールドに対して再帰的に行われます。

より正確には、JSONマージ・パッチは次のように動作します。

パッチがJSONオブジェクトでない場合は、パッチでソースを置き換えます。
それ以外(パッチがオブジェクト)の場合は、次の手順を実行します。
1. ソースがオブジェクトでない場合は、空のオブジェクト({})であるかのように動作します。
2. パッチ・オブジェクトの(p-field:p–value)メンバーに対して繰り返されます。
  - パッチ・メンバーのp-valueがnullの場合は、対応するメンバーをソースから削除します。
  - それ以外の場合は、再帰します。対応するソース・フィールドの値を、対応するソース・フィールドの値を、(次のパッチとして) p-valueでその値を(次のソースとして)マージ・パッチ適用した結果で置き換えます。

パッチ・フィールド値nullに特別な意味がない場合(そのフィールドに対応するソース・メンバーを削除)、これをフィールド値として使用して対応するソース・フィールド値をnullに設定できます。この特別な削除動作では、ソース・フィールド値をnullに設定できません。

例:

パッチ・メンバー"PONumber":99999は、フィールドPONumberでソース・メンバーをオーバーライドし、その値をパッチ指定の値99999に置き換えます。

json_mergepatch('{"User":"ABULL", "PONumber":1600}', '{"PONumber":99999}')の結果は{"User":"ABULL", "PONumber":99999}になります。
パッチ・メンバー"tracking":123456は、欠落したソース・メンバーをフィールドtrackingでオーバーライドし、そのパッチ・メンバーを結果に追加します。また、ソース・メンバー"PONumber":1600は、フィールドPONumberで欠落したパッチ・メンバーをオーバーライドします。これは結果に保持されます。

json_mergepatch('{"PONumber":1600}', '{"tracking":123456}')の結果は{"PONumber":1600, "tracking":123456}"になります。
パッチ・メンバー"Reference":nullは、フィールドReferenceでソース・メンバーをオーバーライドし、結果から削除します。

json_mergepatch('{"PONumber":1600, "Reference":"ABULL-20140421"}', '{"Reference":null}')の結果は{"PONumber":1600}になります。
パッチ値[4,5,6]は対応するソース値[1,2,3]をオーバーライドして、置き換えます。

json_mergepatch('{"PONumber":1600, "LineItems":[1,2,3]}', '{"LineItems":[4,5,6]}') results in {"PONumber":1600, "LineItems":[4,5,6]}.

ノート:

マージ・パッチ・プロシージャでは、特に、非オブジェクト・パッチに対する再帰的な動作がないことにより、配列の値を個別に追加、削除または置換できません。このような変更を加えるには、配列全体を置き換える必要があります。たとえば、ソース・ドキュメントにメンバーPhone:["999-555-1212", "415-555-1234"]がある場合、2番目の電話番号を削除するには、コンテンツにメンバー"Phone":["999-555-1212"]があるパッチを使用できます。

例14-1 JSONマージ・パッチ文書

例1-1に示したドキュメントに適用される場合、このJSONマージ・パッチ・ドキュメントでは、次のことが実行されます。

メンバー"Category" : "Platinum"を追加します。
フィールドShippingInstructionsを持つメンバーを削除します。
フィールドSpecial Instructionsの値を文字列"Contact User SBELL"に置き換えます。
フィールドLineItemsの値を空の配列[]に置き換えます
メンバー"AllowPartialShipment" : nullをメンバー"Allow Partial Shipment" : falseに置き換えます(フィールド値がすでにfalseであったため、実質的にはフィールドの名前が変更されます)。

{ "Category" : "Platinum",
  "ShippingInstructions" : null,
  "Special Instructions" : "Contact User SBELL",
  "LineItems" : [],
  "AllowPartialShipment" : null,
  "Allow Partial Shipment" : false }

例14-2 マージ・パッチ適用されたJSON文書

この例は、例1-1の文書を例14-1のパッチでマージ・パッチ適用した結果の文書を示しています。

{ "PONumber" : 1600,
  "Reference" : "ABULL-20140421",
  "Requestor" : "Alexis Bull",
  "User" : "ABULL",
  "CostCenter" : "A50",
  "Special Instructions" : "Contact User SBELL",
  "Allow Partial Shipment" : false,
  "LineItems" : [],
  "Category" : "Platinum" }

例14-3 JSON_MERGEPATCHを使用したJSON列の更新

この例では、j_purchaseorder.dataにあるすべての文書を更新して、フィールド"Special Instructions"を削除します。

UPDATE j_purchaseorder SET data =
  json_mergepatch(data, '{"Special Instructions":null}');

例14-4 JSON_MERGEPATCHによるJSONデータの即時の変更

この例では、j_purchaseorder.dataにあるすべての文書を選択し、それらのコピーを更新(フィールド"Special Instructions"を削除)し整形して返します。この例の戻りデータ型はCLOBです。(キーワードPRETTYはJSON型には使用できません。)

SELECT json_mergepatch(data, '{"Special Instructions":null}'
                       RETURNING CLOB PRETTY)
  FROM j_purchaseorder;

関連トピック