JSON_VALUEを使用したユーザー定義オブジェクト型またはコレクション型インスタンスのインスタンス化

20.2 JSON_VALUEを使用したユーザー定義オブジェクト型またはコレクション型インスタンスのインスタンス化

SQL/JSONファンクションjson_valueを使用して、ユーザー定義のSQLオブジェクト型またはコレクション型のインスタンスをインスタンス化できます。これを行うには、パス式にJSONオブジェクトまたは配列を指定し、RETURNING句でオブジェクト型またはコレクション型をそれぞれ指定します。

対象のJSON配列の要素は、返されたコレクション型インスタンスの要素を提供します。JSON配列要素は、コレクション型要素に1対1で対応している必要があります。対応しない場合は、不一致エラーが発生します。オブジェクトであるJSON配列要素(次を参照)または配列は再帰的に処理されます。

対象のJSONオブジェクトのフィールドは、返されたオブジェクト型インスタンスの属性値を提供します。JSONフィールドは、オブジェクト型属性に1対1で対応している必要があります。対応しない場合は、不一致エラーが発生します。

対象のJSONオブジェクトのフィールド名は、オブジェクト属性のSQL名と比較されます。配列またはオブジェクトであるフィールド値は再帰的に処理されるため、最終的に、これはスカラーのSQLオブジェクト属性の名前と比較されるスカラー値を持つJSONフィールドの名前です。名前が一致しない場合(デフォルトでは大/小文字を区別しない)、不一致エラーが発生します。

すべての名前が一致する場合、対応するデータ型の互換性がチェックされます。型の非互換性がある場合は、不一致エラーが発生します。表18-2に、互換性のあるスカラー・データ型を明示します。その他の型の組合せには互換性がないため、不一致エラーが発生します。

次のいずれかに該当する場合、問合せコンパイル時に不一致エラーが発生します。デフォルトでは、不一致エラーは無視されますが、json_valueの起動に1つ以上のON MISMATCH句を含めることで、このエラー処理を変更できます。

対象のJSONオブジェクトのフィールドまたは対象のJSON配列の要素は、指定されたオブジェクト型インスタンスの属性または指定されたコレクション型インスタンスの要素に、数と種類が対応していません。
対象のJSONオブジェクトのフィールドが、指定されたオブジェクト型インスタンスの属性と同じ名前ではありません。デフォルトでは、この照合では大文字/小文字が区別されません。
JSON値のJSONおよびOracle SQLのスカラー・データ型とそれに対応するオブジェクト属性値またはコレクション要素値には、表18-2によると互換性がありません。

json_valueを使用すると、PL/SQLおよびSQLでオブジェクト型またはコレクション型のインスタンスを返せます。ただし、レコード型または索引表型のインスタンスを返す場合、NULL ON MISMATCH句とNULL ON EMPTY句の動作はわずかに異なります。これは、これらの型の値を不可分的にNULLにできないためです。詳細は、それらのドキュメントを参照してください。

例20-5 JSON_VALUEを使用したJSONデータからのユーザー定義オブジェクト・インスタンスのインスタンス化

この例では、SQLオブジェクト型shipping_tおよびaddr_tを定義します。オブジェクト型shipping_tには、それぞれVARCHAR2(30)型およびaddr_t型を持つ属性nameおよびaddressがあります。

オブジェクト型addr_tには、属性streetおよびcityがあります。

この例では、json_valueを使用して、フィールドShippingInstructionsの値であるJSONオブジェクトを選択し、SQLオブジェクト型shipping_tのインスタンスを返します。オブジェクト型属性の名前は、JSONオブジェクト・フィールド名と大/小文字を区別せずに照合されるため、たとえば、SQLオブジェクト型shipping_tの属性address (ADDRESSと同じ)はJSONフィールドaddressと一致します。

(ここでは、わかりやすいように、問合せの出力をフォーマット出力しています。)

CREATE TYPE addr_t AS OBJECT
  (street VARCHAR2(100),
   city   VARCHAR2(30));

-- Create after type addr_t, because that's referred to here.
--
CREATE TYPE shipping_t AS OBJECT
  (name    VARCHAR2(30),
   address addr_t); 

-- Query data to return shipping_t instances:
SELECT json_value(data, '$.ShippingInstructions'
                  RETURNING shipping_t)
  FROM j_purchaseorder;

JSON_VALUE(DATA,'$.SHIPPINGINSTRUCTIONS'RETURNING
-------------------------------------------------
SHIPPING_T('Alexis Bull',
           ADDR_T('200 Sporting Green',
                  'South San Francisco'))
SHIPPING_T('Sarah Bell',
           ADDR_T('200 Sporting Green',
                  'South San Francisco'))

例20-6 JSON_VALUEを使用したJSONデータからのコレクション型インスタンスのインスタンス化

この例では、SQLコレクション型items_tおよびSQLオブジェクト型part_tおよびitem_tを定義します。コレクション型items_tのインスタンスは、item_tインスタンスのVARRAYです。オブジェクト型item_tの属性partは、それ自体のSQLオブジェクト型part_tです。

次に、json_valueを使用してJSONを選択します

(ここでは、わかりやすいように、問合せの出力をフォーマット出力しています。)

CREATE TYPE part_t AS OBJECT
  (description VARCHAR2(30),
   unitprice   NUMBER);
 
CREATE TYPE item_t AS OBJECT
  (itemnumber NUMBER,
   part       part_t);
  
CREATE TYPE items_t AS VARRAY(10) OF item_t;

-- Query data to return items_t collections of item_t objects
SELECT json_value(data, '$.LineItems' RETURNING items_t)
  FROM j_purchaseorder;

JSON_VALUE(DATA,'$.LINEITEMS'RETURNINGITEMS_TUSIN
-------------------------------------------------
ITEMS_T(ITEM_T(1, PART_T('One Magic Christmas', 19.95)),
        ITEM_T(2, PART_T('Lethal Weapon', 19.95)))
ITEMS_T(ITEM_T(1, PART_T('Making the Grade', 20)),
        ITEM_T(2, PART_T('Nixon', 19.95)),
        ITEM_T(3, PART_T(NULL, 19.95)))