22 全文検索問合せ
SELECT
文のCASE
式またはWHERE
句でOracle SQL条件json_textcontains
を使用して、JSONデータの全文検索を実行できます。PL/SQLプロシージャCTX_QUERY.result_set
を使用して、JSONデータに対してファセット検索を実行できます。
- Oracle SQL条件JSON_TEXTCONTAINS
SELECT
文のCASE
式またはWHERE
句でOracle SQL条件json_textcontains
を使用して、JSONデータの全文検索を実行できます。 - PL/SQLプロシージャCTX_QUERY.RESULT_SETを使用したJSONファセット検索
JSON検索索引を作成している場合、PL/SQLプロシージャCTX_QUERY.result_set
を使用して、JSONデータに対してファセット検索を実行することもできます。この検索は、たとえばSQLファンクションcontains
で複数の個別問合せを使用するのではなく、様々な種類の検索ヒットをすべて一度に生成するように最適化されています。
親トピック: JSONデータの問合せ
22.1 Oracle SQL条件JSON_TEXTCONTAINS
SELECT
文のCASE
式またはWHERE
句でOracle SQL条件json_textcontains
を使用して、JSONデータの全文検索を実行できます。
条件json_textcontains
は、Oracle Textテクノロジに基づいています。この条件は、後者がパラメータINPATH
を使用する場合、SQLファンクションcontains
のように動作します。json_textcontains
の検索パターン引数の構文は、SQLファンクションcontains
の構文と同じです。たとえば、これは、他のテキスト付近にあるテキストを問い合せたり、問合せであいまいパターン一致を使用できるということです。検索パターン引数に、Oracle Textの検索で予約されている文字または単語が含まれる場合は、その文字または単語をエスケープする必要があります。
条件json_textcontains
を使用するには、最初に次のいずれかを行う必要があります。そうしないと、json_textcontains
を使用したときにエラーが発生します。(両方を実行することはできません。これを試みると、エラーが発生します。)
-
JSON列のJSON検索索引を作成します。
-
問合せ対象のJSONデータの列をインメモリー列ストア(IM列ストア)に格納し、キーワード
TEXT
を指定します。列のデータ型はJSON
である必要があり、そうしないとエラーが発生します。(JSON
型は、データベース初期化パラメータcompatible
が少なくとも20
である場合にのみ使用できます。)
ノート:
JSON検索索引では、デフォルトで、大文字と小文字を区別しない検索がサポートされています。大文字と小文字を区別した索引付けを使用可能または使用禁止にするには、索引の作成時にBASIC_LEXER
プリファレンスのmixed_case
属性を使用します。『Oracle Textリファレンス』のBASIC_LEXERを参照してください。
ノート:
Oracle SQLファンクションjson_textcontains
を使用すると、JSONデータの強力な全文検索を実行できます。単純な文字列のパターン一致のみが必要な場合は、かわりにパス式フィルタ条件をhas substring
、starts with
、like
、like_regex
またはeq_regex
のいずれかのパターン一致比較とともに使用できます。
例22-1は、明細項目の部品の説明にキーワードMagic
が含まれる発注書を検索する全文問合せを示しています。
関連項目:
-
Oracle SQL条件
json_textcontains
の詳細は、『Oracle Database SQL言語リファレンス』 -
Oracle Textの
contains
演算子の詳細は、Oracle TextリファレンスのOracle Text CONTAINS問合せ演算子を参照してください。 -
特殊文字を使用してドキュメントを索引付けするためのJSON検索索引の構成の詳細は、Oracle Textアプリケーション開発者ガイドの特殊文字を参照してください。
-
SQLファンクション
contains
の検索パターン(したがって、json_textcontains
の検索パターン)での特殊文字の使用の詳細は、Oracle TextリファレンスのOracle Text問合せの特殊文字を参照してください。 -
Oracle Text検索に関して予約されている単語および文字の詳細は、Oracle Textリファレンスの予約語を参照してください。また、それらのエスケープ方法は、Oracle Textリファレンスのエスケープ文字を参照してください。
例22-1 JSON_TEXTCONTAINSを使用したJSONデータの全文問合せ
SELECT po_document FROM j_purchaseorder
WHERE json_textcontains(po_document,
'$.LineItems.Part.Description',
'Magic');
22.2 PL/SQLプロシージャCTX_QUERY.RESULT_SETを使用したJSONファセット検索
JSON検索索引を作成している場合、PL/SQLプロシージャCTX_QUERY.result_set
を使用して、JSONデータに対してファセット検索を実行することもできます。この検索は、たとえばSQLファンクションcontains
で複数の個別問合せを使用するのではなく、様々な種類の検索ヒットをすべて一度に生成するように最適化されています。
プロシージャCTX_QUERY.result_set
を使用して検索するには、結果セット・ディスクリプタ(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}} ]