HYBRID_SEARCH

The HYBRID_SEARCH clause uses a JSON string to specify full-text search, vector search, and filtering conditions in a single SELECT statement, and returns rows ranked by a fusion strategy.

Syntax

SELECT select_expr_list
FROM HYBRID_SEARCH(TABLE table_name, dsl_string) [table_alias];

-- The select_expr_list is the SELECT list, which follows the standard SELECT syntax.

Parameters

Global limits and notes

This section lists only syntax limitations. For feature limitations, see Index-based hybrid search (SQL interface) in the References section and read the two sections together.

The description is as follows:

• The top-level query (full-text search) and knn (vector search) operations can have at most three subqueries: one for query, and for knn, one for objects and one per element for arrays. The total number of subqueries cannot exceed three, and at least one full-text or vector search operation must be included.
• Column names in queries are case-insensitive.
• For the query_vector parameter in vector queries, it is recommended to input vectors as strings.
• The min_score parameter is supported only by the SQL interface and not by the PL interface.
• Each query and each knn is an independent search path, and their filters do not affect each other. If a filtering condition is required for a particular query path, the corresponding filter must be specified separately within that path. All search results are merged into a single result set, scored and ranked using a fusion algorithm, and the top size results are returned.
• The rerank parameter must be used with either query or knn. It cannot be used alone. Coarse ranking is performed before reranking, and rerank cannot replace the fusion capability of rank.

The limitations are as follows:

• The rank_feature, es_mode, and _source parameters are not supported.
• You cannot directly use WHERE, ORDER BY, or LIMIT on the same level as HYBRID_SEARCH. If further filtering or sorting is required, process the hybrid search results 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: Subquery first, 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 path of a json element is an array and the element is a json, you cannot specify a path for this json element.
• Scalar query conditions include term, range, terms, wildcard, JSON expressions, and ARRAY expressions. Of these, wildcard contributes to scoring in the top-level query, bool.must, and bool.should. In bool.filter, bool.must_not, or knn.filter, it is used only as a filter. Other scalar query conditions do not contribute to scoring, do not support boost, and cannot be used in the must or should clause of a bool query.
• The boost values of non-top-level full-text queries and bool queries, as well as column and token weights in full-text queries, must be greater than 0. In all other cases, boost must be greater than or equal to 0.
• The multiple full-text columns for multi_match or query_string queries must use the same character set and collation, and the full-text indexes must use the same parser.
• When match, multi_match, or query_string is used for term-level matching, the corresponding full-text column should use FTS_INDEX_TYPE = MATCH (or the default type equivalent to MATCH if not specified). match_phrase applies only to full-text index columns for which FTS_INDEX_TYPE = PHRASE_MATCH has been created.

Syntax of DSL_STRING parameters

dsl_string is a JSON-formatted string. This section describes its syntax in detail. Read it together with the parameter descriptions and examples below.

Syntax

This section describes the meaning and usage rules of BNF (Backus-Naur Form) syntax symbols:

1. Optional elements

   • [ ] indicates that multiple elements are optional in BNF. For example, param_list = param [, param]* means param_list can contain one or more param elements.
   • The [ ] in rank_expression also indicates that the sub-parameter is optional.
   • [, "boost" : boost_value] indicates that this sub-parameter is optional in expressions that support boost. term / range / terms and JSON/ARRAY scalar expressions do not support boost.

2. Array representation

   • [ ] indicates an array in a JSON structure, for example, [condition_list].

3. Alternatives

   • | separates alternatives. For example, param = "query" | "knn" means that param can be either query or knn.

4. Repetition

   • * indicates zero or more repetitions. For example, param_list = param [, param]* indicates that param_list can contain one or more param elements.

5. Requirements for JSON format

   • 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. For parameter descriptions, see the table below.

DSL_STRING skeleton example

SELECT * FROM HYBRID_SEARCH(
  TABLE doc_table,
  '{
    "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
    },
    "from": 0,
    "size": 10
  }'
);

Top-level parameters 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 | search_options} --The query expression or options.
          | "knn"       : {knn_expression}
          | "rank"      : {rank_expression}
          | "rerank"    : {rerank_params} -- Supported starting from V4.6.0 BP1
          | "from"      : number
          | "size"      : number
          | "min_score" : number -- Supported only for the SQL interface.

Query expression structure

Query expression syntax is used to specify full-text and scalar query conditions in a hybrid search, and supports configuring query options.

query_expression = bool_query | scalar_term | fulltext_term

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]
                  | "boost"    : boost_value
    condition_list = query_expression [, query_expression]*

{
  "bool": {
    "must": [
      {
        "match": {
          "content": "oceanbase"
        }
      }
    ],
    "filter": [
      {
        "term": {
          "status": 1
        }
      }
    ]
  }
}

Scalar query structure

This structure defines scalar query expressions, including range_query, term_query, terms_query, and wildcard_query, which represent range queries, exact matches, multi-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

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

terms_query = "terms" : {terms_condition_list}
    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} -- Supported starting from V4.6.0 BP1
    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

Full-text query structure

This structure defines full-text query expressions, including match_query, match_phrase_query, query_string, and multi_match, which represent term-level matching, phrase-level matching, full-text search, and multi-column term-level matching, respectively.

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" : number] [, "boost" : boost_value]

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 term-level weight operator ^. For details, see "Weight operator description" below.
                           | "boost"  : boost_value
                           | "type"   : ("best_fields" | "most_fields")
                           | "default_operator" : ("AND" | "OR")
                           | "minimum_should_match" : number

multi_match = "multi_match" : {multi_match_condition}
    multi_match_condition = "fields" : [field_weight_list]
                         | "query"  : "string_value" -- Does not support keyword-level ^ in the query string.
                         | "boost"  : boost_value
                         | "type"   : ("best_fields" | "most_fields")
                         | "operator" : ("AND" | "OR")
                         | "minimum_should_match" : number
    field_weight_list = field_weight [, field_weight]*
    field_weight = "field_name[^number]"

Query options structure

This structure configures query options such as intra-partition parallelism.

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

This structure specifies vector search parameters, including the vector field, similarity, filter conditions, and weights. It supports both single-vector and multi-vector search.

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 -- Supported starting from V4.6.0 BP1
                 | "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")]

-- Supported starting from V4.6.0 BP1
rerank_params = "model" : "string_value"
              | "field" : "field_name"
              | "query" : "string_value"
              | "rank_window_size" : number
              | "type" : "string_value"

{
  "knn": [
    {
      "field": "vector_a",
      "k": 3,
      "query_vector": "[1,0,0]"
    },
    {
      "field": "vector_b",
      "k": 3,
      "query_vector": "[0,1,0]"
    }
  ]
}

Basic type definitions

The following table describes the basic data types 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 parameter description

Weight operator description

The weight operator ^ specifies a weight at the column level or token level in a full-text query. The syntax is column_name^weight or keyword^weight, for example, "title^2.0". The following forms are supported:

• Column-level weights are supported only for multi_match and query_string.
• Only query_string supports 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": "apple banana orange^1.5 pear^1.2 blueberry mangosteen"
    }
  }
}

As shown in the preceding example, the query string "apple banana orange^1.5 pear^1.2 blueberry mangosteen" is divided into four groups: apple and banana with the default weight of 1.0, orange with a weight of 1.5, pear with a weight of 1.2, and blueberry and mangosteen with the 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. The value must be greater than or equal to 0. If omitted, the default is 1. In the syntax structure above, bool, scalar_term, single-vector search, and multi-vector search (knn) support the boost parameter, subject to the following rules:

1. Expressions that support boost

   • bool queries.
   • Full-text queries (such as match, match_phrase, query_string, and multi_match).
   • Vector queries (single-vector or multi-vector knn).
   • Scalar query: wildcard query.

2. Expressions that do not support boost

   • Scalar query: term / range / terms.
   • JSON or ARRAY scalar expressions.

3. Value constraints

   • The boost values of non-top-level full-text queries and bool queries, as well as column and token weights in full-text queries, must be greater than 0.
   • In all other cases, boost must be greater than or equal to 0.

4. Examples

   a. Query level boost (bool):

   {
     "bool": {
       "filter": [{"term": {"category": "Gaming"}}],
       "boost": 2.0
     }
   }
   

   b. Column-level weights (query_string):

   {
     "query_string": {
       "fields": ["product_name^2.0", "description^1.0"],
       "query": "gaming keyboard",
       "boost": 1.5
     }
   }
   

   c. Vector query boost (knn):

   {
     "knn": {
       "field": "vector",
       "k": 5,
       "query_vector": "[1,2,3]",
       "boost": 1.5
     }
   }
   

num_candidates and ef_search

This section describes the priorities of num_candidates and ef_search.

The priorities of num_candidates and ef_search are as follows:

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:

wildcard scalar queries

This section describes additional information about wildcard scalar queries, including usage scenarios and index support.

Supported usage:

• In the top-level query
• In bool.must
• In bool.should
• In bool.filter
• In bool.must_not
• In knn.filter (semantics consistent with bool.filter)

The index capabilities are as follows:

Examples

The following examples illustrate complex syntax for your reference.

Full-text and vector RRF hybrid search

Create a sample table containing a vector column, create a vector index on it, and create full-text indexes on the two VARCHAR columns.

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 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");

SELECT * FROM HYBRID_SEARCH(
  TABLE doc_table,
  '{
    "query": {
      "match": {"content": "oceanbase mysql"}
    },
    "knn": {
      "field": "vector",
      "k": 5,
      "query_vector": "[1,2,3]"
    },
    "rank": {
      "rrf": {
        "rank_constant": 60,
        "rank_window_size": 10
      }
    }
  }'
);

+------+---------+---------------------------------+----------------------------------+----------------------+
| c1   | vector  | query                           | content                          | __score              |
+------+---------+---------------------------------+----------------------------------+----------------------+
|    1 | [1,2,3] | hello world                     | oceanbase Elasticsearch database |  0.03252247488101534 |
|    2 | [1,2,1] | hello world, what is your name  | oceanbase mysql database         | 0.032266458495966696 |
|    3 | [1,1,1] | hello world, how are you        | oceanbase oracle database        | 0.031754032258064516 |
|    5 | [1,3,2] | real world, how old are you     | redis oracle database            | 0.016129032258064516 |
|    6 | [2,1,1] | hello world, where are you from | starrocks oceanbase database     | 0.016129032258064516 |
|    4 | [1,3,1] | real world, where are you from  | postgres oracle database         |             0.015625 |
+------+---------+---------------------------------+----------------------------------+----------------------+
6 rows in set

Set both num_candidates and ef_search

This example shows setting both num_candidates and ef_search:

-- 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"
    }
  }
}');

Wildcard matching examples

This example demonstrates common scenarios for wildcard scalar queries, including prefix matching on regular columns, JSON path matching, serving as a filter condition in knn.filter, and combined filtering with bool.must / bool.should:

-- Normal column prefix matching
SELECT * FROM HYBRID_SEARCH(TABLE doc_table, '{
  "query": {"wildcard": {"title": "prefix*"}}
}');

-- JSON Path
SELECT * FROM HYBRID_SEARCH(TABLE doc_table, '{
  "query": {"wildcard": {"doc_json.name": "alpha*"}}
}');

-- As a knn.filter
SELECT * FROM HYBRID_SEARCH(TABLE doc_table, '{
  "knn": {
    "field": "vector_col",
    "k": 1,
    "query_vector": "[0.1,0.1,0.1,0.1]",
    "filter": [{"wildcard": {"doc_json.name": "alpha*"}}]
  }
}');

-- bool.must / should combination
SELECT * FROM HYBRID_SEARCH(TABLE doc_table, '{
  "query": {
    "bool": {
      "must": [
        {"term": {"store_id": "20000"}},
        {"bool": {
          "should": [
            {"wildcard": {"phone": "*1399*"}},
            {"wildcard": {"order_no": "*2024061*"}}
          ],
          "minimum_should_match": 1
        }}
      ]
    }
  }
}');

References

• For more information about the syntax, scenarios, and examples, see Index-based hybrid search (SQL interface).