HYBRID_SEARCH

The HYBRID_SEARCH clause specifies a full-text search, vector search, and filter conditions in a SELECT statement by using a JSON string that conforms to the rules in this topic. It returns rows sorted by the hybrid search strategy.

Syntax

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

-- select_expr_list is the SELECT list, which follows the syntax of a regular SELECT statement.

Parameters

Limitations and considerations

This section only lists the syntax limitations. For functional limitations, see the related documentation at the end of this section: Hybrid search (SQL interface). Please refer to this section for a comprehensive understanding.

Considerations:

• At the top level, the combination of query (full-text search) and knn (vector search) can have a maximum of three subqueries: query counts as one; knn as an object counts as one, and if it's an array, each element counts as one. The total number of these three cannot exceed three, and at least one full-text or vector search must be included.
• Column names in queries are case-insensitive.
• For vector queries, it is recommended to input the vector as a string.
• The min_score parameter is only supported in the SQL interface and not in the PL interface.
• Each query and knn are independent search paths, and their respective filter conditions do not affect each other. If a search path requires filtering conditions, the corresponding filter must be specified separately for that path. All search results will be merged into a single result set, scored and sorted based on the fusion algorithm, and the top size results will be returned.

Limitations:

• The rank_feature, es_mode, and _source parameters are not supported.
• Direct use of WHERE, ORDER BY, or LIMIT at the same level as HYBRID_SEARCH is not supported. If further filtering or sorting is needed, the hybrid search results must be used as a subquery first. For example:

  -- Not supported: Using WHERE at the same level 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: Use as a subquery first, then filter
  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 are not supported for pushdown. For example, if the path in json is array, and the elements are json, you cannot specify a path for this json element.
• Scalar queries, including term, range, terms, json expressions, and array expressions, do not support scoring or boost, and cannot be used in must or should clauses of bool.
• The boost parameters for non-top-level full-text queries, boost parameters for bool queries, and the column and term weights for full-text queries must be greater than 0. For other boost parameters, the value must be greater than or equal to 0.
• For multi_match and query_string queries involving multiple full-text columns, the same character set and collation must be used, and the same parser must be specified for the full-text index.
• When match, multi_match, or query_string is used for term-level matching, the corresponding full-text column must use FTS_INDEX_TYPE = MATCH (or the default type equivalent to MATCH if unspecified). match_phrase is only applicable to full-text columns with FTS_INDEX_TYPE = PHRASE_MATCH.

DSL_STRING syntax

dsl_string is a JSON-formatted string. The syntax is described in this section. Please refer to the parameters and examples below for a better understanding.

Syntax

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

1. Optional parameters

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

2. Arrays

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

3. Selection

   • | indicates a selection. For example, param = "query" | "knn" means that param can be either "query" or "knn".

4. Repeated elements

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

5. JSON format requirements

   • All JSON column names and string values must be enclosed in double quotation marks.
   • Numerical values do not need to be enclosed in quotation marks.

Syntax definition

This section provides detailed syntax for dsl_string. For more information about the parameters, see the table below.

DSL 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
      }
    },
    "from": 0,
    "size": 10
  }'
);

Top-level parameter structure

The top-level parameter structure specifies the parameters for hybrid search.

dsl_string = '{param_list}'
    param_list = param [, param]*
    -- At least one of `query` and `knn` must be specified. You can specify both of them in hybrid search.
    param = "query"     : {query_expression}
          | "knn"       : {knn_expression}
          | "rank"      : {rank_expression}
          | "from"      : number
          | "size"      : number
          | "min_score" : number -- Only supported in the SQL interface.

Query expression structure

The query expression structure specifies the full-text and scalar query conditions for hybrid search.

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

The scalar query structure defines scalar query expressions within a single term. It includes range_query, term_query, and terms_query, which correspond to the range, term, and terms expressions in SQL syntax. These represent range queries, exact matches, and multi-value matches, respectively.

scalar_term = range_query | term_query | terms_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]*

Full-text query structure

The full-text query structure defines full-text query expressions within a single term. It includes match_query, match_phrase_query, query_string, and multi_match, which correspond to the match, match_phrase, query_string, and multi_match expressions in SQL syntax. These represent term-level matching, phrase-level matching, full-text queries, 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" -- Supports ^ at the term level. For more information, see the section 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 ^ at the term level.
                         | "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]"

Vector and sorting structure

The vector and sorting structure specifies the parameters for vector search, including the vector field, similarity, filter conditions, and weights. It supports single and multi-vector searches.

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]
                 | "filter"       : [condition_list]
                 | "similarity"   : number
                 | "boost"        : boost_value
    multi_knn_condition_list = {knn_condition} [, {knn_condition}]*
    vector_values = float [, float]*

rank_expression = "rank" : {rank_strategy}
    rank_strategy = "rrf" : {rrf_params}
    rrf_params = "rank_window_size" : number [, "rank_constant" : number]

{
  "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 basic data types used in the syntax above.

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

Operator ^

The ^ operator specifies the weight of a column or a token in a full-text query statement. The syntax is column_name^weight or token^weight, for example, "title^2.0". The supported parameters are as follows:

• Only multi_match and query_string support column-level weights.
• Only query_string supports token-level weights.

The token-level weight operator specifies the weight of the token on its left (up to the space). The default weight of a token without the weight operator is 1.0. A token with the weight operator or a group of consecutive tokens without the weight operator is considered a group.

{
  "query": {
    "query_string": {
      "fields": ["title^2.0", "content"],
      "query": "red heart dragon fruit jelly orange^1.5 autumn moon pear^1.2 blueberry rambutan"
    }
  }
}

As shown in the example, the query token "red heart dragon fruit jelly orange^1.5 autumn moon pear^1.2 blueberry rambutan" is divided into four groups: "red heart" and "dragon fruit" with default weights of 1.0, "jelly" and "orange" with a weight of 1.5, "autumn moon" and "pear" with a weight of 1.2, and "blueberry" and "rambutan" with default weights of 1.0.

The ^ operator can be used with the boost parameter. For more information, see the boost parameter in the next section.

boost parameter

The boost parameter specifies the weight of the query condition in the final relevance calculation. The value must be ≥ 0. If not specified, the default value is 1. In the above syntax, bool, single_term, and single- and multi-vector searches (knn) support the boost parameter. Based on the limitations in this topic, the rules for using boost are as follows:

1. Supported expressions

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

2. Unsupported expressions

   • Scalar queries: term, range, and terms.
   • JSON/ARRAY scalar expressions.

3. Value constraints

   • The boost parameter of non-top-level full-text queries, the boost parameter of bool queries, and the column and token weights of full-text queries must be greater than 0.
   • In other cases, the boost parameter 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 weight (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
     }
   }
   

Examples

Full-text search

This example searches for all rows in the content column of the doc_table table that contain the term oceanbase mysql and returns the top 4 matching records.

SELECT * FROM HYBRID_SEARCH(
  TABLE doc_table,
  '{
    "query": {
      "match": {"content": "oceanbase mysql"}
    }
  }'
);

+------+---------+---------------------------------+----------------------------------+--------------------+
| c1   | vector  | query                           | content                          | __score            |
+------+---------+---------------------------------+----------------------------------+--------------------+
|    2 | [1,2,1] | hello world, what is your name  | oceanbase mysql database         |  2.170969786679347 |
|    1 | [1,2,3] | hello world                     | oceanbase Elasticsearch database | 0.3503184713375797 |
|    3 | [1,1,1] | hello world, how are you        | oceanbase oracle database        | 0.3503184713375797 |
|    6 | [2,1,1] | hello world, where are you from | starrocks oceanbase database     | 0.3503184713375797 |
+------+---------+---------------------------------+----------------------------------+--------------------+
4 rows in set

Hybrid full-text and vector RRF search

Create a sample table with a vector column, create a vector index for it, and create full-text indexes for 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

References

• For more information about the syntax and examples, see Hybrid search (SQL interface).