The SEARCH function is used to return search results in JSON format. The returned results are sorted by relevance.
Syntax
FUNCTION SEARCH (IN table_name VARCHAR(65535),
IN search_params LONGTEXT)
RETURN JSON;
Parameters
Parameter |
Description |
Required |
|---|---|---|
| table_name | Table name, which can be a heap table or a table without a primary key. | Required |
| search_params | The search parameter, which is a string in JSON format. | Required |
Syntax of search_params
search_params is a JSON-formatted string. Its syntax is described in detail in this section. Please understand it together with the parameters and examples below.
Syntax
This section describes the meanings and usage rules of BNF syntax symbols:
Optional parameters
[ ]in BNF indicates that multiple elements are optional. For example,param_list = param [, param]*meansparam_listcan contain one or moreparamelements.- In
rank_featureandrank_expression,[ ]also indicates that the sub-parameter is optional. [, "boost" : boost_value]indicates that the boost subparameter is optional and all boost subparameters are optional.
Array representation
[ ]indicates an array in a JSON structure, such as[condition_list].
Select relationships
|indicates an OR relationship. For example,param = "query" | "knn"means param can be "query" or "knn".
Repeat representation
*indicates zero or more repetitions. For example,param_list = param [, param]*meansparam_listcan contain one or moreparamelements.
Requirements for JSON format
- All JSON field names and string values must be enclosed in double quotes.
- Numbers do not need to be enclosed in double quotes.
Syntax Definition
This section describes the syntax of search_params. For more information about the parameters, see the table below.
Top-level parameter structure
-- Top-level keyword parameters
search_params = '{param_list}'
param_list = param [, param]*
-- The query and knn parameters are used for full-text, scalar, and vector retrieval respectively. At least one of them must be specified; they are used together for hybrid retrieval.
param = "query" : {query_expression}
| "knn" : {knn_expression}
| "rank" : {rank_expression}
| "_source" : [field_list]
| "from" : number
| "size" : number
| "es_mode" : boolean
Notice
When using hybrid search, it is strongly recommended to specify the _source field to define the range of columns to return. If not specified, all columns in the table are returned by default. This can significantly increase response time (RT) if vector columns are included.
Query expression syntax
query_expression = bool_query | single_term
-- Syntax of bool_query
bool_query = "bool" : {bool_condition_list}
bool_condition_list = bool_condition [, bool_condition]*
-- boost subparameter available
bool_condition = "must" : [condition_list]
| "should" : [condition_list]
| "must_not" : [condition_list]
| "filter" : [condition_list]
| "boost" : boost_value
-- Supports nested bool_query
condition_list = query_expression [, query_expression]*
Single entry query structure
single_term = range_query | match_query | term_query | terms_query | query_string | multi_match | rank_feature
-- Syntax of range_query
range_query = "range" : {"field_name" : {range_condition_list}}
range_condition_list = range_condition [, range_condition]*
-- boost subparameter available
range_condition = "gte" : number
| "gt" : number
| "lte" : number
| "lt" : number
| "boost" : boost_value
-- Syntax of match_query
match_query = "match" : {"field_name" : {match_condition}}
-- boost subparameter available
match_condition = "query" : "string_value" [, "boost" : boost_value]
-- Syntax of 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 subparameter available
term_value_object = "value" : scalar_value [, "boost" : boost_value]
-- Syntax of terms_query
terms_query = "terms" : {terms_condition_list}
terms_condition_list = terms_condition [, terms_condition]*
terms_condition = "field_name" : [scalar_value_list]
| "boost" : boost_value
scalar_value_list = scalar_value [, scalar_value]*
-- The structure of a full-text search expression, which includes two retrieval methods: query_string and multi_match.
query_string = "query_string" : {query_string_condition}
-- fields and query sub-parameters are required
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]*
-- No spaces are allowed between field_name, ^, and number.
field_weight = "field_name[^number]"
-- Syntax of multi_match
multi_match = "multi_match" : {multi_match_condition}
-- fields and query sub-parameters are required
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]*
-- No spaces are allowed between field_name, ^, and number.
field_weight = "field_name[^number]"
-- Feature sorting expression structure
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]
Structure of a vector retrieval expression
-- Single-Path Vector Retrieval Expression Structure
knn_expression = "knn" : {knn_condition_list}
knn_condition_list = knn_condition [, knn_condition]*
-- The field, k, and query_vector subparameters are required.
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 = query_expression [, query_expression]*
-- Multi-vector retrieval expression structure
-- The [] in [multi_knn_condition_list] is a fixed format and cannot be modified.
multi_knn_expression = "knn" : [multi_knn_condition_list]
-- The [] in [multi_knn_condition] indicates that the element is optional.
multi_knn_condition_list = {multi_knn_condition} [, {multi_knn_condition}]*
-- The field, k, and query_vector subparameters are required.
multi_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 = query_expression [, query_expression]*
-- Syntax of rank_expression
rank_expression = "rank" : {rank_strategy}
rank_strategy = "rrf" : {rrf_params}
rrf_params = "rank_window_size" : number [, "rank_constant" : number]
Basic type definitions
-- Basic type definitions
field_name = "string_value"
field_list = field_name [, field_name]*
number = integer | decimal
boost_value = integer | float -- The value of the boost parameter must be greater than or equal to 0.
boolean = true | false
scalar_value = "string_value" | number | boolean
Detailed parameter description
The detailed parameter description of search_params is as follows:
Expression type |
Parameter name |
Parameter description |
|---|---|---|
| Top-level keyword parameters | ||
| query | Can be used alone for full-text search, or together with the knn parameter for hybrid retrieval. |
|
| knn | Can be used separately for single-path or multi-path vector retrieval, and can also be used together with the query parameter for hybrid retrieval. |
|
| rank (optional) | Specifies the sorting strategy during hybrid retrieval. The RRF (Reciprocal Rank Fusion) algorithm is supported. | |
| _source (Optional) | Specifies the columns to return in the query result. If this parameter is not specified, all user-defined columns in the table are returned. | |
| from (optional) | Specifies the row number in the result set from which to return results. If not specified, the first row is returned by default. This parameter must be used together with the size parameter. |
|
| size (optional) | The function used to limit the number of returned results. If not specified, the default is 10. |
|
| es_mode | Specifies whether to convert the full-text search to the new ESQL syntax. Default: false. |
|
| bool | must | Required. A score must be calculated. When boolean logic is needed internally, a bool expression must be nested. Multiple conditions in a bool expression are combined by default using the AND logic. |
| should | Should be satisfied, similar to OR, requiring score calculation. When boolean logic is needed internally, a bool expression must be nested. Multiple conditions within a bool expression are combined by default using the AND logic. | |
| must_not | Must not be met; no score is calculated. Converts to a 'NOT' expression, with multiple 'AND' links within must_not. When boolean logic is required internally, a bool expression must be nested. Multiple conditions in a bool expression are combined by default using the AND logic. | |
| filter | A condition that must be met. No score is calculated for this condition, which is converted into an 'AND' expression. When Boolean logic is required internally, a bool expression must be nested. Multiple conditions in a bool expression are combined by default using the AND logic. | |
| boost (optional) | The query weight. For more information, see the boost parameter description below. | |
| rank_feature (related score calculation parameters) | pivot | saturation and sigmoid are required scoring parameters. The default value is the geometric mean of the data in this list. |
| positive_score_impact (Optional) | Specifies whether the field has a positive or negative impact on the final correlation. | |
| scaling_factor | A required parameter in the log-based scoring formula. | |
| exponent | A required parameter in the sigmoid algorithm calculation formula. | |
| rank_feature (related score algorithm) | saturation | Default correlation-based partitioning algorithm
|
| sigmoid |
|
|
| log |
|
|
| linear |
|
|
| single term (search by single entry) | ||
| range | Range search, used with gte, gt, lte, lt, and boost. The fieldname parameter is required. |
|
| match | Fuzzy match. Converted to the SQL 'match' expression for use with Boost. | |
| term | Exact match. Supports scalar values such as strings, numbers, and booleans. Converts them into SQL's '=' expression for use with Boost. | |
| terms | Matches any value in the specified set with high precision. Supports arrays of scalar values such as strings, numbers, and booleans, which can be converted into SQL's 'IN' expression for use with Boost. | |
| query_string | Full-text match, which is converted into a combination of multiple 'match' expressions in SQL. | |
| multi_match | Full-text match. It is converted into a combination of multiple 'match' expressions in SQL, similar to query_string. Single keywords do not support weights. | |
| fields | The list of text retrieval fields, where you can configure the weight for each column. | |
| query | The search keyword list, where you can configure the weight for each keyword. | |
| minimum_should_match (Optional) | Specifies the number of conditions that must be met in the should or query_string clause. If this parameter is omitted, the default value is 1. Note: If a must or filter condition exists in the boolean expression and this parameter is omitted, the default value will be 0, meaning any condition in the should clause can be unsatisfied. |
|
| boost (optional) | The query weight. For more information, see the boost parameter description below. | |
| type (optional) | Specifies the matching mode. Valid values: best_fields, cross_fields, most_fields, and phrase. If not specified, the default is best_fields. |
|
| default_operator (Optional) | A subfield of query_string that specifies the combination logic among multiple keywords. | |
| operator (optional) | The subfield of multi_match, which specifies the combination logic among multiple keywords. | |
| knn (Vector Retrieval) | ||
| field | The vector retrieval field. | |
| k | The number of rows returned by the vector retrieval query. | |
| query_vector | Specifies the retrieval vector. | |
| filter (optional) | The filter condition. | |
| similarity (optional) | Specifies the filter condition for vector distance. | |
| boost (optional) | The query weight. For more information, see the boost parameter description below. | |
| rank (RRF ranking strategy) | rrf | RRF (Reciprocal Rank Fusion) is a sorting strategy used to fuse and rank multiple query results in hybrid retrieval. |
| rank_window_size (Optional) | This value specifies the size of the single result set returned by each query. A larger value indicates a higher degree of relevance but comes with performance overhead. The final sorted result set will be trimmed to the size specified by the size parameter in the search request.The rank_window_size must satisfy both of the following conditions:
size parameter. |
|
| rank_constant (optional) | This value controls the influence of each document pair in the single result set returned by each query on the final sorting result. A larger value indicates that documents with lower rankings have a greater impact on the final result. The default value is 60. |
Detailed description of boost parameters
The boost parameter specifies the weight of a query condition in the final relevance calculation. The value must be ≥ 0. If not specified, the default is 1. In the syntax structure above, bool, single_term (except for rank_feature), single-channel, and multi-channel vector retrieval (knn) all support specifying the boost parameter. The usage of the boost parameter varies by retrieval type as follows:
Query-level boosting
Specify a weight for the entire query condition, for example:
{ "bool": { "must": [{"term": {"category": "Gaming"}}], "boost": 2.0 // The weight of the entire Boolean query. } }Field-level boosting
Specify weights for queries on specific fields:
{ "query_string": { "fields": ["product_name", "description"], "query": "gaming keyboard", "boost": 1.5 // The weight of the entire query_string query } }Value-level boosting
Specify weights for specific match values (supported for MATCH, TERM, and TERMS queries):
{ "match" : { "product_name": { "query" : "gaming keyboard", "boost" : 1.5 } } }Syntax of field weight
In query_string and multi_match, you can use the
field_name^weightsyntax:{ "query_string": { "fields": ["product_name^2.0", "description^1.0"], "query": "gaming" } }Vector retrieval boost
In single- and multi-vector retrieval, you can independently specify the
boostweight for each vector field:Single-path vector retrieval:
{ "knn": { "field": "vector", "k": 5, "query_vector": [1, 2, 3], "boost": 1.5 // The weight of this vector field. } }Multi-vector retrieval:
{ "knn": [{ "field": "c2", "k": 5, "query_vector": [1, 2, 3], "boost": 2.0 // The weight of the C2 vector field. }, { "field": "c4", "k": 5, "query_vector": [1, 2, 3], "boost": 1.0 // The weight of the C4 vector field. }] }
Examples
Regular scalar retrieval
The structure of the sample table products is as follows:
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 data.
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 the retrieval parameters.
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"]
}';
Retrieve all records where brand is "GamerZone".
SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products', @parm));
The return result is as follows:
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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
Full-text and vector hybrid retrieval
Create a sample table containing a vector column, create a vector index for it, and create full-text indexes for the two VARCHAR columns respectively.
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));
Write data.
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 the retrieval parameters.
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"]
}';
Executes the query and returns the query result.
SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm));
The return result is as follows:
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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