22.3 JSON_TABLEによるSQL/JSON問合せファンクションおよび条件の一般化

SQL/JSONファンクションjson_tableは、SQL/JSON条件json_existsと、SQL/JSONファンクションjson_valueおよびjson_queryを一般化します。これらの関数を使用して実行できる処理はすべて、json_tableを使用して実行できます。これらによって実行されるジョブについては、これらの関数の構文の方がjson_tableの構文よりも簡単に使用できます。

同じデータにアクセスするために、json_exists、json_valueまたはjson_queryのいずれかを複数回使用するか、それらの組合せを使用する場合は、そのかわりにjson_tableの単一の呼出しを使用できます。これにより、多くの場合、問合せが読みやすくなり、1回のみデータを読み取るように最適化されます。

このため、オプティマイザは一般にjson_exists、json_valueおよびjson_queryの複数の呼出し(任意の組合せ)を、それよりも少ないjson_tableの呼出しに自動的にリライトします。(実行計画を調べて、そのようなリライトが特定の問合せに対して発生したかどうかを確認できます)。

例22-4および例22-5に、これを示します。これらではそれぞれ、列j_purchaseorder.data内の各オブジェクトによって使用される要求者および一連の電話を選択しています。ただし、json_tableを使用する例では、その列が4回ではなく1回のみ読み取られます。

これらの例では、ブールJSON値を表すためにBOOLEAN SQL値を使用しています。(Oracle Databaseリリース23aiでは、データ型BOOLEANに対するOracle SQLサポートが導入されています)。

JSON値のnullは、SQLに関するかぎりは1つの値であり、SQLで値の欠如(存在しないデータ、不明なデータまたは適用できないデータ)を表すNULLとは異なります。これらの例では、オブジェクト属性zipCodeのJSON値がnullの場合に、SQL BOOLEAN値のTRUEが返されます。

例22-4 JSONデータに複数回アクセスすることによるデータの抽出

この例では、SQL列j_purchaseorder.dataにアクセスするSQLファンクションの呼出しを4回使用しているため、その列を4回読み取ります。

SELECT json_value(data, '$.Requestor' RETURNING VARCHAR2(32)),
       json_query(data, '$.ShippingInstructions.Phone'
                  RETURNING VARCHAR2(100))
  FROM j_purchaseorder
  WHERE json_exists(data, '$.ShippingInstructions.Address.zipCode')
    AND json_value(data,  '$.AllowPartialShipment'
                   RETURNING BOOLEAN) = TRUE;

例22-5 JSON_TABLEを使用した複数回読み取ることのないデータの抽出

この例では、SQL列j_purchaseorder.dataにアクセスする単一のjson_table呼出しを使用しているため、その列を1回のみ読み取ります。

この例では、どちらの仮想列にもBOOLEAN SQL値を使用します。

• 列partialは、データ(フィールドAllowPartialShipment)のJSONブール値に対応します。この列には、json_valueセマンティクスが使用されます。

• 列has_zipは、json_tableキーワードEXISTSの使用による結果です。このキーワードは、json_existsのセマンティクスを使用するように指定します。

ノート: JSONデータがJSONデータ型の場合は、キーワードFORMAT JSONを使用しないでください。使用するとエラーが発生します。

SELECT jt.requestor, jt.phones
  FROM j_purchaseorder,
       json_table(data, '$'
         COLUMNS (
           requestor VARCHAR2(32 CHAR) PATH '$.Requestor',
           phones    VARCHAR2(100 CHAR) FORMAT JSON
                     PATH '$.ShippingInstructions.Phone',
           partial   BOOLEAN PATH '$.AllowPartialShipment',
           has_zip   BOOLEAN EXISTS
                     PATH '$.ShippingInstructions.Address.zipCode')) jt
  WHERE jt.partial AND jt.has_zip;

WHERE句は、あるいは次のように記述することもできます。

WHERE jt.partial = TRUE AND jt.has_zip = TRUE