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
フルテキストとベクトルのハイブリッド検索
ベクトル列を含むサンプルテーブルを作成し、その列に対してベクトルインデックスを作成します。また、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