30.7 JSON_VALUEの索引付けおよび問合せに関するデータ型の考慮事項
SQL/JSONファンクションjson_value
を使用して作成されたファンクション・ベースの索引では、問合せでjson_value
によって返されたデータ型が索引で指定された型と一致する必要があります。
RETURNING DATE
がjson_value
とともに使用される場合、索引が選択されるためには、同じ時間処理動作(切捨てまたは保存)を索引と問合せの両方で使用する必要があります。つまり、両方でRETURNING DATE PRESERVE TIME
を使用するか、両方でRETURNING DATE TRUNCATE TIME
(または、切捨てがデフォルトの動作であるためRETURNING DATE
)を使用する必要があります。
デフォルトでは、SQL/JSONファンクションjson_value
はVARCHAR2
値を戻します。json_value
を使用して関数ベースの索引を作成する場合、RETURNING
句または項目メソッドを使用して異なる戻りデータ型を指定しないかぎり、非VARCHAR2
値を想定する問合せに対してこの索引は選択されません。
たとえば、例30-10の問合せの場合は、json_value
でRETURNING NUMBER
が使用されています。この問合せに対して例30-3で作成した索引を選択できますが、これは、索引付けされたjson_value
式で戻り型としてNUMBER
が指定されているためです。索引にキーワードRETURNING NUMBER
が指定されていない場合、指定される戻り型はVARCHAR2(4000)
(デフォルト)になり、そのような問合せに対して索引は選択されません。
同様に、例30-2で作成した索引は、項目メソッドnumber()
を使用しており、これも戻り型としてNUMBER
が強制されるため、この問合せに対して選択できます。
ここで、戻される値の型がVARCHAR2
になるようにRETURNING
句なしでjson_value
を使用している例30-11と例30-12の問合せについて検討してみます。
例30-11では、SQLファンクションto_number
により、json_value
によって戻されるVARCHAR2
値を明示的に数値に変換しています。同様に、例30-12では、比較条件> (より大きい)により、暗黙的に値を数値に変換しています。
例30-3と例30-2の索引のいずれも、これらの問合せに対して選択されません。これらの問合せでは、いずれの事例でも型キャストのために正しい結果が戻される可能性がありますが、これらの索引を使用してこれらの問合せを評価することはできません。
また、一部のデータを特定のデータ型に変換できない場合に何が起きるかについても検討してください。たとえば、例30-10、例30-11および例30-12の問合せが実行された場合、"alpha"
などのPONumber
値はどうなるでしょうか。
例30-11および例30-12の場合、値を数値にキャストしようとするために問合せはエラーで停止します。ただし、例30-10の場合、デフォルトのエラー処理動作がNULL ON ERROR
であるため、非数値の"alpha"
は単純にフィルタで除外されます。値には索引が付けられますが、問合せに対しては無視されます。
同様に、たとえばDEFAULT '1000' ON ERROR
が問合せで使用されたとすると、つまり、数値のデフォルト値が指定されたとすると、値"alpha"
に対してエラーは発生しません。この場合、デフォルト値の1000
が使用されます。
ノート:
指定された問合せに対してSQL/JSONファンクションjson_value
に基づくファンクション・ベースの索引が選択されるためには、索引と問合せの両方で同じ戻りデータ型および処理方法(エラー、空、不一致)を使用する必要があります。
つまり、問合せの戻り型または処理方法を変更して、索引に指定されたものと一致しないようにする場合は、問合せパターンに依存する永続オブジェクトを再構築する必要があります。(同じことが、マテリアライズド・ビュー、パーティション、チェック制約およびそのパターンに依存するPL/SQLサブプログラムにも当てはまります。)
例30-10 JSON_VALUE問合せと明示的なRETURNING NUMBER
SELECT count(*) FROM j_purchaseorder po
WHERE json_value(data, '$.PONumber' RETURNING NUMBER) > 1500;
例30-11 JSON_VALUE問合せと明示的な数値変換
SELECT count(*) FROM j_purchaseorder po
WHERE to_number(json_value(data, '$.PONumber')) > 1500;
例30-12 JSON_VALUE問合せと暗黙的な数値変換
SELECT count(*) FROM j_purchaseorder po
WHERE json_value(data, '$.PONumber') > 1500;
親トピック: JSONデータの索引