24 JSONデータの索引

JSONデータには、これを格納するために使用する任意のデータ型の場合と同じように索引を付けることができます。また、JSON検索索引を定義できます。これは、非定型の構造的問合せと全文問合せの両方に便利です。

• JSONデータの索引付けの概要
  JSONデータ専用のSQLデータ型はないため、通常の方法でJSONデータに索引を付けられます。さらに、通常と同じ方法で、JSON検索索引を使用して非定型の構造的問合せおよび全文問合せ用に索引付けを行えます。
• JSONデータに対して関数ベースの索引が選択されるかどうかを確認する方法
  特定の問合せで特定の関数ベースの索引が選択されるかどうかを確認するには、問合せの実行計画内で索引名を検索します。
• SQL/JSON条件JSON_EXISTSのビットマップ索引の作成
  json_existsで戻される値に対して、ビットマップ索引を作成できます。条件に対して想定される戻り値が2つ(trueおよびfalse)のみであるため、これはjson_existsでの使用に適した索引です。
• JSON_VALUE関数ベースの索引の作成
  SQL/JSONファンクションjson_valueに対して関数ベースの索引を作成できます。これには標準の構文を使用(json_value)するか、単純なドット表記法構文を使用できます。これらいずれかの方法で作成された索引は、ドット表記法問合せとjson_value問合せのどちらとも一緒に使用できます。
• JSON_VALUE関数ベースの索引とJSON_TABLE問合せの使用
  json_tableによって投影された列をWHERE句が参照し、この列を対象とする有効なSQL/JSONパスが索引付きパス式と一致する場合、json_tableが関連する問合せに対して、ERROR ON ERRORが設定されたjson_valueを使用して作成された索引を使用できます。
• JSON_VALUE関数ベースの索引とJSON_EXISTS問合せの使用
  ERROR ON ERRORが設定されたSQL/JSONファンクションjson_valueを使用して作成された索引は、SQL/JSON条件json_existsが関連する問合せに対して使用できます。ただし、問合せのパス式に含まれるフィルタ式に、パス式の比較またはこのような比較が複数個&&で区切られたものだけが含まれる場合に限られます。
• JSON_VALUEの索引付けおよび問合せに関するデータ型の考慮事項
  デフォルトでは、SQL/JSONファンクションjson_valueはVARCHAR2値を戻します。json_valueを使用して関数ベースの索引を作成する場合、RETURNING句を使用して異なる戻りデータ型を指定しないかぎり、非VARCHAR2値を想定する問合せに対してこの索引は選択されません。
• コンポジットBツリー索引を使用した複数のJSONフィールドの索引付け
  JSONオブジェクトの複数のフィールドに索引を付けるには、最初にこれらの仮想列を作成します。次に、仮想列に対してコンポジットBツリー索引を作成します。
• JSON検索索引: 非定型の問合せおよび全文検索
  JSON検索索引は、一般的な索引です。この検索索引によって、(1)非定型の構造的問合せ、つまり、定期的には予測または使用されない可能性のある問合せと、(2)全文検索の両方のパフォーマンスを向上させることができます。これは、JSONデータでの使用に特化して設計されたOracle Text索引です。

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

JSONデータ専用のSQLデータ型はないため、通常の方法でJSONデータに索引を付けられます。さらに、通常と同じ方法で、JSON検索索引を使用して非定型の構造的問合せおよび全文問合せ用に索引付けを行えます。

JSONデータには、これを格納するために使用する任意のデータ型の場合と同じように索引を付けることができます。特に、SQL/JSONファンクションjson_valueにBツリー索引やビットマップ索引を、SQL/JSON条件is json、is not jsonおよびjson_existsにビットマップ索引を使用できます。

(より一般的には、想定される関数の数が小さい場合は常にビットマップ索引が適しています。たとえば、値がブールであるか少数の文字列値であると想定される場合、関数json_valueにビットマップ索引を使用できます。)

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

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

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

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

注意:

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

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

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

たとえば、例24-4で定義されている索引が指定された場合、例11-1のjson_value問合せの実行計画では索引po_num_id1を使用して索引スキャンが参照されます。

24.3 SQL/JSON条件JSON_EXISTSのビットマップ索引の作成

json_existsで戻される値に対して、ビットマップ索引を作成できます。条件に対して想定される戻り値が2つ(trueおよびfalse)のみであるため、これはjson_existsでの使用に適した索引です。

これを、例24-1に示します。

例24-2では、json_valueによって戻される値のビットマップ索引を作成しています。データ内のフィールドCostCenterに対して想定される値が少ない場合、これは使用に適した索引です。

例24-1 JSON_EXISTSのビットマップ索引の作成

CREATE BITMAP INDEX has_zipcode_idx
  ON j_purchaseorder (json_exists(po_document, 
                                  '$.ShippingInstructions.Address.zipCode'));

例24-2 JSON_VALUEのビットマップ索引の作成

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

24.4 JSON_VALUE関数ベースの索引の作成

SQL/JSONファンクションjson_valueに対して関数ベースの索引を作成できます。これには標準の構文を使用(json_value)するか、単純なドット表記法構文を使用できます。これらいずれかの方法で作成された索引は、ドット表記法問合せとjson_value問合せのどちらとも一緒に使用できます。

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

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

代替方法として、JSONデータに対する単純なドット表記法アクセスで説明されている簡単な構文を使用して索引を作成する方法があります。例24-3はこれを示しています。ここでは、ドット表記法の問合せで戻すことが可能な内容に応じてスカラー結果と非スカラー結果の両方に索引を付けています。

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

例24-3の索引が問合せjson_valueに対して選択される場合、正しいフィールド値をテストするために、索引の選択後にフィルタリングが適用されます。この索引には非スカラー値を格納でき、ドット表記法の問合せではこのような値を戻すことができますが、json_valueの問合せではできないため、このような値は索引選択後にフィルタで除外されます。

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

次のいずれかの形式を使用してjson_valueに対して関数ベースの索引を作成することをお薦めします。

• ドット表記法構文

  索引が付けられる値は、可能な場合は常にJSON値を戻すドット表記法問合せの柔軟な動作に対応します。これには、非スカラーのJSON値(JSONオブジェクトおよび配列)が含まれる場合があります。これらは、json_valueの問合せに加えてドット表記法の問合せと照合できます。索引は、初期セットの一致結果を見つけ出すために使用され、これらの一致結果は問合せの詳細事項に応じてフィルタされます。たとえば、索引が付けられた値のうちJSONスカラーでないものはフィルタで除外されます。

• RETURNINGデータ型を指定し、ERROR ON ERROR(さらにオプションでNULL ON EMPTY)を使用するjson_value式。

  索引が付けられる値は、指定したデータ型の(非nullの)スカラー値のみです。それでもなお、索引は、このようなスカラー結果が生成されるドット表記法問合せで使用できます。

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

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

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

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

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

例24-5 JSON_VALUE関数ベースの索引に対するNULL ON EMPTYの指定

句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));

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

json_tableによって投影された列をWHERE句が参照し、この列を対象とする有効なSQL/JSONパスが索引付きパス式と一致する場合、json_tableが関連する問合せに対して、ERROR ON ERRORが設定されたjson_valueを使用して作成された索引を使用できます。

この索引が索引付きパスに対する制約として機能することにより、JSONコレクション内の各項目に対して1つの(非nullの)スカラーのJSON値のみが確実に投影されるようになります。

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

注意:

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

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

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

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;

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

ERROR ON ERRORが設定されたSQL/JSONファンクションjson_valueを使用して作成された索引は、SQL/JSON条件json_existsが関連する問合せに対して使用できます。ただし、問合せのパス式に含まれるフィルタ式に、パス式の比較またはこのような比較が複数個&&で区切られたものだけが含まれる場合に限られます。

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

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

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

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

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

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

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

例24-4では、フィールドPONumberのjson_valueに対する関数ベースの索引を作成します。索引の戻り型は、NUMBERです。

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

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

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

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

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

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

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

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

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

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

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

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

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

例24-9 変数値と比較して数値にキャストされるフィールドを対象とした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");

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

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

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

24.7 JSON_VALUEの索引付けおよび問合せに関するデータ型の考慮事項

デフォルトでは、SQL/JSONファンクションjson_valueはVARCHAR2値を戻します。json_valueを使用して関数ベースの索引を作成する場合、RETURNING句を使用して異なる戻りデータ型を指定しないかぎり、非VARCHAR2値を想定する問合せに対してこの索引は選択されません。

たとえば、例24-11の問合せの場合、json_valueでRETURNING NUMBERが使用されています。この問合せには例24-4で作成された索引を選択できますが、これは、索引が付けられたjson_value式で戻り型としてNUMBERが指定されているためです。

ただし、例24-3で作成された索引にはRETURNING NUMBER (デフォルトでは、戻り型はVARCHAR2(4000)です)が使用されていないため、このような問合せに対して選択することはできません。

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

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

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

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

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

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

例24-11 JSON_VALUE問合せと明示的なRETURNING NUMBER

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

例24-12 JSON_VALUE問合せと明示的な数値変換

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

例24-13 JSON_VALUE問合せと暗黙的な数値変換

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

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

JSONオブジェクトの複数のフィールドに索引を付けるには、最初にこれらの仮想列を作成します。次に、仮想列に対してコンポジットBツリー索引を作成します。

例24-14および例24-15に、これを示します。例24-14では、JSONオブジェクト・フィールドUserおよびCostCenterそれぞれに対して仮想列useridおよびcostcenterを作成しています。

例24-15では、例24-14の仮想列に対してコンポジットBツリー索引を作成しています。

仮想列または対応するJSONデータ(オブジェクト・フィールド)を参照するSQL問合せでは、コンポジット索引が選択されます。このことは、例24-16の両方の問合せに当てはまります。

これら2つの問合せの効果は同一であり、パフォーマンスも同一です。ただし、最初の問合せ形式はJSONデータ自体を対象としておらず、このデータに索引を付けるために使用される仮想列を対象としています。

問合せのパフォーマンスを向上させるために実装された索引にデータが論理的に依存することはありません。実装からのこのような独立性がコードに反映されるようにするには、2番目の問合せ形式を使用してください。このようにすることにより、問合せは索引の有無とは関係なく同じ機能を果たすようになります。この場合、索引はパフォーマンスの向上に特化して機能します。

例24-14 JSONオブジェクト・フィールドの仮想列の作成

ALTER TABLE j_purchaseorder ADD (userid VARCHAR2(20)
  GENERATED ALWAYS AS (json_value(po_document, '$.User' RETURNING VARCHAR2(20))));

ALTER TABLE j_purchaseorder ADD (costcenter VARCHAR2(6)
  GENERATED ALWAYS AS (json_value(po_document, '$.CostCenter'
                       RETURNING VARCHAR2(6))));

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

CREATE INDEX user_cost_ctr_idx on j_purchaseorder(userid, costcenter);

例24-16 コンポジット索引を使用して索引が付けられたJSONデータを問い合せる2通りの方法

SELECT po_document FROM j_purchaseorder WHERE userid      = 'ABULL'
                                          AND costcenter  = 'A50';

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

24.9 JSON検索索引: 非定型の問合せおよび全文検索

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

注意:

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

JSON検索索引の概要

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

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

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

JSON検索索引は、要求に応じて非同期にメンテナンスされます。したがって、索引メンテナンスのコストを遅延し、コミット時のみまたはデータベースの負荷が軽減されている特定の時期に実行できます。これにより、DMLのパフォーマンスを向上させることができます。また、索引の同期化を行う際に、非同期索引行のバルク・ロードを有効にすると、索引メンテナンスのパフォーマンスを向上させることができます。一方、索引の非同期メンテナンスとは、索引が同期化されるまでは、変更されたか新しく挿入されたデータに索引が使用されないことを意味します。

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

注意:

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

例24-17 JSON検索索引の作成

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

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

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

JSONデータの全文検索

SELECT文のCASE式またはWHERE句でSQL/JSON条件json_textcontainsを使用して、VARCHAR2、BLOBまたはCLOB列に格納されているJSONデータの全文検索を実行できます。

Oracle Textテクノロジは、SQL条件json_textcontainsに基づいています。たとえば、これは、他のテキスト付近にあるテキストを問い合せたり、問合せであいまいパターン一致を使用できるということです。

条件json_textcontainsを使用するためには、まず、JSON検索索引を作成する必要があります。そうしない場合、json_textcontainsを使用するとエラーが発生します。

例24-19は、明細項目の部品の説明にキーワードMagicが含まれる発注書を検索する全文問合せを示しています。

例24-19 JSONデータの全文問合せ

SELECT po_document FROM j_purchaseorder
  WHERE json_textcontains(po_document, '$.LineItems.Part.Description', 'Magic');

例24-20 検索パターンをエスケープした、JSONデータの全文問合せ

条件json_textcontainsの3つ目の引数に、Oracle Textの検索で予約されている文字または単語が含まれる場合は、その文字または単語をエスケープする必要があります。

ここに示す問合せでは、部品の説明にMagicとChristmasの両方が含まれる文書を検索します。これらの例では、Oracle Textの検索では予約されている、Oracle Textの問合せ演算子&とandが使用されています。

最初の問合せでは、単一の文字&をバックスラッシュ(\)を使用してエスケープしています。2つ目の問合せでは、予約語のandが含まれているため、検索パターン全体を中カッコ({、})を使用してエスケープしています。(演算子andに含まれる文字だけをエスケープするために、{and}を使用することも考えられます)。

SELECT po_document FROM j_purchaseorder
  WHERE json_textcontains(po_document, '$.LineItems.Part.Description', '{Magic and  Christmas}');

SELECT po_document FROM j_purchaseorder
  WHERE json_textcontains(po_document, '$.LineItems.Part.Description', 'Magic \& Christmas');

JSONデータの非定型問合せ

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

例24-21 いくつかの非定型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') = 'ABULL';

関連項目:

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

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

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

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

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

• Oracle Text検索で予約されている単語および文字の詳細は、Oracle Textリファレンスを参照してください。また、それらのエスケープ方法は、Oracle Textリファレンスを参照してください。