HYBRID_SEARCH is used to describe a full-text search, vector search, and filter conditions in a single SELECT statement using JSON strings that comply with the rules of this document, and returns rows sorted according to the fusion strategy.
Syntax
SELECT select_expr_list
FROM HYBRID_SEARCH(TABLE table_name, dsl_string) [table_alias];
-- select_expr_list is the SELECT list.
-- In addition to table columns, you can also reference hybrid search pseudo-columns. For details, see the "Pseudo-columns" section below.
Parameters
Parameters |
Note |
|---|---|
table_name |
Target table name. Only heap tables are supported. ORGANIZATION = HEAP), which can be a partitioned table or a non-partitioned table. |
dsl_string |
JSON string, description. query, knn, rank, rerank, aggs, collapse, sort, from, size, min_score and so on. |
Global limitations and notes
This section only lists syntax limitations. For feature limitations, see Hybrid search. You must understand them in conjunction with the content of this section.
The following are the details:
- The top-level
query(full-text search) andknn(vector search) procedures can have at most three subqueries in total: one forquery, and one forknnif it is an object; otherwise, one for each element of the array. The total number cannot exceed three, and at least one full-text or vector search must be included. - Column names in queries are case-insensitive.
- For the
query_vectorparameter in vector queries, it is recommended to input vectors as strings. - The
min_scoreparameter is supported only by the SQL interface and not by the PL interface. - Each
queryand eachknnis an independent search path, and theirfiltersdo not affect each other. If a filter condition is needed for a particular search path, you must specify the correspondingfilterseparately on that path. All search results are merged into a single result set, scored and ranked based on a fusion algorithm, and the topsizeresults are returned. - Any query can support
boost, but it may not necessarily be used in scoring. For specific rules, see the boost parameters section below. rerankmust be used together with eitherqueryorknnand cannot appear alone. The execution order is rough ranking first, followed by fine ranking. You cannot usererankalone to replace the fusion capability ofrank.
Limitations:
The
rank_feature,es_mode, and_sourceparameters are not supported.Direct use of
WHERE/ORDER BY/LIMITat the same level asHYBRID_SEARCHis not supported. If further filtering or sorting is required, process the hybrid search result as a subquery. For example:-- Not supported: Using WHERE in the same layer as HYBRID_SEARCH SELECT id FROM HYBRID_SEARCH(TABLE doc_table, '{"knn":{"field":"vector","k":5,"query_vector":"[1,2,3]"}}') WHERE id > 3; -- Supported: First subquery, then filtering SELECT id FROM ( SELECT id FROM HYBRID_SEARCH(TABLE doc_table, '{"knn":{"field":"vector","k":5,"query_vector":"[1,2,3]"}}') ) t WHERE id > 3;Some nested JSON/ARRAY element paths cannot be pushed down. For example, if the
pathof ajsonelement is anarraywhose elements arejson, you cannot specify a path for thisjsonelement.Scalar query conditions, including
term/range/terms/wildcard/ JSON expressions / array expressions, all supportboost. When placed at the top level inquery,bool.must/bool.should, they participate in scoring; when placed inbool.filter/bool.must_not/knn.filter, they only serve as hard filters. For specific rules, see the boost parameters section below.The
boostfor non-top-level full-text queries, theboostforboolqueries, the column weights, and word weights for full-text queries must be greater than0. For other cases, theboostmust be greater than or equal to0.Multiple full-text columns in
multi_match/query_stringqueries must use the same character set andcollation, and the full-text index must use the sameparser.When
match/multi_match/query_stringis used for term-level matching, the corresponding full-text columns should useFTS_INDEX_TYPE = MATCH(or the default type equivalent toMATCHif not specified).match_phraseapplies only to full-text index columns for whichFTS_INDEX_TYPE = PHRASE_MATCHhas been created.sortandrankcannot be specified simultaneously;aggsandsortcannot be specified simultaneously;aggsandcollapsecannot be specified simultaneously.The query
SELECT count(*)is supported. In this query, the SELECT list can only containcount(*)and cannot be mixed with table columns,__score, or aggregate pseudocolumns. For more information about pseudocolumns, see the Pseudocolumns section below. For counting semantics, see the "Counting queries (count(*))" section.
Syntax of DSL_STRING parameters
dsl_string is a string in JSON format. The syntax structure is described in detail in this section. Please understand it in conjunction with the parameters and examples below.
Suggested reading order
First, review the Sample DSL_STRING parameters (which top-level fields it consists of). Then, refer to the BNF blocks in the Syntax definition as needed. For the meaning and constraints of each field, refer to the Detailed parameter description table. For end-to-end table creation and more scenario examples, see Hybrid search.
Syntax
This section describes the meanings and usage rules of BNF (Backus-Naur Form) syntax symbols:
Optional parameters
[ ]in BNF indicates that multiple elements are optional. For example,param_list = param [, param]*meansparam_listcan contain one or moreparamelements.- The
[ ]inrank_expressionalso indicates that the sub-parameter is optional. [, "boost" : boost_value]indicates that this sub-parameter is optional in expressions that supportboost.
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]*indicates thatparam_listcan contain one or moreparamelements.
JSON format requirements
- All JSON column 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 dsl_string in detail. For parameter descriptions, see the table below.
Sample DSL_STRING parameter skeleton
The following dsl_string skeleton illustrates the positions and formats of all top-level fields: query (full-text), knn (vector), rank (fusion rough ranking), rerank (model refined ranking), aggs (aggregation), collapse (collapse), sort (sorting), from / size (pagination), and min_score (minimum score, available only in the SQL interface). In actual requests, retain only the compatible keys as needed. For a complete runnable example, see [Hybrid search](#Related topics).
Notice
This skeleton is only a structural illustration and cannot be executed as a whole. Some fields are mutually exclusive; see the "Global restrictions and notes" section above for details.
SELECT ...,
'{
"query": {
"match": {
"content": "oceanbase mysql"
}
},
"knn": {
"field": "vector",
"k": 5,
"query_vector": "[1,2,3]"
},
"rank": {
"rrf": {
"rank_window_size": 10,
"rank_constant": 60
}
},
"rerank": {
"model": "rerank_model",
"field": "content",
"query": "oceanbase mysql",
"rank_window_size": 10
},
"aggs": {
"my_terms_agg": {
"terms": {
"field": "category"
}
}
},
"collapse": {
"field": "category"
},
"sort": [
{
"publish_time": {
"order": "desc"
}
}
],
"from": 0,
"size": 10,
"min_score": 0
}'
);
Top-level parameter structure
The top-level parameter structure is used to specify the parameters for hybrid search.
dsl_string = '{param_list}'
param_list = param [, param]*
-- At least one of query and knn must be selected; they can be used together for hybrid search.
param = "query" : {query_expression} -- The value is a JSON object that contains the query expression and, optionally, search_options.
| "knn" : {knn_expression}
| "rank" : {rank_expression}
| "rerank" : {rerank_params}
| "aggs" : {aggs_expression}
| "collapse" : {collapse_expression}
| "sort" : [sort_item_list]
| "from" : number
| "size" : number
| "min_score" : number -- Supported only by the SQL interface.
Query expression syntax
In blended search, both the full-text and scalar conditions are specified in the query clause. The value of query is a JSON object: the innermost element must be a query_expression of the following types: bool, match, or term. To configure options such as recall parallelism, you can add an optional search_options field to the same JSON object. For details, see [Query options structure](#Query options structure).
query_expression = bool_query | scalar_term | fulltext_term | nested_query
bool_query = "bool" : {bool_condition_list}
bool_condition_list = bool_condition [, bool_condition]*
bool_condition = "must" : [condition_list]
| "should" : [condition_list]
| "must_not" : [condition_list]
| "filter" : [condition_list]
| "minimum_should_match" : msm_value -- bool-level minimum_should_match, supported starting from V4.6.2.1
| "boost" : boost_value
condition_list = query_expression [, query_expression]*
The values for must, should, must_not, and filter under bool are all arrays of query expressions. Each element in the array is a complete query_expression (which can further nest bool, nested, or be followed by match / term, etc.). The following example shows only the inner fragment of the query object:
{
"bool": {
"must": [
{
"match": {
"content": "oceanbase"
}
}
],
"filter": [
{
"term": {
"status": 1
}
}
]
}
}
Scalar query structure
Used to define scalar query expressions within a single entry, including range_query, term_query, terms_query, and wildcard_query, representing range queries, exact matches, multiple-value matches, and wildcard matches respectively.
scalar_term = range_query | term_query | terms_query | wildcard_query
range_query = "range" : {"field_name" : {range_condition_list}}
range_condition_list = range_condition [, range_condition]*
range_condition = "gte" : number
| "gt" : number
| "lte" : number
| "lt" : number
| "boost" : boost_value
term_query = "term" : {term_condition_list}
term_condition_list = term_condition [, term_condition]*
term_condition = "field_name" : scalar_value
| "field_name" : term_value_object
term_value_object = "value" : scalar_value
[, "boost" : boost_value]
terms_query = "terms" : {terms_condition_list [, "boost" : boost_value]}
terms_condition_list = terms_condition [, terms_condition]*
terms_condition = "field_name" : [scalar_value_list]
scalar_value_list = scalar_value [, scalar_value]*
wildcard_query = "wildcard" : {wildcard_condition_list}
wildcard_condition_list = wildcard_condition [, wildcard_condition]*
wildcard_condition = "field_name" : wildcard_pattern
| "field_name" : {wildcard_value_object}
wildcard_pattern = "string_value" | number | boolean
wildcard_value_object = wildcard_value_body [, "boost" : boost_value]
wildcard_value_body = "value" : wildcard_pattern
| "wildcard" : wildcard_pattern
In addition to scalar columns, term and terms also support single-level array columns and array paths within JSON documents. The details are as follows:
Query |
Column type |
Description |
|---|---|---|
term |
scalar | Equal match |
term |
One-dimensional array | Determine whether an array contains a scalar |
term |
Array in JSON | Determine whether an array contains a scalar |
terms |
scalar | Field value is in the parameter list. |
terms |
One-dimensional array | Determine whether the parameter array has an intersection with the array column |
terms |
Array in JSON | Determine whether the parameter array and the JSON array have an intersection |
The limitations are as follows:
- Currently,
termandtermsonly support single-level arrays.
Nested query structure
Note
Nested queries are supported starting from V4.6.2.1.
This operator is used to perform associated queries on an array of JSON objects based on the same array element. Before using it, you must declare NESTED_PATHS in the search index. For details, see Search index.
nested_query = "nested" : {nested_body}
nested_body = "path" : "column.json_path"
| "query" : query_expression
Full-text query structure
Used to define full-text query expressions within individual entries, including match_query, match_phrase_query, query_string, and multi_match, representing term-level matching, phrase-level matching, full-text search, and multi-column term-level matching.
fulltext_term = match_query | match_phrase_query | query_string | multi_match
match_query = "match" : {"field_name" : match_body}
match_body = "string_value" | {match_condition}
match_condition = "query" : "string_value" [, "operator" : ("OR" | "AND")] [, "minimum_should_match" : msm_value] [, "boost" : boost_value]
-- The `minimum_should_match` parameter takes effect only when the `operator` is set to `OR` (which is the default when not specified). When `operator` is set to `AND`, all terms must match for a match to be considered valid, which conflicts with the semantics of `minimum_should_match`. Therefore, these two parameters cannot be used simultaneously.
match_phrase_query = "match_phrase" : {"field_name" : phrase_body}
phrase_body = "string_value" | {phrase_condition}
phrase_condition = "query" : "string_value" [, "slop" : number] [, "boost" : boost_value]
query_string = "query_string" : {query_string_condition}
query_string_condition = "fields" : [field_weight_list]
| "query" : "string_value" -- Can contain the metapositional operator ^. For details, see "Weight operator" below.
| "boost" : boost_value
| "type" : ("best_fields" | "most_fields")
| "default_operator" : ("AND" | "OR")
| "minimum_should_match" : msm_value
-- The minimum_should_match condition takes effect only when the default_operator is set to OR.
multi_match = "multi_match" : {multi_match_condition}
multi_match_condition = "fields" : [field_weight_list]
| "query" : "string_value" -- Does not support the ^ metacharacter in query strings.
| "boost" : boost_value
| "type" : ("best_fields" | "most_fields")
| "operator" : ("AND" | "OR")
| "minimum_should_match" : msm_value
-- The `minimum_should_match` clause takes effect only when the operator is OR (including the default OR).
field_weight_list = field_weight [, field_weight]*
field_weight = "field_name[^number]"
msm_value = number | "string_value" -- String type is supported starting from V4.6.2.1
Query options structure
search_options is used only in the query path. It is placed within the query object, alongside match and bool, and cannot be used alone as the value for query.
search_options = "search_options" : {search_options_body}
search_options_body = "query_dop" : number
{
"query": {
"search_options": {"query_dop": 4},
"match": {"content": "oceanbase mysql"}
}
}
Vector and sorting structures
It is used to specify vector search parameters, including the search vector field, similarity, filter conditions, and weights. It supports both single-channel and multi-channel vector retrieval.
knn_expression = "knn" : {knn_condition_list} | [multi_knn_condition_list]
knn_condition_list = knn_condition [, knn_condition]*
knn_condition = "field" : "field_name"
| "k" : number
| "query_vector" : [vector_values]
| "num_candidates" : number
| "search_options" : {search_option_list}
| "filter" : [condition_list]
| "similarity" : number
| "boost" : boost_value
search_option_list = search_option [, search_option]*
search_option = "ef_search" : number
| "refine_k" : number
| "filter_mode" : ("pre" | "pre-knn" | "pre-brute" | "post" | "post-index-merge")
multi_knn_condition_list = {knn_condition} [, {knn_condition}]*
vector_values = float [, float]*
rank_expression = "rank" : {rank_strategy}
rank_strategy = "rrf" : {rrf_params}
| "weighted_sum" : {weighted_sum_params}
rrf_params = "rank_window_size" : number [, "rank_constant" : number]
weighted_sum_params = "rank_window_size" : number [, "normalizer" : ("minmax" | "none")]
rerank_params = "model" : "string_value"
| "field" : "field_name"
| "query" : "string_value"
| "rank_window_size" : number
| "type" : "string_value"
aggs_expression = "aggs" : {aggregation_bucket}
aggregation_bucket = "agg_name" : {aggregation_body}
aggregation_body = "terms" : {terms_condition_list}
| "cardinality" : {cardinality_condition_list}
terms_condition_list = terms_condition [, terms_condition]*
terms_condition = "field" : "field_name"
| "size" : number
| "min_doc_count" : number
| "order" : {"_count" : ("asc" | "desc")}
| "order" : {"_key" : ("asc" | "desc")}
cardinality_condition_list = "field" : "field_name"
collapse_expression = "collapse" : {collapse_condition_list}
collapse_condition_list = "field" : "field_name"
sort_item_list = sort_item [, sort_item]*
sort_item = "field_name"
| "__score"
| {"field_name" : {sort_condition_list}}
sort_condition_list = sort_condition [, sort_condition]*
sort_condition = "order" : ("asc" | "desc")
| "missing" : ("_first" | "_last" | number)
During single-vector search, knn is a single object. During multi-vector search, knn is an array of objects, where each element is a set of knn_condition. The following example shows the knn section for multi-vector search:
{
"knn": [
{
"field": "vector_a",
"k": 3,
"query_vector": "[1,0,0]"
},
{
"field": "vector_b",
"k": 3,
"query_vector": "[0,1,0]"
}
]
}
Basic type definitions
Basic data type definitions used in the preceding syntax.
field_name = "string_value"
field_list = field_name [, field_name]*
number = integer | decimal
boost_value = integer | float
boolean = true | false
scalar_value = "string_value" | number | boolean
Detailed parameters
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 search. Query options can be set. |
| knn | Can be used separately for single-path or multi-path vector search, and together with the query parameter for hybrid search. |
|
| rank (optional) | The fusion and rough ranking strategy for multi-retrieval results. Supports parameters rrf and weighted_sum (including normalizer). If not specified, the default fusion strategy (rrf) is used. |
|
| rerank (Optional) | After coarse ranking, the AI Rerank model performs fine-ranking on the candidate documents. It must be used in conjunction with query or knn. For parameters, see rerank in the Detailed Parameters section below. |
|
| aggs (optional) | Specifies the aggregation statistics. Currently, only single aggregation is supported. The aggregation types are terms and cardinality. |
|
| collapse (optional) | Groups the search results by specified fields, retaining only the record with the best sort score for each group. | |
| sort (optional) | Specifies the sort key. Single-field or multi-field sorting is supported. If not specified, the records are sorted by the fusion score (__score) by default. |
|
| from (optional) | Specifies which row to return from the search result set. If not specified, the first row is returned by default. This parameter must be used together with the size parameter. |
|
| size (optional) | Limits the number of rows returned in the final result.
|
|
| bool | must | Required. The 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, requires scoring. 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. Conditions that are not met are not scored and are converted into a 'NOT' expression. Multiple 'must_not' conditions are connected by 'AND'. When boolean logic is required internally, a bool expression must be nested. Multiple conditions within a bool expression are combined by default using the AND logic. A bool expression must contain at least one positive condition (must / should / filter). A query with only one must_not condition is not supported. Exceptions: parent domain negation (the outer bool.must_not directly wraps nested) and sub-domain negation (nested.query.bool.must_not directly contains the negative condition) are allowed to be used separately; when negating a sub-domain, must_not must be directly under nested.query.bool. Cross-layer nesting of bool is not allowed. For the semantics of parent domain/sub-domain negation in nested queries, see "The semantics of must_not in nested queries" below. |
|
| filter | Must be met. Does not contribute to the score. Converted into an 'AND' expression. When boolean logic is required internally, a bool expression must be nested. Multiple conditions within a bool expression are combined by default using the AND logic. | |
| minimum_should_match (Optional) | Applies to the should clause and must be written at the same level as should. It specifies the minimum number of should clauses that must be met for a match (where N is the number of should clauses). The bool-level minimum_should_match value can be an integer or a string (for example, "7" or "60%"). For details, see the minimum_should_match section below.
NoteThis parameter is supported starting with V4.6.2.1. |
|
| boost (optional) | The query weight. For more information, see the description of the boost parameter below. | |
| Scalar query (scalar_term) | range | Range search, used with gte, gt, lte, or lt. The field_name parameter is required. When placed in bool.must/should or at the top-level query, it supports boost scoring (1 point for each matched condition, multiplied by the number of chained boost conditions); when placed in filter/must_not or knn.filter, it does not contribute to scoring. |
| term | Exact match. Supports the abbreviated form {"field": value} or the object form with boost, {"field": {"value": ..., "boost": ...}}. The scoring rule is the same as range. |
|
| terms | Exact multi-value match. You can specify boost on the terms object. The scoring rule is the same as that of range. |
|
| wildcard | Wildcard fuzzy search is supported, which is equivalent to using the LIKE ... ESCAPE '\\' clause in SQL.* represents any multiple characters (equivalent to % in SQL), and ? represents any single character (equivalent to _ in SQL).There are three usage methods: directly writing a wildcard string (for example, {"wildcard": {"title": "ab*cd?"}}), using the value field (for example, {"wildcard": {"title": {"value": "ab*cd?", "boost": 0.5}}}), or using the wildcard field (for example, {"wildcard": {"title": {"wildcard": "ab*cd?", "boost": 0.5}}}). You can only choose either value or wildcard, not both. An incorrect example is {"wildcard": {"title": {"value": "abc", "wildcard": "cbd"}}}. The case_insensitive, rewrite, and _name parameters are not supported.The boost parameter is supported. |
|
| Nested query | path | Nested path, required. The format is column name.json path (excluding the root path $), for example, j1_comments. It must correspond to an entry in the NESTED_PATHS list in the search index DDL. The field path in the inner query (such as j1.comments.author) must be a subpath of this path (j1.comments) and must not contain the array indexing symbol []. For details on how the path naming differs from the DDL, see the "Nested query path description" section below. |
| query | The inner query object, which is required. It is used to combine filter conditions on the same array element. It supports term, terms, range, bool, and further nested nested. It does not support match, match_phrase, query_string, or multi_match, nor does it support JSON expressions such as JSON_CONTAINS, JSON_OVERLAPS, or JSON_MEMBER_OF. |
|
| Full-text query (fulltext_term) | match | Full-text match, performing term matching on a single column. The corresponding column must have a single-column full-text index (FTS_INDEX_TYPE = MATCH or a type equivalent to the default). It supports the complete object form {"field":{"query":"...","operator":"OR|AND","minimum_should_match":...,"boost":...}}, or the simplified form when the optional sub-segments are omitted {"field":"query string"}. The query string is tokenized by the tokenizer or text analyzer used by the index. Repeated occurrences of the same keyword only increase its weight and do not change whether a match is found. The operator specifies the logic between keywords. It is optional, with a default of OR. OR means any keyword should be matched; AND means all keywords must be matched. minimum_should_match takes effect only when operator is OR (including the default). It conflicts with AND semantics, so they cannot be used together. For details, see the minimum_should_match section below. |
| match_phrase | Full-text match, performing phrase matching on a single column. For a synchronous full-text index, you must specify FTS_INDEX_TYPE = PHRASE_MATCH for the corresponding column; for an asynchronous full-text index, this type is not required, and the query syntax is the same as for synchronous indexing. The match_phrase feature for asynchronous full-text indexes is supported starting from V4.6.2.1. It supports the full object form {"field":{"query":"...","slop":n,"boost":...}} or the simplified form {"field":"phrase_string"}. Phrases are tokenized by the index's tokenizer or text analyzer. If a phrase contains a stopword, any token can be matched at that position. slop specifies the maximum allowable offset between tokens, with values being integers from [0, INT32_MAX]; 0 indicates an exact match, while positive integers indicate a fuzzy match (optional, default is 0). |
|
| query_string | Full-text match. Use the operator ^ on multiple columns to extend the query semantics. First, split the query into multiple groups using the operator. Then, match each group against the corresponding columns and summarize the results by column. Finally, aggregate the scores of all groups.Among the full-text search parameters, only query_string supports token-level weighting: you can use ^ followed by a positive floating-point number in the query to assign a weight to that keyword group.The parameter must not contain reserved words (case-insensitive): OR, AND, NOT, or TO. It must also not contain the following characters: + - & | ! = < > ( ) [ ] { } " ~ * ? : \ / (these are shown as literal constraints in the documentation but must be properly escaped in the actual JSON). For more information about the optional parameter minimum_should_match, see the minimum_should_match section below. |
|
| multi_match | Full-text match, performing term matching across multiple columns in a field-centric manner: Each column scores the keywords separately and then the scores are aggregated. All columns must be single-column full-text indexes with consistent character sets, tokenizers, or text analyzers.multi_match does not support token-level weights; it only supports weight settings at the column level and the query level:
minimum_should_match is described in the minimum_should_match section below. |
|
| Common parameters for full-text queries | fields | The list of columns for multi_match or query_string. You can add ^ to a column name followed by a positive floating-point number to specify the column weight, with a default value of 1.0. If the same column name appears multiple times, the weight from the last occurrence prevails. |
| query | The query string for multi_match or query_string; the search content for match or match_phrase is written in the query column (or directly as a column value string in its simplified form) under the respective column object. Keyword repetition only affects the weight. |
|
| minimum_should_match (Optional) | The minimum number of matches. Starting from V4.6.2.1, this parameter for full-text queries supports not only integer values but also string types and extended semantics (such as percentages and combinations). It can be used in match, multi_match, query_string; for usage in bool, see the bool parameter table.It takes effect only in OR semantics for full-text queries; it conflicts with AND semantics, so they cannot be used together. For details, see the minimum_should_match section below. |
|
| boost (optional) | The query weight. For more information, see the description of the boost parameter below. | |
| type (Optional) | The matching mode for multi_match or query_string. best_fields is supported, indicating to take the maximum value; most_fields is supported, indicating to sum. cross_fields and phrase are not supported. If not specified, the default is best_fields. |
|
| default_operator (optional) | query_string only. The default logical operator between groups and between keywords within a group is OR, which means any group or keyword must be matched. The default operator is OR. When set to AND, you cannot also specify minimum_should_match. |
|
| operator (optional) | The keyword logic for match and multi_match: OR or AND. Optional. Default is OR. When set to AND, minimum_should_match cannot be specified at the same time. |
|
| Query options (search_options) | ||
| query_dop (optional) | Configures the parallel execution within partitions for the hybrid search query recall path. Parallelism is only supported for full-text/scalar paths (vector paths are not supported). Each query recall path can have only one query_dop set, which must be an integer in the range [1, 128]. If not specified, adaptive DOP (Auto DOP) is used, where the system automatically selects the query parallelism based on CPU load. When explicitly set to 1, no parallelism is performed within the path. When set to 2 to 128, the data is split into ranges and requests are made with the corresponding parallelism. Out-of-range values will cause an error during JSON parsing. This setting takes effect only when hybrid search parallel execution is enabled.
NoteAdaptive DOP (Auto DOP) is supported starting from V4.6.2.1. |
|
| knn (Vector Search) | ||
| field | The name of the vector search column. | |
| k | The number of nearest neighbors returned. Value range: [1, 16384]. |
|
| query_vector | Specifies the search vector. | |
| num_candidates (Optional) | The number of candidate sets specified for vector search, equivalent to ef_search. It is an integer with a range of [k, 10000] (out-of-range values will cause an error). When filter_mode is post or post-index-merge, this parameter serves as an initial budget rather than an absolute upper limit. When set together with ef_search, the priority and detailed usage are described in the "num_candidates and ef_search" section below. |
|
| search_options (Optional) | Advanced options for vector search, including subkeys such as ef_search, refine_k, and filter_mode. ef_search is an integer with a value range of [1, 10000]. refine_k adjusts the rearrangement ratio of quantized vector indexes. It is a floating-point number with a value range of [1.0, 1000.0]. It can still take effect when set together with num_candidates. filter_mode controls the execution path of vector queries with filter conditions. It is a string. For values and meanings, see the "filter_mode" description below. It may be printed in numeric form in the execution plan (for example, filter_mode=0). |
|
| filter (optional) | The filter condition within the vector branch, which can include scalar expressions (including wildcard). |
|
| similarity (optional) | Specifies the filter conditions for vector similarity calculation. | |
| boost (optional) | The query weight. For more information, see the description of the boost parameter below. | |
| rank (bold) | rrf | RRF (Reciprocal Rank Fusion) is a sorting strategy used to fuse and rank multiple query results during hybrid search. |
| weighted_sum | Weighted sum fusion strategy. You can configure rank_window_size and normalizer. |
|
| normalizer (optional) | Only weighted_sum supports this. The value minmax or none is used to normalize the scores before weighting. If not specified, it defaults to none. |
|
| rank_window_size (optional) | Coarse Ranking Fusion Window Size: The number of candidate items participating in fusion for each recall path during multi-path fusion. A larger value may indicate better relevance but higher performance overhead. After fusion ranking, the final output is truncated by size.
|
|
| rank_constant (optional) | This value controls the influence of each document pair in the single result set returned by each query on the final sorted result. A larger value indicates that documents with lower rankings have a greater impact on the final result. The default value is 60. | |
| rerank (fine ranking) | model | Required. The name of the reranking model. The value must be in the provider/model format (for example, aliyun-dashscope/gte-rerank-v2). You must register the provider configuration by using the REGISTER_PROVIDER statement first. |
| field | Required. The name of the text column involved in precise ranking. The column must exist in the query table and be of type CHAR, VARCHAR, or TEXT. |
|
| query | Required. The query string used to compare relevance with the document text. | |
| rank_window_size (optional) | Refinement Window Size: Specifies how many documents to retrieve from the pre-ranked results for re-ranking using the Rerank model. A larger value may yield better performance but incurs higher model invocation overhead.
|
|
| type (Optional) | The implementation type of precision reranking. The default value is model (which calls an external reranking model). |
|
| aggs (aggregation) | agg_name | The name of the bucket (a user-defined key). In the terms scenario, you can directly reference this name in the SELECT list to indicate the count for the corresponding group. In the cardinality scenario, it indicates the deduplication count. |
| terms | Groups documents by the specified field and counts the number of documents in each group. Valid parameters: field (required), which specifies the field name for grouping; size (optional, default: 10), which specifies the number of documents returned for each group. The value must be an integer in the range [1, INT32_MAX]. min_doc_count (optional, default: 1), which specifies the minimum number of documents in each group. The value must be an integer in the range [1, INT32_MAX]. order (optional, default: {"_count":"desc"}), which specifies whether to sort the results by the number of documents or the field value. The value must be {"_count":"asc|desc"} or {"_key":"asc|desc"}. |
|
| cardinality | Perform deduplication and counting by field. Supported parameter: field (required), which specifies the name of the field for deduplication. |
|
| field | The name of the statistical field for terms/cardinality. Full-text index columns, vector columns, and LOB, JSON, or Geometry types are not currently supported. NULL values are excluded from statistics. |
|
| order (optional) | Only terms supports this. You can configure {"_count":"asc|desc"} or {"_key":"asc|desc"}. When sorting by _count and the counts are the same, the items are sorted in ascending order by _key. |
|
| collapse (collapse) | field | Fold field name (required). Group by this field, retaining only the record with the optimal score for each group. Currently, full-text index columns, vector columns, and LOB/JSON/Geometry types are not supported; __score is also not supported. NULL values form their own independent group. Can be used together with sort. If sort is not specified, records are folded in descending order by __score; size is trimmed after folding. |
| sort (sorting) | sort | The list of sort keys, which take effect in the order of the array. Object notation (such as {"price":{"order":"desc"}}) and abbreviated notation (such as "price", which is equivalent to ascending order by this field) are supported. |
| field_name | The name of the column to sort by. Supports regular columns and the pseudo-column __score. |
|
| order (optional) | The sorting direction. Valid values: asc and desc. Default value: asc. |
|
| missing (optional) | Controls the sorting position of NULL values. Valid values: "_first", "_last", or a specific value (NULL is replaced with this value for sorting). Default: "_last". |
Description of Weight Operators
The weight operator ^ is used to specify the weight at the column level or token level in a full-text query statement. The syntax is column name^weight or keyword^weight, for example, "title^2.0". Supported parameters:
- Column-level weights are supported only for
multi_matchandquery_string. - Only
query_stringsupports per-token weights.
The token-level weight operator specifies the weight of the keyword to its left (up to the space). Keywords without a weight operator have a default weight of 1.0. A keyword associated with a weight operator or a sequence of consecutive keywords without weight operators forms a group.
{
"query": {
"query_string": {
"fields": ["title^2.0", "content"],
"query": "Honeydew pitaya, jelly orange^1.5, autumn moon pear^1.2, blueberry, mangosteen"
}
}
}
As shown in the preceding example, when querying the term "Red Heart Pitaya Jellied Orange^1.5 Autumn Moon Pear^1.2 Blueberry Mangosteen", it is divided into four groups: Red Heart and Pitaya with a default weight of 1.0; Jellied Orange and Orange with a specified weight of 1.5; Autumn Moon Pear and Pear with a specified weight of 1.2; and Blueberry and Mangosteen with a default weight of 1.0.
Weight operators can be used together with the boost parameter. For specific examples, see the boost parameters section below.
Detailed description of boost parameters
The boost parameter specifies the weight of a query condition in the final relevance calculation. When a scalar condition is met, it scores 1 point, which is then multiplied by the cascaded boost values. If not specified, the default is 1. Cascading refers to the way the boost parameters are calculated by multiplying nested levels when contributing to the score, for example, bool.boost × subquery boost.
Expressions that support
boostboolqueries.- Full-text queries (such as
match,match_phrase,query_string, andmulti_match). - Vector queries (single/multi-way
knn). - Scalar query:
term/range/terms/wildcard. - JSON/ARRAY scalar expressions.
nestedqueries.
Whether to participate in scoring
Query positionIncluded in__scoreTop-level queryclauseYes bool.mustandbool.shouldYes. They are included only if their outer expression participates in scoring, for example, in bool.filter.bool.must, the innermustdoes not contribute to the score.bool.filterandbool.must_notNo knn.filterNo bool nested inside filterormust_notNo (this booland its subqueries do not score points)Value constraints
- The column weights and term weights for
boostqueries that are not top-level full-text queries,boostqueries that are not top-level full-text queries, and full-text queries must be greater than0. - For other cases, the
boostrequirement is greater than or equal to0.
- The column weights and term weights for
Syntax examples
a. Scalar
rangewithboost:{ "range": { "c1": { "gt": 1, "boost": 2.1 } } }b. Scalar
termwithboost:-- Abbreviation. The default boost is 1. { "term": { "c1": 1 } }-- Object format { "term": { "c1": { "value": 1, "boost": 2.35 } } }c. Scalar
termswithboost:{ "terms": { "c1": [1, 3, 5], "boost": 3.14 } }d.
wildcardwithboost:-- Short form. The default boost is 1. { "wildcard": { "content": "*ocea*" } }-- Object format { "wildcard": { "content": { "value": "*ocea*", "boost": 1.8 } } }e. JSON/single-level array expressions with
boost:{ "json_contains": { "meta_json": { "candidate": "\"mysql\"", "path": "$.tags", "boost": 3.14 } } }{ "array_contains": { "tag_array1": { "arg": "ios", "boost": 2.1 } } }f. Query-level
boost(bool):{ "bool": { "must": [{"range": {"c1": {"gt": 1, "boost": 2.1}}}], "boost": 9.131 } }In the example above, when the
rangewithinmustis satisfied, the score = 1 × 2.1 × 9.131.g. Column-level weight (
query_string):{ "query_string": { "fields": ["product_name^2.0", "description^1.0"], "query": "gaming keyboard", "boost": 1.5 } }h. Vector query
boost(knn):{ "knn": { "field": "vector", "k": 5, "query_vector": "[1,2,3]", "boost": 1.5, "filter": [ {"range": {"c1": {"gt": 1, "boost": 2.1}}} ] } }knnitself contributes to scoring; scalar conditions withinknn.filterdo not contribute to scoring.
num_candidates and ef_search
This section describes the priority between num_candidates and ef_search.
The priority of num_candidates over ef_search is as follows:
Settings |
Effective behavior |
|---|---|
Set only num_candidates |
Effective, and ef_search = num_candidates |
Set only search_options.ef_search |
By ef_search |
Set both parameters to num_candidates and search_options.ef_search |
num_candidates Priority, Final ef_search equal to num_candidates |
| None | Keep Default ef_search system parameters / session parameters |
filter_mode
filter_mode is a parameter in search_options that controls the execution path for vector queries with filter conditions. Valid values are as follows:
Valid values |
Meaning |
|---|---|
pre |
Adaptive Prefiltering |
pre-knn |
Pre-filtering + KNN |
pre-brute |
Pre-filtering + Brute-force search |
post |
Expression-based Iterative Filtering |
post-index-merge |
Iterative filtering based on the index-merge framework |
wildcard scalar query
This section provides supplementary information about wildcard scalar queries, including usage locations and indexing capabilities.
Supported usage locations:
- In the top-level
query - In
bool.must - In
bool.should - In
bool.filter - In
bool.must_not - In
knn.filter(semantics consistent withbool.filter)
The index capabilities are as follows:
Index type |
Supported |
|---|---|
| Normal index on VARCHAR column | Supported (in non-precise queries)LIKE(can be accessed via index) |
| prefix index | Not supported |
| Full-text index | Not supported |
| JSON SEARCH INDEX | Not supported |
| JSON CAST Index | Not supported |
minimum_should_match
Note
Starting from V4.6.2.1, in addition to supporting the integer type for minimum_should_match, match, multi_match, and query_string also support string types (including percentages and combinations), and support bool level minimum_should_match.
minimum_should_match (MSM for short) indicates the minimum number of matches. That is, it specifies the minimum number of alternative cells that must be matched for a document to be considered a match.
Typical scenarios
There are generally two major scenarios for MSM:
- Full-text search
ORscenarios: This scenario occurs when theoperatorinmatchormulti_matchisORor not set (the default isOR), or when thedefault_operatorinquery_stringisORor not set. The MSM can be written in thematch,multi_match, orquery_stringclause, with a default value of1. Whenoperatorordefault_operatorisAND, all optional units must match, which conflicts with the semantics of MSM; therefore, they cannot be used simultaneously. MSM takes effect only in the aforementionedORscenarios. - Scenario where
boolclauses are optional: This scenario occurs when aboolclause contains one or moreshouldclauses. Each element in theshouldarray is an optional subquery. An MSM is written within thisboolobject (parallel to theshouldclauses) to specify the minimum number ofshouldclauses that must be true for a match to be considered valid. The default value of MSM varies under different conditions:- If there is only a
shouldclause and nomustorfilterclauses, the default value is1. - When a
mustorfilterclause is present, the default value is0. - If there are no
shouldclauses and onlymustorfilterclauses, no error is reported, but the clause has no practical effect.
- If there is only a
To calculate the MSM, you must first determine N, which is the total number of selectable units. Regardless of whether the MSM is a positive integer, negative integer, or percentage, it is converted relative to N to obtain the actual minimum hit count. The N values corresponding to each query clause type are as follows:
Types of query clauses |
N Value |
|---|---|
match / multi_match |
Number of Tokens after Query Tokenization |
query_string |
The number of groups after splitting by the token-level weight ^ in query (see Description of weight operators above for the rules; this is not the number of tokens after tokenization). |
bool |
Within the object should Number of clauses |
Note
The units counted by MSM are collectively referred to as alternatives in the following text. Among them, match and multi_match are tokens, query_string is a group after segmentation by ^, and bool is the should clause.
Single-value matching logic
The value of N can be an integer or a string. If the string can be parsed as an integer, it behaves the same as an integer; otherwise, it is parsed as a string. Unless specified otherwise in this section, the example assumes N = 10.
Positive Integer
Represents the minimum number of selectable units that must be met (the final calculated result in this section is set as m). The value range is [0, N], and m=min(MSM, N).
MSM Value |
Calculation (N=10) |
Note |
|---|---|---|
2 or "2" |
m = min(2, 10) = 2 | At least 2 hits |
0 |
m = min(0, 10) = 0 | Special handling based on query type:
|
12(≥ N) |
m = min(12, 10) = 10 | Equivalent to all hits (operator = AND) |
"minimum_should_match": 2
Negative Integer
It indicates the maximum number of optional units that are allowed to be unmatched (denoted as m in the final calculation of this section). The value of m is calculated by subtracting |MSM| from N, using the formula m = N - |MSM|.
MSM Value |
Computing |
Note |
|---|---|---|
-2(N=10) |
m = 10 - | −2 | = 8 | At least 8 hits |
-4(N=3) |
m = 3 - | −4 | = −1 | -1 rounded to 0 (see Positive integers for logic), and the system automatically treats it as 1 available unit. |
"minimum_should_match": -2
Positive Percentage
It indicates the minimum percentage of selectable cells that must be hit. The value is rounded down to the nearest integer based on the percentage of N, calculated by the formula m = floor(N × MSM / 100).
MSM Value |
Calculation (N=10) |
Note |
|---|---|---|
"75%"(less than 100%) |
m = floor(10 × 75 / 100) = 7 | At least 7 hits |
"100%"(100%) |
m = floor(10 × 100 / 100) = 10 | All Hits |
"150%"(Greater than 100%) |
m = floor(10 × 150 / 100) = 15. When m exceeds N, the system automatically truncates it to N, that is, m = 10. | Equivalent to all hits, no error will be reported |
"minimum_should_match": "75%"
Negative percentage
It indicates the allowable proportion of missing optional units, rounded down to the nearest integer as a percentage of N (for example, trunc(-1.5) = -1). In the calculation formula of this section, MSM represents the signed value in the percentage (for example, MSM = −25 in "-25%"), and the formula is m = N + trunc(N × MSM / 100).
MSM Value |
Calculation (N=10) |
Note |
|---|---|---|
"-25%"(The value is greater than −100%, that is, the absolute value is less than 100%.) |
m = 10 + trunc(10 × (−25) / 100) = 10 + trunc(−2.5) = 10 + (−2) = 8 | At least 8 hits |
≤ −100%(For example, "-150%") |
m = 10 + trunc(10 × (−150) / 100) = 10 + (−15) = −5 | If the median value is ≤ 0, the system automatically treats it as one available unit without returning an error. |
"minimum_should_match": "-25%"
Combined operation logic
MSM can also be written as a string with operators, in the format condition_threshold < MSM_value. For example, "3<90%" means that when the total number of selectable units N is greater than the condition threshold 3, MSM is calculated as 90%. For scenarios where N is less than or equal to 3, it is fixed at 100% (all hits). Multiple segments are separated by spaces. The system checks from left to right. Condition threshold intervals are open on the left and closed on the right (the leftmost is a closed interval), as shown in the examples below.
MSM Value |
Calculation (N=10) |
Note |
|---|---|---|
"3<90%" |
When N = 10, it falls within the interval (3, +∞). According to the rule that 90% Calculation |
m = 10 × 0.9 = 9 |
"15<90%" |
N = 10 falls in the [0, 15], press 100% Calculation |
m = 10 |
"minimum_should_match": "2<60%, 5<70%, 9<80%"
When you explicitly specify three percentage segments for a string, the system automatically adds an implicit default segment (treating N as 100% when it does not exceed the first threshold). The complete interval is as follows (left-open, right-closed; the leftmost is a closed interval):
- The number of selectable units is in the range of [0, 2], with an application threshold of 100%.
- Number of selectable cells is in the range of (2, 5], with an application threshold of 60%.
- When the number of selectable units is in the range (5, 9], use the application's 70% threshold.
- Number of selectable units is in the range (9, +∞), with an application threshold of 80%.
Therefore, for different values of N, the calculation results are as follows:
N Value |
Scanning Process |
Result |
|---|---|---|
| N ≤ 2 | If the number of available units in the first segment is greater than or equal to 2, the service stops directly. | m = N × 100 / 100 = N |
| 2 < N ≤ 5 (for example, N = 5) | 5 is in the interval (2, 5]. Calculated by 60%: m = 5 × 60 / 100 = 3. The next available unit-count range is (5, 9]. Since 5 is not in this range, the process stops. |
m = 3 |
| 5 < N ≤ 9 (for example, N = 9) | 9 in the interval (5, 9], by 70% Calculation: m = trunc(9 × 70 / 100) = 6. The next available unit range is (9, +∞). Since 9 is not within this range, the process stops. |
m = 6 |
| N > 9 (for example, N = 10) | 10 in the interval (9, +∞), by 80% Calculation: m = 10 × 80 / 100 = 8 |
m = 8 |
Nesting and Hierarchical Structure
Based on the preceding rules, it is important to understand that minimum_should_match applies only to the query level where it is defined. Constraints at each level are independent of one another and are evaluated separately at each level during nested queries, without overriding each other.
Placement |
Constraint objects (N) |
|---|---|
match / multi_match internal |
Number of tokens after word segmentation in this query |
query_string internal |
Number of groups after boosting segmentation (see [Weight operator description](#Weight operator description) for rules) |
bool and should Same Level |
should Number of clauses |
For example, if the outer bool condition sets MSM to 2 (at least 2 of the 3 should conditions must be met), and within one of the should conditions, match is set to MSM = 2 (at least 2 of the 3 terms in this match must be matched), then both layers of conditions must be met simultaneously. See the "Minimum match number example" section for an illustration.
Nested queries
This section provides additional information on path naming in nested queries, the differences between nested queries and DDL statements, and the semantics of must_not.
The following table describes the differences between path naming and DDL.
Context |
Format |
Example |
|---|---|---|
DDL NESTED_PATHS |
$.path, excluding column name |
$.comments |
dsl_string nested.path |
column name/path, excluding $ |
j1.comments |
| Field path of inner query in dsl_string | column name.full path |
j1.comments.author |
Nested queries can appear in the top-level query or in any clause of a bool. Multiple conditions in an inner bool must match the same array element; for multi-layer nested queries, further nested clauses are required to query deeper fields. For details on the DDL-side path matching rules (how arrays and objects correspond to declared paths), see the "NESTED_PATHS Path Matching" section in CREATE INDEX.
The negation domain is determined by the relative position of must_not relative to nested:
Parent document (a row in a table / the entire JSON)
└── comments[] ← child elements affected by nested
├── {author: alice}
└── {author: bob}
- Parent domain negation: The outer
bool.must_notdirectly wrapsnested, for example:
# Indicates that no Alice characters are allowed in the comment.
{
"bool": {
"must_not": [{"nested": {"path": "comments", "query": {"bool": {"must": [{"term": {"comments.author": "alice"}}]}}}}]
}
}
The parent document matches if no nested element satisfies the negation condition. In other words, as long as one nested element meets the condition, the document does not match. If the nested array is empty, or the document lacks this path field, it will also match.
- Child domain negation:
must_notis placed undernested.query.bool, for example:
# There must be at least one comment other than Alice's.
{
"nested": {
"path": "comments",
"query": {"bool": {"must_not": [{"term": {"comments.author": "alice"}}]}}
}
}
The parent document matches if at least one nested element does not satisfy the negated condition. In other words, as long as one nested element does not meet the negation condition (for example, the author is not alice), the document matches. If all nested elements satisfy the negation condition, it does not match. If the nested array is empty, or the document lacks this path field, it will also match.
Aggregation pseudo-columns
HYBRID_SEARCH provides the following pseudo-columns in the SELECT list. Pseudo-columns are only valid in hybrid search syntax and are not part of the general MySQL SELECT expression.
Pseudo column name |
Meaning |
Application scenarios |
|---|---|---|
__score |
Final score after fusing search results from multiple sources | Can be referenced in any mixed-search query for returning rows |
agg_name |
Aggregated bucket count (the name must be consistent with the custom bucket key in aggs) |
Available only when a same-named aggs variable is declared in dsl_string |
For aggregation over terms, the pseudo-column agg_name returns the number of documents corresponding to that group key (equivalent to COUNT(*)). For aggregation over cardinality, the pseudo-column agg_name returns the deduplication count of that field (equivalent to COUNT(DISTINCT field)). You can also write COUNT(DISTINCT field) in SELECT, but field must match cardinality.field.
Count queries (count(*))
SELECT count(*) is used to count the total number of documents in the union of search results from all paths. Single-path searches return the number of hits for that path index, while multi-path searches count the union of row keys after deduplication. Counting is done at the index layer, without scoring or accessing the primary table.
size, from, sort, aggs, and collapse do not affect the count result; min_score does. When both rank and min_score > 0 are specified, only documents within the fusion window and with a score meeting the threshold are included in the count.
The following example sets "size": 1, but count(*) still returns the actual total number of full-text hits, not 1:
SELECT count(*)
FROM HYBRID_SEARCH(
TABLE doc_table,
'{
"query": {
"match": {"content": "oceanbase"}
},
"size": 1
}'
);
Examples
The following examples illustrate complex syntax.
Set num_candidates and ef_search simultaneously
-- When both num_candidates and search_options.ef_search are specified: num_candidates takes precedence.
SELECT c1 FROM HYBRID_SEARCH(TABLE doc_table, '{
"knn": {
"field": "vector",
"k": 10,
"num_candidates": 2000,
"query_vector": "[0.712338,0.603321,0.133444]",
"search_options": {
"ef_search": 101,
"refine_k": 5.6,
"filter_mode": "pre"
}
}
}');
Fuzzy match example (wildcard)
This example demonstrates common scenarios for wildcard scalar queries, including prefix matching on regular columns, JSON path matching, filtering conditions in knn.filter, and combined filtering with bool.must / bool.should. For the table creation and data of the sample tables doc_table and doc_json_array, see Hybrid search.
-- Match by common column prefix (doc_table.content)
SELECT c1, content FROM HYBRID_SEARCH(TABLE doc_table, '{
"query": {"wildcard": {"content": "oceanbase*"}}
}');
-- JSON Path (doc_json_array.doc_json.name)
SELECT id, title FROM HYBRID_SEARCH(TABLE doc_json_array, '{
"query": {"wildcard": {"doc_json.name": "doc*"}}
}');
-- As knn.filter (doc_table)
SELECT c1 FROM HYBRID_SEARCH(TABLE doc_table, '{
"knn": {
"field": "vector",
"k": 3,
"query_vector": "[1,2,3]",
"filter": [{"wildcard": {"content": "oceanbase*"}}]
}
}');
-- bool.must / should combination (doc_table)
SELECT c1, query, content FROM HYBRID_SEARCH(TABLE doc_table, '{
"query": {
"bool": {
"must": [
{"term": {"c1": 2}},
{"bool": {
"should": [
{"wildcard": {"query": "*world*"}},
{"wildcard": {"content": "*mysql*"}}
],
"minimum_should_match": 1
}}
]
}
}
}');
Minimum match count example (minimum_should_match)
The following examples correspond to the sections of minimum_should_match above. For the table creation and data of the sample table doc_table, see the related document Hybrid search at the end of this topic.
-- Positive percentage (match): N=10, "75%" → m=floor(10×75/100)=7
SELECT c1, content FROM HYBRID_SEARCH(TABLE doc_table, '{
"query": {
"match": {
"content": {
"query": "oceanbase mysql oracle postgres redis mongodb tidb starrocks elasticsearch database",
"operator": "OR",
"minimum_should_match": "75%"
}
}
}
}');
-- Combination operation (match): N=10, "2<60%, 5<70%, 9<80%" → N>9 based on 80% → m=8
SELECT c1, content FROM HYBRID_SEARCH(TABLE doc_table, '{
"query": {
"match": {
"content": {
"query": "oceanbase mysql oracle postgres redis mongodb tidb starrocks elasticsearch database",
"operator": "OR",
"minimum_should_match": "2<60%, 5<70%, 9<80%"
}
}
}
}');
-- bool.should: N=3 (3 shoulds), MSM=2 → At least 2 shoulds hold
SELECT c1, content FROM HYBRID_SEARCH(TABLE doc_table, '{
"query": {
"bool": {
"should": [
{"match": {"content": "oceanbase"}},
{"match": {"content": "mysql"}},
{"match": {"content": "database"}}
],
"minimum_should_match": 2
}
}
}');
-- Nested: The outer bool (N=3, MSM=2) and the inner match (3 tokens, MSM=2) are evaluated separately. Both must be satisfied simultaneously.
SELECT c1, content FROM HYBRID_SEARCH(TABLE doc_table, '{
"query": {
"bool": {
"should": [
{
"match": {
"content": {
"query": "oceanbase mysql database",
"operator": "OR",
"minimum_should_match": 2
}
}
},
{"match": {"content": "vector"}},
{"match": {"content": "search"}}
],
"minimum_should_match": 2
}
}
}');
-- query_string: The query is divided into 10 groups using the ^ operator (N=10). For example, "3<90%" → m=floor(10×90/100)=9.
SELECT c1, content FROM HYBRID_SEARCH(TABLE doc_table, '{
"query": {
"query_string": {
"fields": ["content"],
"query": "oceanbase^1 mysql^1 oracle^1 postgres^1 redis^1 mongodb^1 tidb^1 starrocks^1 elasticsearch^1 database^1",
"minimum_should_match": "3<90%"
}
}
}');
Nested query example
The DDL and test data for the sample table search_idx_nested are as follows (subsequent nested query examples are based on this table; multi-level nesting examples rely on the $.comments.replies declaration):
CREATE TABLE search_idx_nested (
k INT,
j1 JSON,
SEARCH INDEX idx_nested (
j1 WITH (
NESTED_PATHS = ('$.comments', '$.comments.replies')
)
)
) ORGANIZATION = HEAP;
INSERT INTO search_idx_nested VALUES
-- k=1: Alice scores 4 points, Bob scores 5 points (with different comments); no reply from Bob to Alice
(1, '{"title":"doc1","comments":[{"author":"alice","rating":4,"replies":[]},{"author":"bob","rating":5,"replies":[{"author":"bob","text":"good"}]}]}'),
-- k=2: Alice scores 5 points on the same comment, and Bob is among the replies.
(2, '{"title":"doc2","comments":[{"author":"alice","rating":5,"replies":[{"author":"bob","text":"good"},{"author":"carol","text":"well"}]},{"author":"bob","rating":4,"replies":[]}]}'),
-- k=3: No Alice
(3, '{"title":"doc3","comments":[{"author":"bob","rating":4},{"author":"carol","rating":3}]}'),
-- k=4: All comments are from alice
(4, '{"title":"doc4","comments":[{"author":"alice","rating":5}]}'),
-- k=5: comments is empty
(5, '{"title":"doc5","comments":[]}');
Find the same comment where the author is alice and the rating is greater than or equal to 5:
SELECT k, j1 FROM HYBRID_SEARCH(
TABLE search_idx_nested,
'{
"query": {
"nested": {
"path": "j1.comments",
"query": {
"bool": {
"must": [
{"term": {"j1.comments.author": "alice"}},
{"range": {"j1.comments.rating": {"gte": 5}}}
]
}
}
}
}
}'
);
The expected results are k=2 and k=4. In k=1, alice and a rating of 5 are not in the same comment, so it does not match.
Multi-level nested query example
If multiple nested paths are declared when creating an index (for example, NESTED_PATHS = ('$.comments', '$.comments.replies')), you must continue to nest nested clauses when querying deeper fields. For example, find the comment where author=alice and there exists a comment with author=bob in its replies:
SELECT k, j1 FROM HYBRID_SEARCH(
TABLE search_idx_nested,
'{
"query": {
"nested": {
"path": "j1.comments",
"query": {
"bool": {
"must": [
{"term": {"j1.comments.author": "alice"}},
{
-- Continue Nested Clauses
"nested": {
"path": "j1.comments.replies",
"query": {
"term": {"j1.comments.replies.author": "bob"}
}
}
}
]
}
}
}
}
}'
);
The outer and inner conditions must apply to the same comment. The expected result is only k=2.
Nested query must_not example
Parent domain negation
SELECT k, j1 FROM HYBRID_SEARCH( TABLE search_idx_nested, '{ "query": { "bool": { "must_not": [{ "nested": { "path": "j1.comments", "query": { "term": {"j1.comments.author": "alice"} } } }] } } }' );The expected results are
k=3andk=5(no alice or empty array).k=1,k=2, andk=4containing alice do not match.Subdomain Negation
SELECT k, j1 FROM HYBRID_SEARCH( TABLE search_idx_nested, '{ "query": { "nested": { "path": "j1.comments", "query": { "bool": { "must_not": [ {"term": {"j1.comments.author": "alice"}} ] } } } } }' );The expected return is
k=1,k=2,k=3, andk=5. Onlyk=4(where all comments are from alice) does not hit.
References
- For more information about this syntax, see Hybrid search