20 全文検索問合せ: ファンクションJSON_TEXTCONTAINS
SELECT
文のCASE
式またはWHERE
句でOracle SQL条件json_textcontains
を使用して、JSONデータの全文検索を実行できます。
条件json_textcontains
は、Oracle Textテクノロジに基づいています。その検索パターンの引数の構文は、SQLファンクションcontains
(INPATH
を使用)の構文と同様であり、その動作は同じです。たとえば、これは、他のテキスト付近にあるテキストを問い合せたり、問合せであいまいパターン一致を使用できるということです。検索パターン引数に、Oracle Textの検索で予約されている文字または単語が含まれる場合は、その文字または単語をエスケープする必要があります。
条件json_textcontains
を使用するには、最初に次のいずれかを行う必要があります。そうしないと、json_textcontains
を使用したときにエラーが発生します。(両方を実行することはできません。これを試みると、エラーが発生します。)
-
JSON列のJSON検索索引を作成します。
-
問合せ対象のJSONデータの列をインメモリー列ストア(IM列ストア)に格納し、キーワード
TEXT
を指定します。列のデータ型はJSON
である必要があり、そうしないとエラーが発生します。(JSON
型は、データベース初期化パラメータcompatible
が少なくとも20
である場合にのみ使用できます。)
注意:
Oracle SQLファンクションjson_textcontains
を使用すると、JSONデータの強力な全文検索を実行できます。単純な文字列のパターン一致のみが必要な場合は、かわりにパス式フィルタ条件をhas substring
、starts with
、like
、like_regex
またはeq_regex
のいずれかのパターン一致比較とともに使用できます。
例20-1は、明細項目の部品の説明にキーワードMagic
が含まれる発注書を検索する全文問合せを示しています。
注意:
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 SQL条件
json_textcontains
の詳細は、『Oracle Database SQL言語リファレンス』 -
Oracle Textの
contains
演算子の詳細は、Oracle Textリファレンス -
SQLファンクション
contains
の検索パターン(したがって、json_textcontains
の検索パターン)での特殊文字の使用の詳細は、Oracle Textリファレンス -
Oracle Text検索で予約されている単語および文字の詳細は、Oracle Textリファレンスを参照してください。また、それらのエスケープ方法は、Oracle Textリファレンスを参照してください。
例20-1 JSONデータの全文問合せ
SELECT po_document FROM j_purchaseorder
WHERE json_textcontains(po_document,
'$.LineItems.Part.Description',
'Magic');