HYBRID_SEARCH

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

-- select_expr_list 为 SELECT 列表，与常规 SELECT 语法一致

dsl_string = '{param_list}'
    param_list = param [, param]*
    -- query 和 knn 至少必选一个；混合搜索时可同时使用
    param = "query"     : {query_expression | search_options} --包含查询表达式或查询选项
          | "knn"       : {knn_expression}
          | "rank"      : {rank_expression}
          | "rerank"    : {rerank_params} -- 从 V4.6.0 BP1 版本开始支持
          | "from"      : number
          | "size"      : number
          | "min_score" : number -- 仅 SQL 接口支持

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]*

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} -- 从 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

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" -- 可含词元级 ^，具体见下文「权重运算符说明」
                           | "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" -- 不支持查询串内词元级 ^
                         | "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]"

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 -- 从 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")]

-- 从 V4.6.0 BP1 版本开始支持
rerank_params = "model" : "string_value"
              | "field" : "field_name"
              | "query" : "string_value"
              | "rank_window_size" : number
              | "type" : "string_value"

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

{
  "query": {
    "query_string": {
      "fields": ["title^2.0", "content"],
      "query": "红心火龙果 果冻橙^1.5 秋月梨^1.2 蓝莓 山竹"
    }
  }
}

CREATE TABLE doc_table(c1 INT, vector VECTOR(3), query VARCHAR(255), content VARCHAR(255), VECTOR INDEX idx1(vector) WITH (distance=l2, type=hnsw, lib=vsag), FULLTEXT INDEX idx2(query), FULLTEXT INDEX idx3(content));

INSERT INTO doc_table VALUES(1, '[1,2,3]', "hello world", "oceanbase Elasticsearch database"),
                            (2, '[1,2,1]', "hello world, what is your name", "oceanbase mysql database"),
                            (3, '[1,1,1]', "hello world, how are you", "oceanbase oracle database"),
                            (4, '[1,3,1]', "real world, where are you from", "postgres oracle database"),
                            (5, '[1,3,2]', "real world, how old are you", "redis oracle database"),
                            (6, '[2,1,1]', "hello world, where are you from", "starrocks oceanbase database");

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

-- 同时设置 num_candidates 与 search_options.ef_search：num_candidates 优先
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"
    }
  }
}');

-- 普通列前缀匹配
SELECT * FROM HYBRID_SEARCH(TABLE doc_table, '{
  "query": {"wildcard": {"title": "prefix*"}}
}');

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

-- 作为 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 组合
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
        }}
      ]
    }
  }
}');