SEARCH 関数は、検索結果をJSON形式で返すために使用されます。返される結果は関連性に基づいてソートされます。
構文
FUNCTION SEARCH (IN table_name VARCHAR(65535),
IN search_params LONGTEXT)
RETURN JSON;
パラメータ説明
パラメータ |
説明 |
必須 |
|---|---|---|
| table_name | テーブル名。ヒープテーブルと主キーのないテーブルをサポートします。 | 必須 |
| search_params | 検索パラメータ。JSON形式の文字列です。 | 必須 |
search_params の構文
search_params はJSON形式の文字列であり、その構文はこのセクションで詳しく説明します。以下のパラメータと例を併せてご理解ください。
構文の説明
このセクションでは、BNF構文記号の意味と使用規則について説明します。
オプションパラメータの表現
[ ]はBNFにおいて複数の要素を含めることができることを表します。例えば、param_list = param [, param]*はparam_listが1つまたは複数のparamを含むことができることを示しています。rank_feature、rank_expressionの[ ]もサブパラメータがオプションであることを示しています。[, "boost" : boost_value]はboostサブパラメータがオプションであることを示しており、すべてのboostサブパラメータはオプションです。
配列の表現
[ ]はJSON構造において配列を表します。例えば、[condition_list]が該当します。
結合関係
|は結合関係を表します。例えば、param = "query" | "knn"は param が "query" または "knn" であることができることを示しています。
繰り返しの表現
*は0回以上繰り返すことを表します。例えば、param_list = param [, param]*はparam_listが1つまたは複数のparamを含むことができることを示しています。
JSON形式要件
- すべてのJSONフィールド名と文字列値は二重引用符で囲む必要があります。
- 数値は二重引用符で囲む必要はありません。
構文の定義
このセクションでは、search_params の構文を詳しく説明します。パラメータの説明については、下記の詳細なパラメータ説明表を参照してください。
トップレベルパラメータ構造
-- トップレベルキーワードパラメータ
search_params = '{param_list}'
param_list = param [, param]*
-- queryとknnのパラメータは、それぞれ全文検索/スカラー検索/ベクトル検索に使用されます。少なくとも一方は必須です。ハイブリッド検索では両方を同時に使用します。
param = "query" : {query_expression}
| "knn" : {knn_expression}
| "rank" : {rank_expression}
| "_source" : [field_list]
| "from" : number
| "size" : number
| "es_mode" : boolean
注意
ハイブリッド検索を使用する場合、返される列の範囲を指定するために、_source フィールドを指定することを強く推奨します。指定しない場合、デフォルトでテーブル上のすべての列が返されます。ベクトル列が含まれる場合、応答時間（RT）が大幅に増加します。
クエリ式構造
query_expression = bool_query | single_term
-- bool_query構文
bool_query = "bool" : {bool_condition_list}
bool_condition_list = bool_condition [, bool_condition]*
-- boostサブパラメータはオプションです
bool_condition = "must" : [condition_list]
| "should" : [condition_list]
| "must_not" : [condition_list]
| "filter" : [condition_list]
| "boost" : boost_value
-- bool_queryのネストはサポートされています
condition_list = query_expression [, query_expression]*
単一語句クエリ構造
single_term = range_query | match_query | term_query | query_string | multi_match | rank_feature
-- range_query構文
range_query = "range" : {"field_name" : {range_condition_list}}
range_condition_list = range_condition [, range_condition]*
-- boostサブパラメータはオプションです
range_condition = "gte" : number
| "gt" : number
| "lte" : number
| "lt" : number
| "boost" : boost_value
-- match_query構文
match_query = "match" : {"field_name" : {match_condition}}
-- boostサブパラメータはオプションです
match_condition = "query" : "string_value" [, "boost" : boost_value]
-- term_query構文
term_query = "term" : {term_condition_list}
term_condition_list = term_condition [, term_condition]*
term_condition = "field_name" : scalar_value
| "field_name" : term_value_object
-- boostサブパラメータはオプションです
term_value_object = "value" : scalar_value [, "boost" : boost_value]
-- 全文検索式構造。query_stringとmulti_matchの2種類の検索方式を含みます
query_string = "query_string" : {query_string_condition}
-- fieldsとqueryサブパラメータは必須です
query_string_condition = "fields" : [field_weight_list]
| "query" : "string_value"
| "boost" : boost_value
| "type" : ("best_fields" | "cross_fields" | "most_fields" | "phrase")
| "default_operator" : ("AND" | "OR")
| "minimum_should_match" : number
field_weight_list = field_weight [, field_weight]*
-- field_name、^、numberの間にスペースは置けません
field_weight = "field_name[^number]"
-- multi_match構文。V4.4.1 HotFix1バージョンからサポートされています
multi_match = "multi_match" : {multi_match_condition}
-- fieldsとqueryサブパラメータは必須です
multi_match_condition = "fields" : [field_weight_list]
| "query" : "string_value"
| "boost" : boost_value
| "type" : ("best_fields" | "cross_fields" | "most_fields" | "phrase")
| "operator" : ("AND" | "OR")
| "minimum_should_match" : number
field_weight_list = field_weight [, field_weight]*
-- field_name、^、numberの間にスペースは置けません
field_weight = "field_name[^number]"
-- 特徴ランキング式構造
rank_feature = "rank_feature" : {rank_feature_condition_list}
rank_feature_condition_list = "field" : "field_name",
rank_algorithm
rank_algorithm = "saturation" : {saturation_params}
| "sigmoid" : {sigmoid_params}
| "log" : {log_params}
| "linear" : {linear_params}
saturation_params = "pivot" : number [, "positive_score_impact" : boolean]
sigmoid_params = "pivot" : number, "exponent" : number [, "positive_score_impact" : boolean]
log_params = "scaling_factor" : number [, "positive_score_impact" : boolean]
linear_params = ["positive_score_impact" : boolean]
ベクトル検索式の構造
-- knn_expression 構文構造
knn_expression = "knn" : {knn_condition_list}
knn_condition_list = knn_condition [, knn_condition]*
-- field、k、query_vector のサブパラメータは必須です。
knn_condition = "field" : "field_name"
| "k" : number
| "query_vector" : [vector_values]
| "filter" : [condition_list]
| "similarity" : number
| "boost" : boost_value
vector_values = float [, float]*
condition_list = {condition [, condition]*}
condition = single_term
-- rank_expression 構文構造。RRFはV4.4.1 HotFix1バージョンからサポートされています。
rank_expression = "rank" : {rank_strategy}
rank_strategy = "rrf" : {rrf_params}
rrf_params = "rank_window_size" : number [, "rank_constant" : number]
基本データ型の定義
-- 基本データ型の定義
field_name = "string_value"
field_list = field_name [, field_name]*
number = integer | decimal
boost_value = integer | float -- boostパラメータの値は0以上でなければなりません。
boolean = true | false
scalar_value = "string_value" | number | boolean
パラメータの詳細説明
search_params のパラメータの詳細説明は以下の通りです。
式のタイプ |
パラメータ名 |
パラメータの説明 |
|---|---|---|
| トップレベルキーワードパラメータ | ||
| query | 全文検索では単独で使用できます。ハイブリッド検索では knn パラメータと併用できます。 |
|
| knn | ベクトル検索では単独で使用できます。ハイブリッド検索では query パラメータと併用できます。 |
|
| rank（オプション） | ハイブリッド検索時のソート戦略を指定するために使用されます。RRF（Reciprocal Rank Fusion）アルゴリズムをサポートします。
注意このパラメータはV4.4.1 HotFix1バージョンからサポートされています。 |
|
| _source（オプション） | 検索で返す列を指定するために使用されます。指定しない場合、テーブル上のすべてのユーザー定義列が返されます。 | |
| from（オプション） | 検索結果セットの何行目から結果を返すかを指定するために使用されます。指定しない場合、デフォルトで1行目から返されます。size パラメータと併用する必要があります。 |
|
| size（オプション） | 返される結果の数を制限するために使用されます。指定しない場合、デフォルトは 10 です。 |
|
| es_mode | 全文検索をESQLの新しい構文に変換するかどうかを指定するために使用されます。デフォルトは false です。 |
|
| bool | must | 満たす必要があり、スコアを計算する必要があります。内部でブール論理が必要な場合は、bool式をネストする必要があります。bool式内の複数の条件は、デフォルトでAND論理で組み合わされます。 |
| should | 満たすべきであり、ORのようなものです。スコアを計算する必要があります。内部でブール論理が必要な場合は、bool式をネストする必要があります。bool式内の複数の条件は、デフォルトでAND論理で組み合わされます。 | |
| must_not | 満たしてはならない。スコアは計算されません。'NOT' 式に変換され、must_not内の複数の項は 'AND' で結ばれます。内部でブール論理が必要な場合は、bool式をネストする必要があります。bool式内の複数の条件は、デフォルトでAND論理で組み合わされます。 | |
| filter | 満たす必要がありますが、スコアは計算されません。'AND' 式に変換されます。内部でブール論理が必要な場合は、bool式をネストする必要があります。bool式内の複数の条件は、デフォルトでAND論理で組み合わされます。 | |
| boost（オプション） | クエリの重みです。詳細は下記のboostパラメータの説明を参照してください。 | |
| rank_feature（関連スコア計算パラメータ） | pivot | saturation、sigmoidでは必須のスコア計算パラメータです。デフォルト値はこのリスト上のデータの幾何平均です。 |
| positive_score_impact（オプション） | フィールドが最終的な関連性に正の影響を与えるか負の影響を与えるかを設定します。 | |
| scaling_factor | log スコア計算式の必須パラメータです。 | |
| exponent | sigmoid アルゴリズムの計算式の必須パラメータです。 | |
| rank_feature（関連スコアアルゴリズム） | saturation | デフォルトの関連スコアアルゴリズム
|
| sigmoid |
|
|
| log |
|
|
| linear |
|
|
| single term（単一語条検索） | ||
| range | 範囲検索。gte、gt、lte、lt、boost と組み合わせて使用します。fieldname は必須です。 |
|
| match | あいまい一致。sql の 'match' 式に変換し、boost と組み合わせて使用します。 | |
| term | 厳密一致。文字列、数値、ブール値などのスカラー値をサポートします。sql の '=' 式に変換し、boost と組み合わせて使用します。 | |
| query_string | 全文一致。sql の複数の 'match' 式の組み合わせに変換します。 | |
| multi_match | 全文一致。sql の複数の 'match' 式の組み合わせに変換します。query_string と同様ですが、単一のキーワードには重みを設定できません。
注意このパラメータはV4.4.1 HotFix1バージョンからサポートされています。 |
|
| fields | テキスト検索のフィールドリスト。各列の重みを設定できます。 | |
| query | 検索キーワードリスト。各キーワードの重みを設定できます。 | |
| minimum_should_match（オプション） | should、query_string で満たす必要がある条件の数をを制御します。このパラメータを記述しない場合、デフォルト値は 1 です。注意：bool 式に must/filter が含まれており、このパラメータを記述しない場合、デフォルト値は 0 となり、should の任意の条件を満たさなくてもよくなります。 |
|
| boost（オプション） | クエリの重みです。詳細は下記のboostパラメータの説明を参照してください。 | |
| type（オプション） | マッチングモードを指定します。今回は best_fiedls、cross_fields、most_fields、phrase をサポートします。指定しない場合、デフォルトは best_fields です。 |
|
| default_operator（オプション） | query_string のサブフィールドで、複数のキーワード間の組み合わせロジックを指定します。 | |
| operator（オプション） | multi_match のサブフィールドで、複数のキーワード間の組み合わせロジックを指定します。 | |
| knn（ベクトル検索） | ||
| field | ベクトル検索のフィールド。 | |
| k | ベクトル検索で返される行数を実行します。 | |
| query_vector | 検索ベクトルを指定します。 | |
| filter（オプション） | フィルター条件。 | |
| similarity（オプション） | ベクトル距離のフィルター条件を指定するために使用されます。 | |
| boost（オプション） | クエリの重みです。詳細は下記のboostパラメータの説明を参照してください。 | |
| rank（RRFソート戦略） | rrf | RRF（Reciprocal Rank Fusion）ソート戦略で、ハイブリッド検索時に複数のクエリ結果を融合ソートするために使用されます。
注意このパラメータはV4.4.1 HotFix1バージョンからサポートされています。 |
| rank_window_size（オプション） | この値は、各クエリが返す個々の結果セットのサイズを指定するために使用されます。値が大きいほど、結果の関連性は高くなりますが、パフォーマンスオーバーヘッドが発生します。最終的なソート結果セットは、検索リクエストで指定された size サイズにトリミングされます。rank_window_size は、以下の両方を満たす必要があります：
size パラメータの値です。 |
|
| rank_constant（オプション） | この値は、各クエリが返す個々の結果セット内の各ドキュメントが最終的なソート結果に与える影響度を制御するために使用されます。値が大きいほど、ランキングの低いドキュメントが最終結果に与える影響が大きくなります。デフォルト値は 60 です。 |
boostパラメータの詳細説明
boostパラメータは、クエリ条件が最終的な関連性計算において持つ重みを指定するために使用されます。値は必ず0以上でなければならず、指定しない場合はデフォルトで1となります。上記の構文では、bool、single_term（rank_featureを除く）、knnはすべてboostパラメータの指定をサポートしています。各検索タイプでサポートされるboostパラメータの使い方は以下の通りです：
クエリレベルのboost
クエリ全体の条件に重みを指定します。例：
{ "bool": { "must": [{"term": {"category": "Gaming"}}], "boost": 2.0 // boolクエリ全体の重み } }フィールドレベルのboost
特定のフィールドに対するクエリに重みを指定します：
{ "query_string": { "fields": ["product_name", "description"], "query": "gaming keyboard", "boost": 1.5 // query_stringクエリ全体の重み } }照合値レベルのboost
具体的な照合値に重みを指定します（matchおよびtermクエリのみサポート）：
{ "match" : { "product_name": { "query" : "gaming keyboard", "boost" : 1.5 } } }フィールド重み構文
query_stringおよびmulti_matchでは、
field_name^weight構文を使用できます：{ "query_string": { "fields": ["product_name^2.0", "description^1.0"], "query": "gaming" } }
例
通常のスカラー検索
サンプルテーブル products の構造は以下のとおりです：
CREATE TABLE products (
`product_id` varchar(50) DEFAULT NULL,
`product_name` varchar(255) DEFAULT NULL,
`description` text DEFAULT NULL,
`brand` varchar(100) DEFAULT NULL,
`category` varchar(100) DEFAULT NULL,
`tags` varchar(255) DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
`stock_quantity` int(11) DEFAULT NULL,
`release_date` datetime DEFAULT NULL,
`is_on_sale` tinyint(1) DEFAULT NULL,
`vec` VECTOR(4) DEFAULT NULL
);
データを挿入します。
INSERT INTO products VALUES
('prod-004', 'Gamer-Pro Mechanical Keyboard', 'A responsive mechanical keyboard with customizable RGB lighting for the ultimate gaming experience.',
'GamerZone', 'Gaming', 'best-seller,gaming-gear,rgb', 149.00, 100, '2023-07-20 00:00:00.000000', 1, '[0.5,0.1,0.6,0.9]'),
('prod-009', 'Gamer-Pro Headset', 'High-fidelity gaming headset with a noise-cancelling microphone.',
'GamerZone', 'Gaming', 'best-seller,gaming-gear,audio', 149.00, 100, '2023-07-20 00:00:00.000000', 1, '[0.1,0.9,0.2,0]');
検索パラメータを設定します。
SET @parm = '{
"query": {
"bool": {
"must": [
{"term": {"brand": "GamerZone"}}
]
}
},
"_source": ["product_id", "product_name", "description", "brand", "category", "tags", "price", "stock_quantity", "release_date", "is_on_sale"]
}';
brand が "GamerZone" のすべてのレコードを検索します。
SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products', @parm));
結果は次のとおりです：
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products', @parm)) |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [
{
"tags": "best-seller,gaming-gear,rgb",
"brand": "GamerZone",
"price": 149.00,
"_score": 1,
"category": "Gaming",
"is_on_sale": 1,
"product_id": "prod-004",
"description": "A responsive mechanical keyboard with customizable RGB lighting for the ultimate gaming experience.",
"product_name": "Gamer-Pro Mechanical Keyboard",
"release_date": "2023-07-20 00:00:00.000000",
"stock_quantity": 100
},
{
"tags": "best-seller,gaming-gear,audio",
"brand": "GamerZone",
"price": 149.00,
"_score": 1,
"category": "Gaming",
"is_on_sale": 1,
"product_id": "prod-009",
"description": "High-fidelity gaming headset with a noise-cancelling microphone.",
"product_name": "Gamer-Pro Headset",
"release_date": "2023-07-20 00:00:00.000000",
"stock_quantity": 100
}
] |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
フルテキストとベクトルのハイブリッド検索
ベクトル列を含むサンプルテーブルを作成し、その列に対してベクトルインデックスを作成します。また、2つのvarchar列にそれぞれフルテキストインデックスを作成します。
CREATE TABLE doc_table(c1 INT, vector VECTOR(3), query VARCHAR(255), content VARCHAR(255), VECTOR INDEX idx1(vector) WITH (distance=l2, type=hnsw, lib=vsag), FULLTEXT INDEX idx2(query), FULLTEXT INDEX idx3(content));
データを書き込みます。
INSERT INTO doc_table VALUES(1, '[1,2,3]', "hello world", "oceanbase Elasticsearch database"),
(2, '[1,2,1]', "hello world, what is your name", "oceanbase mysql database"),
(3, '[1,1,1]', "hello world, how are you", "oceanbase oracle database"),
(4, '[1,3,1]', "real world, where are you from", "postgres oracle database"),
(5, '[1,3,2]', "real world, how old are you", "redis oracle database"),
(6, '[2,1,1]', "hello world, where are you from", "starrocks oceanbase database");
検索パラメータを設定します。
SET @parm = '{
"query": {
"bool": {
"should": [
{"match": {"query": "hi hello"}},
{"match": { "content": "oceanbase mysql" }}
]
}
},
"knn" : {
"field": "vector",
"k": 5,
"query_vector": [1,2,3]
},
"rank": {
"rrf": {
"rank_window_size": 10,
"rank_constant": 60
}
},
"_source" : ["query", "content"]
}';
クエリを実行し、結果を返します。
SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm));
結果は次のとおりです：
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm)) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [
{
"query": "hello world",
"_score": 0.0325,
"content": "oceanbase Elasticsearch database"
},
{
"query": "hello world, what is your name",
"_score": 0.0323,
"content": "oceanbase mysql database"
},
{
"query": "hello world, how are you",
"_score": 0.0315,
"content": "oceanbase oracle database"
},
{
"query": "real world, how old are you",
"_score": 0.0161,
"content": "redis oracle database"
},
{
"query": "hello world, where are you from",
"_score": 0.0159,
"content": "starrocks oceanbase database"
},
{
"query": "real world, where are you from",
"_score": 0.0156,
"content": "postgres oracle database"
}
] |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set