30.4 JSON_VALUEのためのBツリー索引の作成

SQL/JSONファンクションjson_valueのために、Bツリーの関数ベースの索引を作成できます。これを行うには、標準構文を使用してjson_valueを明示的に指定するか、項目メソッドを持つドット表記法構文を使用できます。これらいずれかの方法で作成された索引は、ドット表記法問合せとjson_value問合せのどちらとも一緒に使用できます。

例30-3では、表j_purchaseorderの列data内にあるオブジェクトのフィールドPONumberに対するjson_valueのファンクションベースの索引を作成しています。オブジェクトはパス式のコンテキスト項目として渡されます。

ここでERROR ON ERRORを使用することは、フィールドPONumberがないレコード、複数のPONumberフィールドを持つレコードまたは非数値の値を持つPONumberフィールドがあるレコードがデータに含まれる場合、索引の作成が失敗することを意味します。また、索引が存在する場合、このようなレコードを挿入しようとしても失敗します。

代替の方法としては、JSONデータに対する単純なドット表記法アクセスで説明されているように、ドット表記法構文を使用して対象となるデータに項目メソッドを適用して索引を作成する方法があります。例30-2に、これを示します。

例30-3と例30-2の両方で作成した索引は、ドット表記法構文を使用する問合せとjson_valueを使用する問合せのいずれかに対して選択できます。

json_value式の対象のフィールドで欠落している可能性のあるデータの索引付けを許可する場合は、NULL ON EMPTY句をERROR ON ERROR句と一緒に使用します。例30-4に、これを示します。

次のいずれかの形式を使用してjson_valueに対して関数ベースの索引を作成することをお薦めします。どちらの場合も、指定されたJSONデータ型のスカラーの結果となるドット表記法およびjson_value問合せの両方で、索引を使用できます。

• 項目メソッドが索引付けされる値に適用されるドット表記法構文。索引付けされる値は、項目メソッドで指定されたデータ型のスカラーのみです。

• RETURNINGのデータ型を指定するjson_value式。必要に応じて、ERROR ON ERRORおよびNULL ON EMPTYを使用できます。索引付けされる値は、RETURNING句で指定されたデータ型のスカラーのみです。

このように、これらいずれかの方法で作成された索引は、ドット表記法問合せとjson_value問合せのどちらとも一緒に使用できます。

関連項目:

Oracle Database SQL言語リファレンスのCREATE INDEX

例30-2 JSONフィールドに対するファンクション・ベースの索引の作成: ドット表記法

項目メソッドnumber()を使用すると、数値型の索引が作成されます。ドット表記法を使用して関数ベースの索引を作成する場合は、対象データに項目メソッドを常に適用します。

CREATE UNIQUE INDEX po_num_idx1 ON j_purchaseorder po
  (po.data.PONumber.number());

ノート:

デフォルトでは、関数ベースの索引にはNULL値は含まれません。索引で使用されるjson_value式がNULLを返す場合、デフォルトでは、一致するドキュメントの取得時に索引は使用されません。これは、NULLがフィルタ述語として使用されている場合(たとえば、json_value ... IS NULL)、またはORDER BY句でjson_valueが使用されている場合、デフォルトで関数ベースの索引が使用されないことを意味します。

NULL値を索引付けしてORDER BY句でjson_valueを使用できるようにするには、索引作成文に定数値(任意の値)を追加する必要があります。

CREATE INDEX po_num_idx1 ON j_purchaseorder po
  (po.data.PONumber.number(), 42);

ただし、索引のサイズは大きくなります。

例30-3 JSONフィールドに対するファンクション・ベースの索引の作成: JSON_VALUE

項目メソッドnumber()を使用すると、数値型の索引が作成されます。かわりに、RETURNING NUMBER句を使用することもできます。

CREATE UNIQUE INDEX po_num_idx2 ON j_purchaseorder
  (json_value(data, '$.PONumber.number()' 
              ERROR ON ERROR));

例30-4 JSON_VALUEファンクション・ベースの索引に対するNULL ON EMPTYの指定

RETURNING VARCHAR2(200)句を指定すると、索引は最大長200文字のSQL文字列になります。かわりにパス式に項目メソッドstring()を使用できますが、その場合は、デフォルトの戻り型VARCHAR2(4000)が使用されます。

句NULL ON EMPTYが指定されているため、索引po_ref_idx1は、ReferenceフィールドがないJSON文書にも索引を付けることができます。

CREATE UNIQUE INDEX po_ref_idx1 ON j_purchaseorder
  (json_value(data, '$.Reference'
              RETURNING VARCHAR2(200) ERROR ON ERROR
              NULL ON EMPTY));