27 JSONデータの索引

関数ベースの索引を使用すると、JSONデータのスカラー値を索引付けできます。また、JSON検索索引を定義できます。これは、非定型の構造的問合せと全文問合せの両方に便利です。

• JSONデータの索引付けの概要
  非定型の構造的問合せおよび全文問合せ用に、JSON検索索引を使用して通常の方法でJSONデータを索引付けできます。関数ベースの索引を使用して、JSONデータ内の特定のスカラー値を索引付けすることもできます。
• JSONデータに対して関数ベースの索引が選択されるかどうかを確認する方法
  特定の問合せで特定の関数ベースの索引が選択されるかどうかを確認するには、問合せの実行計画内で索引名を検索します。
• JSON_VALUEのためのビットマップ索引の作成
  SQL/JSONファンクションjson_valueのためにビットマップ索引を作成できます。ビットマップ索引は、問合せが小さいセットのJSON値のみを対象にしている場合に適しています。
• JSON_VALUEのためのBツリー索引の作成
  SQL/JSONファンクションjson_valueのために、Bツリーの関数ベースの索引を作成できます。これを行うには、標準構文を使用してjson_valueを明示的に指定するか、項目メソッドを持つドット表記法構文を使用できます。これらいずれかの方法で作成された索引は、ドット表記法問合せとjson_value問合せのどちらとも一緒に使用できます。
• JSON_VALUE関数ベースの索引とJSON_TABLE問合せの使用
  ERROR ON ERRORを指定したjson_valueを使用して作成された索引は、json_tableを含む問合せに使用できます。この場合、この索引が索引付きパスに対する制約として機能することにより、JSONコレクション内の各項目に対して1つの(非nullの)スカラーのJSON値のみが確実に投影されるようになります。
• JSON_VALUE関数ベースの索引とJSON_EXISTS問合せの使用
  ERROR ON ERRORを指定したSQL/JSONファンクションjson_valueを使用して作成された索引は、SQL/JSON条件json_existsを含む問合せに使用できます。
• JSON_VALUEの索引付けおよび問合せに関するデータ型の考慮事項
  SQL/JSONファンクションjson_valueを使用して作成されたファンクション・ベースの索引では、問合せでjson_valueによって返されたデータ型が索引で指定された型と一致する必要があります。
• コンポジットBツリー索引を使用した複数のJSONフィールドの索引付け
  JSONオブジェクトの複数のフィールドを索引付けするには、SQL/JSONファンクションjson_valueまたはドット表記法構文に複数のパス式を使用して、コンポジットBツリー索引を作成します。
• 非定型の問合せおよび全文検索のためのJSON検索索引
  JSON検索索引は、一般的な索引です。この検索索引によって、(1)非定型の構造的問合せ、つまり、定期的には予測または使用されない可能性のある問合せと、(2)全文検索の両方のパフォーマンスを向上させることができます。これは、JSONデータでの使用に特化して設計されたOracle Text索引です。

27.1 JSONデータの索引付けの概要

非定型の構造的問合せおよび全文問合せ用に、JSON検索索引を使用して通常の方法でJSONデータを索引付けできます。関数ベースの索引を使用して、JSONデータ内の特定のスカラー値を索引付けすることもできます。

SQL/JSONファンクションjson_valueのためにBツリー索引またはビットマップ索引を使用できます。関数で想定される値の数が少ない場合は、常にビットマップ索引が適しています。たとえば、対象となる値が少数であると予想される場合は、json_valueのためにビットマップ索引を使用できます。

この場合も、このような関数ベースの索引付けは、特定の関数を対象とする問合せに適していますが、これは、SQL/JSONファンクションのコンテキストでは特定のSQL/JSONパス式を意味します。これは、非定型(つまり、任意)の問合せの場合はあまり役に立ちません。関数ベースの索引を定義するのは、特定のパス式を何度も問い合せることがわかっている場合にしてください。

述語を含むSQL/JSONパス式は、関数ベースの索引が選択される問合せに使用できますが、関数ベースの索引を定義するために使用するパス式には、述語を含めることはできません。そうしないと、索引の作成時にエラーが発生します。

非定型方式で問い合せる場合は、JSON検索索引を定義してください。これは、一般的索引であり、特定のパス式の対象ではありません。これは、構造問合せ(特定の値を持つJSONフィールドを検索する場合など)や、Oracle SQL条件json_textcontainsを使用した全文問合せ(様々な文字列値から特定の単語を検索する場合など)に適しています。

同じJSON列に対して関数ベースの索引とJSON検索索引を定義することもできます。

JSON検索索引は、JSONデータでの使用に特化して設計されたOracle Text (全文)索引です。

注意:

データベース・キャラクタ・セットとしてAL32UTF8を使用することをお薦めします。索引の作成または適用時に、キャラクタ・セットの自動変換が行われる場合があります。このような変換では情報が失われる可能性があります。これは、問合せで戻されることが予測されるデータが、戻されない場合があることを意味します。JSONデータのキャラクタ・セットおよび文字エンコーディングを参照してください。

27.2 JSONデータに対して関数ベースの索引が選択されるかどうかを確認する方法

特定の問合せで特定の関数ベースの索引が選択されるかどうかを確認するには、問合せの実行計画内で索引名を検索します。

たとえば、例27-3で定義された索引の場合は、例27-5、例27-6、例27-7、例27-8および例27-10の各問合せの実行計画が、索引po_num_id1を使用した索引スキャンを参照しています。

27.3 JSON_VALUEのためのビットマップ索引の作成

SQL/JSONファンクションjson_valueに対してビットマップ索引を作成できます。ビットマップ索引は、問合せが小さいセットのJSON値のみを対象にしている場合に適しています。

例27-1 JSON_VALUEのビットマップ索引の作成

データ内のフィールドCostCenterに対して想定される値が少ない場合、これは作成に適した索引です。

CREATE BITMAP INDEX cost_ctr_idx ON j_purchaseorder
  (json_value(po_document, '$.CostCenter'));

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

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

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

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

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

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

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

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

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

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

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

例27-2 JSONフィールドに対する関数ベースの索引の作成: ドット表記法

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

CREATE UNIQUE INDEX po_num_idx2 ON j_purchaseorder po
  (po.po_document.PONumber.number());

例27-3 JSONフィールドに対する関数ベースの索引の作成: JSON_VALUE

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

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

例27-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(po_document, '$.Reference'
              RETURNING VARCHAR2(200) ERROR ON ERROR
              NULL ON EMPTY));

27.5 JSON_VALUE関数ベースの索引とJSON_TABLE問合せの使用

ERROR ON ERRORを指定したjson_valueを使用して作成された索引は、json_tableを含む問合せに使用できます。この場合、この索引が索引付きパスに対する制約として機能することにより、JSONコレクション内の各項目に対して1つの(非nullの)スカラーのJSON値のみが確実に投影されるようになります。

この方法で索引を使用する場合は、次の各条件が満たされている必要があります。

• 問合せのWHERE句は、json_tableによって投影される列を参照する。

• その列のデータ型は、索引定義に使用されたデータ型と一致する。

• その列を対象とする有効なSQL/JSONパスが、索引付けされたパス式と一致する。

したがって、例27-5の問合せでは、例27-3で作成された索引を使用しています。

注意:

json_value式またはドット表記法を使用して作成された関数ベースの索引を、問合せでWHERE句が出現するたびに取得できるのは、この発生がSQLの比較条件(>=など)で使用されている場合のみです。特に、これは、条件IS NULLまたはIS NOT NULLで使用される存在としては選択されません。

SQL比較条件の詳細は、Oracle Database SQL言語リファレンスを参照してください。

例27-5 JSON_VALUE関数ベースの索引とJSON_TABLE問合せの使用

列のSQL型NUMBER(5)が索引に使用されている型と一致するため、索引を選択できます。

SELECT jt.*
  FROM j_purchaseorder po,
       json_table(po.po_document, '$'
         COLUMNS po_number  NUMBER(5) PATH '$.PONumber',
                 reference  VARCHAR2(30 CHAR) PATH '$.Reference',
                 requestor  VARCHAR2(32 CHAR) PATH '$.Requestor',
                 userid     VARCHAR2(10 CHAR) PATH '$.User',
                 costcenter VARCHAR2(16 CHAR) PATH '$.CostCenter') jt
  WHERE po_number = 1600;

27.6 JSON_VALUE関数ベースの索引とJSON_EXISTS問合せの使用

ERROR ON ERRORを指定したSQL/JSONファンクションjson_valueを使用して作成された索引は、SQL/JSON条件json_existsを含む問合せに使用できます。

問合せの比較のいずれかでjson_value関数ベースの索引が選択されるためには、その比較の型が、索引のSQL戻りデータ型と同じである必要があります。使用されるSQLデータ型は、double()、float()、number()、string()、timestamp()、date()、dateWithTime()、dsInterval()およびymInterval()の項目メソッドで言及されたデータ型です(SQL/JSONパス式の項目メソッドを参照)。

たとえば、索引で数値が戻される場合は、比較の型も数値である必要があります。問合せのフィルタ式に、json_value索引と一致する複数の比較が含まれる場合は、これらの索引の1つがオプティマイザによって選択されます。

比較の型は、次のように決まります。

1. 2つの比較語(比較の両側)のSQLデータ型が異なる場合、比較の型は不明になり、索引は選択されません。両方の型が同じ場合は、その型が比較の型になります。

2. 1つの比較語のSQLデータ型が文字列(テキスト・リテラル)である場合、比較の型は、もう一方の比較語の型になります。

3. 1つの比較語が、項目メソッドによってSQLの一致型が強制される関数ステップを持つパス式の場合、その型も比較語の型になります。SQLの一致型が強制される項目メソッドは、double()、float()、number()、string()、timestamp()、date()、dateWithTime(), dsInterval()およびymInterval()です。

4. 1つの比較語が、前述のような関数ステップを持たないパス式の場合、その型はSQL文字列(テキスト・リテラル)になります。

例27-3では、フィールドPONumberのjson_valueに対するファンクションベースの索引を作成します。この索引は、NUMBER値に索引付けします。

例27-6、例27-7および例27-8のそれぞれの問合せでは、json_exists条件を評価するときにこの索引を使用できます。これらの各問合せで、絶対パス式$.PONumberに相対的な単純なパス式に関連する比較が使用されます。それぞれの場合の単純な相対パス式は、カレント・フィルタ項目(@)を対象としますが、例27-8の場合は、照合データをSQLデータ型NUMBERに変換(キャスト)します。

例27-6 リテラル数値と比較したフィールドを対象としたJSON_EXISTS問合せ

この問合せでは、次の理由から索引が利用されます。

1. 1つの比較語が、関数ステップを持たないパス式であるため、その型はSQL文字列(テキスト・リテラル)である。

2. 1つの比較語が文字列型であるため、比較の型はもう一方の語の型になり、その型が数値である(もう一方の語は数字)。

3. (唯一の)比較の型が、索引で戻される型(数値)と同じである。

SELECT count(*) FROM j_purchaseorder 
  WHERE json_exists(po_document, '$.PONumber?(@ > 1500)');

例27-7 変数値と比較したフィールドを対象としたJSON_EXISTS問合せ

この問合せでは、次の理由から索引を利用できます。

1. 1つの比較語が、関数ステップを持たないパス式であるため、その型はSQL文字列(テキスト・リテラル)である。

2. 1つの比較語が文字列型であるため、比較はもう一方の語の型を持つことになり、この型が数値である(もう一方の語は、数値にバインドされた変数)。

3. (唯一の)比較の型が、索引で戻される型(数値)と同じである。

SELECT count(*) FROM j_purchaseorder 
  WHERE json_exists(po_document, '$.PONumber?(@ > $d)'
                    PASSING 1500 AS "d");

例27-8 変数値と比較して数値にキャストされるフィールドを対象としたJSON_EXISTS問合せ

この問合せでは、次の理由から索引を利用できます。

1. 1つの比較語が、項目メソッド(number())によって照合データが数値に変換される関数ステップを持つパス式であるため、比較語の型はSQL数値である。

2. もう一方の比較語が、SQL型数値を持つ数値である。比較語の型が一致しているため、比較も同じ型である数値になる。

3. (唯一の)比較の型が、索引で戻される型(数値)と同じである。

SELECT count(*) FROM j_purchaseorder 
  WHERE json_exists(po_document, '$.PONumber?(@.number() > $d)'
                    PASSING 1500 AS "d");

例27-9 フィールド比較の論理積を対象としたJSON_EXISTS問合せ

例27-6と同様に、この問合せではフィールドPONumberの索引を利用できます。json_value索引がフィールドReferenceに対しても定義されている場合、オプティマイザがこの問合せに対してどちらの索引を使用するかを選択します。

SELECT count(*) FROM j_purchaseorder
  WHERE json_exists(po_document, '$?(@.PONumber > 1500
                                     && @.Reference == "ABULL-20140421")');

27.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値を想定する問合せに対してこの索引は選択されません。

たとえば、例27-10の問合せの場合、json_valueでRETURNING NUMBERが使用されています。この問合せには例27-3で作成された索引を選択できますが、これは、索引が付けられたjson_value式で戻り型としてNUMBERが指定されているためです。索引にキーワードRETURNING NUMBERが指定されていない場合、指定される戻り型はVARCHAR2(4000) (デフォルト)になり、そのような問合せに対して索引は選択されません。

同様に、例27-2で作成された索引は、項目メソッドnumber() (戻り型NUMBERが強制されます)を使用しているので、この問合せに対して選択される可能性があります。

ここで、戻される値の型がVARCHAR2になるようにRETURNING句なしでjson_valueを使用している例27-11と例27-12の問合せについて検討してみます。

例27-11では、SQL関数to_numberにより、json_valueによって戻されるVARCHAR2値を数値に明示的に変換しています。同様に、例27-12では、比較条件> (より大きい)により、値を数値に暗黙的に変換しています。

例27-3と例27-2の索引のいずれも、これらの問合せに対して選択されません。これらの問合せでは、いずれの事例でも型キャストのために正しい結果が戻される可能性がありますが、これらの索引を使用してこれらの問合せを評価することはできません。

また、一部のデータを特定のデータ型に変換できない場合に何が起きるかについても検討してください。たとえば、例27-10、例27-11および例27-12の問合せが実行された場合、"alpha"などのPONumber値はどうなるでしょうか。

例27-11および例27-12の場合、値を数値にキャストしようとするために問合せはエラーで停止します。ただし、例27-10の場合、デフォルトのエラー処理動作がNULL ON ERRORであるため、非数値"alpha"は単純にフィルタで除外されます。値には索引が付けられますが、問合せに対しては無視されます。

同様に、たとえばDEFAULT '1000' ON ERRORが問合せで使用されたとすると、つまり、数値のデフォルト値が指定されたとすると、値"alpha"に対してエラーは発生しません。この場合、デフォルト値の1000が使用されます。

注意:

指定された問合せに対してSQL/JSONファンクションjson_valueに基づくファンクション・ベースの索引が選択されるためには、索引と問合せの両方で同じ戻りデータ型および処理方法(エラー、空、不一致)を使用する必要があります。

つまり、問合せの戻り型または処理方法を変更して、索引に指定されたものと一致しないようにする場合は、問合せパターンに依存する永続オブジェクトを再構築する必要があります。(同じことが、マテリアライズド・ビュー、パーティション、チェック制約およびそのパターンに依存するPL/SQLサブプログラムにも当てはまります。)

例27-10 JSON_VALUE問合せと明示的なRETURNING NUMBER

SELECT count(*) FROM j_purchaseorder po
  WHERE json_value(po_document, '$.PONumber' RETURNING NUMBER) > 1500;

例27-11 JSON_VALUE問合せと明示的な数値変換

SELECT count(*) FROM j_purchaseorder po
  WHERE to_number(json_value(po_document, '$.PONumber')) > 1500;

例27-12 JSON_VALUE問合せと暗黙的な数値変換

SELECT count(*) FROM j_purchaseorder po
  WHERE json_value(po_document, '$.PONumber') > 1500;

27.8 コンポジットBツリー索引を使用した複数のJSONフィールドの索引付け

JSONオブジェクトの複数のフィールドを索引付けするには、SQL/JSONファンクションjson_valueまたはドット表記法構文に複数のパス式を使用して、コンポジットBツリー索引を作成します。

例27-13に、これを示します。対応するJSONデータ(オブジェクト・フィールド)を参照するSQL問合せでは、コンポジット索引が選択されます。例27-14に、これを示します。

または、索引付けするJSONオブジェクトのフィールドの仮想列を作成し、それらの仮想列にコンポジットBツリー索引を作成できます。その場合、仮想列または対応するJSONデータ(オブジェクト・フィールド)を参照するSQL問合せでは、コンポジット索引が選択されます。問合せパフォーマンスはどちらの場合も同じです。

問合せのパフォーマンスを向上させるために実装された索引に、データが論理的に依存することはありません。この実装からの独立をコードに反映するには、(仮想列ではなく)データを直接問い合せます。このようにすることにより、問合せは索引の有無とは関係なく同様に機能します。この場合、索引はパフォーマンスの向上に特化して機能します。

例27-13 JSONオブジェクト・フィールドのコンポジットBツリー索引の作成

CREATE INDEX user_cost_ctr_idx on j_purchaseorder(json_value(po_document, '$.User' RETURNING VARCHAR2(20),
                                                  json_value(po_document, '$.CostCenter' RETURNING VARCHAR2(6)));

例27-14 コンポジット索引を使用して索引付けされたJSONデータの問合せ

SELECT po_document FROM j_purchaseorder
  WHERE json_value(po_document, '$.User')       = 'ABULL'
    AND json_value(po_document, '$.CostCenter') = 'A50';

27.9 非定型の問合せおよび全文検索のためのJSON検索索引

JSON検索索引は、一般的な索引です。この検索索引によって、(1)非定型の構造的問合せ、つまり、定期的には予測または使用されない可能性のある問合せと、(2)全文検索の両方のパフォーマンスを向上させることができます。これは、JSONデータでの使用に特化して設計されたOracle Text索引です。

注意:

JSON型として格納されているJSONデータの場合、JSON検索索引を作成して保守することの代替の方法としては、インメモリー列ストア(IM列ストア)にJSON列を移入する方法があります。

非定型問合せをサポートするには、キーワードTEXTを使用せずに、列またはその表をIM列ストアに配置します。全文検索をサポートするには、キーワードTEXTを使用して、列をIM列ストアに配置します。両方の種類の問合せをサポートするには、両方を行うことができます。(JSON型は、データベース初期化パラメータcompatibleが少なくとも20である場合にのみ使用できます。)

JSON型の列をIM列ストアに配置し、それにJSON検索索引を定義した場合、非定型問合せと全文問合せの両方で検索索引のみが使用されます。検索索引はIM列ストアの使用よりも常に優先されます。

注意:

JSON検索索引を作成してある場合、PL/SQLプロシージャCTX_QUERY.result_setを使用して、索引付けされたJSONデータの別の種類の全文検索(ファセット検索)を実行することもできます。

CTX_QUERY検索は、たとえばSQLファンクションcontainsにより複数の個別問合せを使用するのではなく、様々な種類の検索ヒットをすべて一度に生成するように最適化されています。

これらのプロシージャのいずれかを使用して検索するには、結果セット・ディスクリプタ(RSD)を渡します。これにより、(事前定義済演算子フィールド$query、$searchおよび$facetを含むJSONオブジェクトとして)索引付けされたJSONデータから検索するJSON値と、そのグループ化または集計方法が指定されます。取得および処理できる値は、JSONスカラーまたはJSONのスカラーの配列のいずれかです。

(演算子フィールド$queryは、SODA query-by-example (QBE)問合せでも使用されます。演算子$containsを、フィールド$queryの値内に、Oracle SQL条件json_textcontainsで提供されるのと同様の全文照合のために使用できます。)

RSDフィールドは、出力結果セットに含める内容を指定する順序付きテンプレートとして機能します。(検出されたJSONデータに加えて、結果セットには通常、検索ヒットしたrowidといくつかのカウントのリストが含まれています。)

$facetフィールド値はファセット・オブジェクトのJSON配列です。それぞれのオブジェクトが、特定のパスにあるJSONデータを定義し、場合によっては一部の条件を満たし、そのデータに適用する集計操作を定義しています。

ファセット・データは、演算子$count、$min、$max、$avgおよび$sumを使用して集計できます。たとえば、$sumは、ターゲット・データ値の合計を返します。集計演算子は、パスによって対象指定されたすべてのスカラー値に適用するか、または様々な値範囲で定義されたそのような値のバケットに個別に適用できます。

最後に、演算子$uniqueCountを使用して、指定のパスでの個別値の出現回数を取得できます。

たとえば、次の$facet値について考えてみます。

[{"$uniqueCount" : "zebra.name"},
 {"$sum"         : {"path"  : "zebra.price",                   
                    "bucket : [{"$lt"  : 3000},
                               {"$gte" : 3000}]},
 {"$avg"         : "zebra.rating"}]

問合せ結果が返されると、出力内のフィールド$facetの値は3つのオブジェクトの配列であり、次のフィールドが含まれています。

• zebra.name — 各zebra名の出現回数。

• zebra.price — 2つのバケット(3000未満の価格と3000以上の価格)で表したzebra価格の合計。

• zebra.rating — すべてのzebra評価の平均。(評価のないzebraは無視されます。)

[{"zebra.name"   : [{"value" : "Zigs",   "$uniqueCount : 2},                  
                    {"value" : "Zigzag", "$uniqueCount : 1},             
                    {"value" : "Storm",  "$uniqueCount : 1}]},
 {"zebra.price"  : [{"value" : 1000, "$uniqueCount : 2},                  
                    {"value" : 3000, "$uniqueCount : 2},                  
                    {"value" : 2000, "$uniqueCount : 1}]},
 {"zebra.rating" : {"$avg" : 4.66666666666666666667}}]

注意:

Oracle Database 12cリリース1 (12.1.0.2)を使用してJSON検索索引を作成した場合は、その索引を削除し、以降のリリースで使用するために、ここで説明するCREATE SEARCH INDEXを使用して検索索引を新しく作成することをお薦めします。

CREATE SEARCH INDEXにキーワードFOR JSONを指定して、JSON検索索引を作成します。例27-15に、これを示します。

JSON検索索引を作成する列は、JSON、VARCHAR2、CLOBまたはBLOBデータ型の列です。整形式のJSONデータのみが含まれていることが認識されている必要があります(つまり、JSON型であるか、is jsonチェック制約がある)。列にJSONデータが含まれていることを認識できない場合、CREATE SEARCH INDEXでエラーが発生します。

問合せの実行計画内にJSON検索索引の名前が存在する場合、索引が実際に問合せに対して選択されたことがわかります。例27-17に示すものと似た行が表示されます。

特定の構成可能なオプションのデフォルト設定をオーバーライドする場合は、検索索引の作成時にPARAMETERS句を指定できます。デフォルト(PARAMETERS句なし)では、索引はコミット時に同期化され、テキストと数値の範囲の両方が索引付けされます。

JSON検索索引は非同期にメンテナンスされます。同期されるまで、この索引は変更されたデータまたは新しく挿入されたデータに使用されません。索引を使用すると問合せのパフォーマンスが向上しますが、データと同期する処理の実行中はパフォーマンスに悪影響します。特に、DML操作に対して悪影響することがあります。

JSON検索索引を同期するには、基本的に3つの方法があります。通常、それぞれの方法は異なるユースケースに適しています。

• コミット時に同期。

  これは、コミットが頻繁に行われず、コミットされた変更を他の操作(問合せなど)がすぐに参照できることが重要である場合に適しています。(索引が失効していると、コミットされていない変更を参照できないことがあります。)例27-15では、コミット時に同期される検索索引を作成します。

• 一定の間隔で定期的に同期。

  高速で信頼性の高いトランザクション処理で高いスループットが求められるオンライン・トランザクション処理(OLTP)アプリケーションでは、通常、各操作をコミットするため、定期的に索引を同期することが適しています。この場合、通常、同期の間隔は各コミットの間の時間より長くします。各コミットの結果が他の操作によってすぐに参照可能になることは重要ではありません。例27-16では、毎秒同期される検索索引を作成します。

• オンデマンドで同期(たとえば、データベースの負荷が減少したとき)。

  通常、これは頻繁に実行しません。コミット時の同期または間隔での同期よりも少ない頻度で、索引が同期されます。DMLのパフォーマンスが特に重要な場合は、この方法が一般的に適しています。

JSON検索索引を作成するには、CTXAPP権限が必要となります。索引をコミット時に同期するのではなく、同期の間隔を使用して索引を作成する場合は、CREATE JOB権限も必要となります。

JSON検索索引を利用する問合せで数値の範囲が使用されない場合は、パラメータSEARCH_ONにTEXTを指定すると、メンテナンスの時間とディスク領域をいくらか節約できます。SEARCH_ONのデフォルト値はTEXT_VALUEです。これは、テキストと同様に索引の数値の範囲も意味します。

注意:

JSON検索索引j_s_idxを変更するには、ALTER INDEX j_s_idx REBUILD ...(ALTER SEARCH INDEX j_s_idx ...ではない)を使用します。

例27-15 コミット時に同期されるJSON検索索引の作成

コミット時の同期はデフォルトの動作ですが、PARAMETERS ('SYNC (ON COMMIT)'を使用して明示的に指定できます。

CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document)
  FOR JSON;

例27-16 毎秒同期されるJSON検索索引の作成

CREATE SEARCH INDEX po_search_1_sec_idx ON j_purchaseorder (po_document)
  FOR JSON
  PARAMETERS('SYNC (EVERY "FREQ=SECONDLY; INTERVAL=1")

例27-17 JSON検索索引が使用されていることを示す実行計画

|* 2|   DOMAIN INDEX     | PO_SEARCH_IDX |     |     |     4 (0)

JSONデータの非定型問合せ

例27-18は、例27-15で作成したJSON検索索引も使用するJSONデータの非全文問合せを示しています。

例27-18 いくつかの非定型JSON問合せ

この問合せでは、船積み依頼書の住所に国が含まれる文書を選択します。

SELECT po_document FROM j_purchaseorder
  WHERE json_exists(po_document, '$.ShippingInstructions.Address.country');

この問合せでは、ユーザーAKHOOが含まれる文書で、注文された項目が8個より多いものを選択します。ここでは、数値範囲の索引付けが利用されます。

SELECT po_document FROM j_purchaseorder
  WHERE json_exists(po_document, '$?(@.User == "AKHOO"
                                     && @.LineItems.Quantity > 8)');

この問合せでは、ユーザーがAKHOOの文書が選択されます。ここでは、WHERE句で、json_existsのかわりにjson_valueが使用されます。

SELECT po_document FROM j_purchaseorder
  WHERE json_value(po_document, '$.User') = 'AKHOO';

関連項目:

• CREATE SEARCH INDEXのPARAMETERS句の詳細は、Oracle Textリファレンスを参照してください。

• ALTER INDEX ... REBUILDのPARAMETERS句の詳細は、Oracle Textリファレンスを参照してください。

• JSON検索索引の同期化の詳細は、Oracle TextリファレンスのCREATE INDEXを参照してください。

• JSON検索索引のパフォーマンスの最適化および調整の詳細は、Oracle Textアプリケーション開発者ガイドを参照してください。