SEARCH

FUNCTION SEARCH (IN table_name VARCHAR(65535), 
                 IN search_params LONGTEXT)
RETURN JSON;

-- Top-level keyword parameters
search_params = '{param_list}'
    param_list = param [, param]*
    -- The query and knn parameters are used for full-text, scalar, and vector retrieval respectively. At least one of them is required; they are used together for hybrid retrieval.
    param = "query"     : {query_expression}
          | "knn"       : {knn_expression}  
          | "rank"      : {rank_expression}
          | "_source"   : [field_list]
          | "from"      : number
          | "size"      : number
          | "es_mode"   : boolean

  <main id="notice" type='notice'>
  <h4>Notice</h4>
<p>When mixed search is used, we strongly recommend that you specify the <code>_source</code> parameter to specify the columns to be returned. If you do not specify this parameter, all columns in the table will be returned by default. If the table contains vector columns, this will significantly increase the response time (RT).</p>

</main>

#### Query expression structure
```sql

query_expression = bool_query | single_term
-- Syntax of bool_query
    bool_query = "bool" : {bool_condition_list}
    bool_condition_list = bool_condition [, bool_condition]*
    -- boost subparameter available
                  bool_condition = "must"    : [condition_list]
                  | "should"   : [condition_list]
                  | "must_not" : [condition_list]
                  | "filter"   : [condition_list]
    | "boost"    : boost_value
    -- Nested bool_query is supported.
condition_list = query_expression [, query_expression]*

#### Single-term query structure

```sql
single_term = range_query | match_query | term_query | query_string | multi_match | rank_feature
    -- Syntax of range_query
    range_query = "range" : {"field_name" : {range_condition_list}}
    range_condition_list = range_condition [, range_condition]*
                    -- boost subparameter available
                    range_condition = "gte"    : number
                    | "gt"     : number
                    | "lte"    : number

| "lt"     : number
| "boost"  : boost_value
    -- Syntax of match_query
    match_query = "match" : {"field_name" : {match_condition}}

-- boost subparameter available
match_condition = "query" : "string_value" [, "boost" : boost_value]
    -- Syntax of term_query
    term_query = "term" : {term_condition_list}
                   term_condition_list = term_condition [, term_condition]*
    term_condition = "field_name" : scalar_value
    | "field_name" : term_value_object

-- boost subparameter available
term_value_object = "value" : scalar_value [, "boost" : boost_value]
-- The structure of a full-text search expression, which includes two retrieval methods: query_string and multi_match
    query_string = "query_string" : {query_string_condition}
                           -- fields and query sub-parameters are required
                           query_string_condition = "fields" : [field_weight_list]
                           | "query"  : "string_value"
                           | "boost"  : boost_value
                           | "type"   : ("best_fields" | "cross_fields" | "most_fields" | "phrase")
    | "default_operator" : ("AND" | "OR")
    | "minimum_should_match" : number
    field_weight_list = field_weight [, field_weight]*

-- No spaces are allowed between field_name, ^, and number.
field_weight = "field_name[^number]"
-- Syntax of multi_match, supported starting from V4.4.1 HotFix1
    multi_match = "multi_match" : {multi_match_condition}
                         -- fields and query sub-parameters are required
                         multi_match_condition = "fields" : [field_weight_list]
                         | "query"  : "string_value"
                         | "boost"  : boost_value
                         | "type"   : ("best_fields" | "cross_fields" | "most_fields" | "phrase")
    | "operator" : ("AND" | "OR")
    | "minimum_should_match" : number
    field_weight_list = field_weight [, field_weight]*

-- No spaces are allowed between field_name, ^, and number.
field_weight = "field_name[^number]"
    -- Feature sorting expression structure
                                  rank_feature = "rank_feature" : {rank_feature_condition_list}
    rank_feature_condition_list = "field" : "field_name",
                   rank_algorithm
                   rank_algorithm = "saturation" : {saturation_params}
                   | "sigmoid"    : {sigmoid_params}
    | "log"        : {log_params}
    | "linear"     : {linear_params}
    saturation_params = "pivot" : number [, "positive_score_impact" : boolean]
    sigmoid_params = "pivot" : number, "exponent" : number [, "positive_score_impact" : boolean]
log_params = "scaling_factor" : number [, "positive_score_impact" : boolean]

linear_params = ["positive_score_impact" : boolean]

| "similarity"    : number
| "boost"         : boost_value
vector_values = float [, float]*
```sql
-- Basic type definitions
field_name = "string_value"

field_list = field_name [, field_name]*

number = integer | decimal

boost_value = integer | float  -- The value of the boost parameter must be greater than or equal to 0.
  boolean = true | false
    scalar_value = "string_value" | number | boolean
      ```
      #### Basic types
      ```sql
    -- Basic types
  field_name = "string_value"
  field_list = field_name [, field_name]*
      number = integer | decimal
      boost_value = integer | float  -- The boost parameter must be >= 0.
    boolean = true | false
    scalar_value = "string_value" | number | boolean
      ```
      ### Parameter details
    The following table describes the parameters of `search_params`:
    <table>
      <thead>
      <tr>
    <th>Expression type</th>
    <th>Parameter name</th>
      <th>Description</th>
      </tr>
    </thead>
    <tbody>
      <tr>
      <td rowspan="8">Top-level keyword parameters</td>
    </tr>
    <tr>
      <td>query</td>
      <td>Can be used alone for full-text search or in combination with the <code>knn</code> parameter for mixed retrieval.</td>
    </tr>
    <tr>
      <td>knn</td>
      <td>Can be used alone for vector search or in combination with the <code>query</code> parameter for mixed retrieval.</td>
    </tr>
    <tr>
      <td>rank (optional)</td>
      <td>Specifies the sorting strategy for mixed retrieval, supporting the RRF (Reciprocal Rank Fusion) algorithm.<main id="notice" type='notice'><h4>Notice</h4><p>This parameter is supported starting from V4.4.1 BP0 HotFix1. </p></main></td>
    </tr>
    <tr>
      <td>_source (optional)</td>
      <td>Specifies the columns to return in the search result. If not specified, all user-defined columns in the table are returned.</td>
      </tr>
    <tr>
    <td>from (optional)</td>
      <td>Specifies the row number from which to start returning results. If not specified, the default is the first row. Must be used with the <code>size</code> parameter.</td>
      </tr>
    <tr>
    <td>size (optional)</td>
      <td>Specifies the maximum number of results to return. If not specified, the default is <code>10</code>.</td>
      </tr>
    <tr>
    <td>es_mode</td>
      <td>Specifies whether to convert full-text search to the new ESQL syntax. Default is <code>false</code>.</td>
      </tr>
    <tr>
    <td rowspan="5">bool</td>
      <td>must</td>
      <td>Must be satisfied and scored. When using boolean logic internally, nest bool expressions. Conditions within a bool expression are combined using AND logic by default.</td>
    </tr>
    <tr>
      <td>should</td>
      <td>Should be satisfied and scored. When using boolean logic internally, nest bool expressions. Conditions within a bool expression are combined using AND logic by default.</td>
      </tr>
    <tr>
    <td>must_not</td>
      <td>Must not be satisfied. Conditions within a must_not clause are combined using AND logic. When using boolean logic internally, nest bool expressions. Conditions within a bool expression are combined using AND logic by default.</td>
      </tr>
    <tr>
    <td>filter</td>
      <td>Must be satisfied and not scored. Conditions within a filter clause are combined using AND logic. When using boolean logic internally, nest bool expressions. Conditions within a bool expression are combined using AND logic by default.</td>
      </tr>
    <tr>
    <td>boost (optional)</td>
      <td>Query weight. For details, see the boost parameter description below.</td>
      </tr>
    <tr>
    <td rowspan="4">rank_feature (parameters for calculating relevance scores)</td>
      <td>pivot</td>
      <td>A required parameter for the saturation and sigmoid scoring algorithms. The default value is the geometric mean of the data in this list.</td>
      </tr>
    <tr>
    <td>positive_score_impact (optional)</td>
      <td>Specifies whether the field's impact on the final relevance is positive or negative.</td>
      </tr>
    <tr>
    <td>scaling_factor</td>
      <td>A required parameter for the log scoring algorithm.</td>
      </tr>
    <tr>
    <td>exponent</td>
      <td>A required parameter for the sigmoid scoring algorithm.</td>
      </tr>
    <tr>
    <td rowspan="4">rank_feature (scoring algorithms)</td>
      <td>saturation</td>
    <td>The default relevance scoring algorithm<ul><li>Positive correlation: `S / (S + pivot)`</li><li>Negative correlation: `pivot / (S + pivot)`</li><li>S is the value in the rank_feature column.</li></td>
    </tr>
      <tr>
      <td>sigmoid</td>
    <td><ul><li>Similar to saturation, but includes an exponent parameter. When using the sigmoid algorithm, specify e.</li><li>S^e / (S^e + pivot^e)</li></td>
    </tr>
      <tr>
      <td>log</td>
    <td><ul><li>Supports only positive correlation.</li><li>Scoring formula: ln(scaling_factor + S)</li><li>S is the value in the rank_feature column.</li><li>scaling_factor must be specified when using the log algorithm.</li></td>
    </tr>
      <tr>
      <td>linear</td>
    <td><ul><li>Positive correlation score is S.</li><li>Negative correlation score is 1/S.</li><li>S is the value in the rank_feature column.</li></td>
    </tr>
      <tr>
      <td rowspan="13">single term (single-term search)</td>
    </tr>
    <tr>
      <td>range</td>
      <td>Range search. Use with gte, gt, lte, lt, and boost. <code>fieldname</code> is required.</td>
    </tr>
    <tr>
      <td>match</td>
      <td>Fuzzy match. Converted to the SQL 'match' expression. Use with boost.</td>
    </tr>
    <tr>
      <td>term</td>
      <td>Exact match. Supports scalar values such as strings, numbers, and booleans. Converted to the SQL '=' expression. Use with boost.</td>
    </tr>
    <tr>
      <td>query_string</td>
      <td>Full-text match. Converted to a combination of multiple 'match' expressions in SQL.</td>
    </tr>
    <tr>
      <td>multi_match</td>
      <td>Full-text match. Converted to a combination of multiple 'match' expressions in SQL. Similar to query_string, but single keywords do not support weights.<main id="notice" type='notice'><h4>Notice</h4><p>This parameter is supported starting from V4.4.1 BP0 HotFix1. </p></main></td>
    </tr>
    <tr>
      <td>fields</td>
      <td>List of text search fields. Weights can be configured for each field.</td>
    </tr>
    <tr>
      <td>query</td>
      <td>Retrieves a list of keywords, each of which can be assigned a weight.</td>
    </tr>
    <tr>
      <td>minimum_should_match (optional)</td>
      <td>Specifies the number of conditions that must be met in the should or query_string clauses. If this parameter is not specified, the default value is <code>1</code>. Note: If the bool expression contains must/filter clauses and this parameter is not specified, the default value is <code>0</code>, meaning that the should conditions can be unmet.</td>
    </tr>
    <tr>
      <td>boost (optional)</td>
    <td>The query weight. For more information, see the detailed description of the boost parameter below.</td>
    </tr>
      <tr>
      <td>type (optional)</td>
    <td>Specifies the matching mode. Supported values include best_fields, cross_fields, most_fields, and phrase. The default value is <code>best_fields</code> if this parameter is not specified.</td>
    </tr>
      <tr>
      <td>default_operator (optional)</td>
    <td>A subfield of query_string that specifies the logical combination of multiple keywords.</td>
    </tr>
      <tr>
      <td>operator (optional)</td>
    <td>A subfield of multi_match that specifies the logical combination of multiple keywords.</td>
    </tr>
      <tr>
      <td rowspan="7">knn (vector search)</td>
    </tr>
    <tr>
      <td>field</td>
      <td>The vector search field.</td>
    </tr>
    <tr>
      <td>k</td>
      <td>The number of rows to return for vector search.</td>
    </tr>
    <tr>
      <td>query_vector</td>
      <td>The search vector.</td>
      </tr>
    <tr>
    <td>filter (optional)</td>
      <td>The filter condition.</td>
      </tr>
    <tr>
    <td>similarity (optional)</td>
      <td>The filter condition for vector distance.</td>
      </tr>
    <tr>
  <td>boost (optional)</td>
<td>The query weight. For more information, see the detailed description of the boost parameter below.</td>

</tr>

<tr>

<td rowspan="3">rank (RRF sorting strategy)</td>

    <td>rrf</td>

    <td>The RRF (Reciprocal Rank Fusion) sorting strategy, which is used to integrate and sort multiple query results during hybrid search.<main id="notice" type='notice'><h4>Notice</h4><p>This parameter is supported starting from V4.4.1 BP0 HotFix1. </p></main></td>
    </tr>
      <tr>
        <td>rank_window_size (optional)</td>
        <td>This parameter specifies the size of the result set returned for each query. A larger value indicates higher relevance but may result in higher performance overhead. The final sorted result set will be trimmed to the size specified by the <code>size</code> parameter in the search request.<br/><br/>The rank_window_size parameter must meet the following conditions:<ul><li>It must be greater than or equal to the <code>size</code> parameter.</li><li>It must be greater than or equal to 1.</li></ul>The default value is the value of the <code>size</code> parameter.</td>
      </tr>
    <tr>
    <td>rank_constant (optional)</td>

<td>This parameter controls the impact of each document in the result set on the final sorting result. A larger value indicates a greater impact from documents ranked lower in the result set. The default value is 60.</td>

    </tr>

    </tbody>
    </table>
      #### boost parameter
        The `boost` parameter specifies the weight of query conditions in the final relevance calculation. The value must be ≥ `0`, and the default value is `1` when not specified. In the above syntax, the `bool`, `single_term` (excluding `rank_feature`), and `knn` parameters support the `boost` parameter.
        1. Query-level boost
        Specify a weight for the entire query condition, for example:
      ```shell
    {
    "bool": {

"must": [{"term": {"category": "Gaming"}}],

    "boost": 2.0  // Weight for the entire bool query

    }
    }
      ```
        2. Field-level boost
        Specify a weight for a specific field:
        ```shell
        {
      "query_string": {
    "fields": ["product_name", "description"],
    "query": "gaming keyboard",

"boost": 1.5  // Weight for the entire query_string query

    }

    }
    ```
      3. Match value-level boost
        Specify a weight for specific match values (only supported for match and term queries):
        ```shell
      {
    "match" : {
    "product_name":  {

"query" : "gaming keyboard",

"boost" : 1.5

}

}

}

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

SET @parm = '{

"query": {
"bool": {
"must": [
{"term": {"brand": "GamerZone"}}
]
  }
    },
    "_source": ["product_id", "product_name", "description", "brand", "category", "tags", "price", "stock_quantity", "release_date", "is_on_sale"]
    }';
    ```
    Retrieve all records where `brand` is `"GamerZone"`.
    ```sql
    SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products', @parm)); 
    ```
    The result is as follows:
    :::collapse{title="Result"}
    ```shell
  +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products', @parm))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
    +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | [
    {
    "tags": "best-seller,gaming-gear,rgb",
    "brand": "GamerZone",
    "price": 149.00,
    "_score": 1,
    "category": "Gaming",
    "is_on_sale": 1,
    "product_id": "prod-004",
    "description": "A responsive mechanical keyboard with customizable RGB lighting for the ultimate gaming experience.",
  "product_name": "Gamer-Pro Mechanical Keyboard",
"release_date": "2023-07-20 00:00:00.000000",
"stock_quantity": 100
},
{

"tags": "best-seller,gaming-gear,audio",

"brand": "GamerZone",

"price": 149.00,

"_score": 1,
"category": "Gaming",
"is_on_sale": 1,

"product_id": "prod-009",

"description": "High-fidelity gaming headset with a noise-cancelling microphone.",
"product_name": "Gamer-Pro Headset",
                            "release_date": "2023-07-20 00:00:00.000000",
                            "stock_quantity": 100
                            }
                            ] |
                            +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

  Create a sample table with a vector column, create a vector index, and create full-text indexes for two `varchar` columns.
    ```sql
      CREATE TABLE doc_table(c1 INT, vector VECTOR(3), query VARCHAR(255), content VARCHAR(255), VECTOR INDEX idx1(vector) WITH (distance=l2, type=hnsw, lib=vsag), FULLTEXT INDEX idx2(query), FULLTEXT INDEX idx3(content));
        ```
        Write data.
      ```sql
    INSERT INTO doc_table VALUES(1, '[1,2,3]', "hello world", "oceanbase Elasticsearch database"),
  (2, '[1,2,1]', "hello world, what is your name", "oceanbase mysql database"),
   (3, '[1,1,1]', "hello world, how are you", "oceanbase oracle database"),
      (4, '[1,3,1]', "real world, where are you from", "postgres oracle database"),
      (5, '[1,3,2]', "real world, how old are you", "redis oracle database"),
      (6, '[2,1,1]', "hello world, where are you from", "starrocks oceanbase database");
  ```
  Set the retrieval parameters.
    ```sql
      SET @parm = '{
      "query": {
    "bool": {
  "should": [
  {"match": {"query": "hi hello"}}, 
{"match": { "content": "oceanbase mysql" }}

Execute the query and return the query result.
  ```sql
  SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm)); 
  ```
The result is as follows:
:::collapse{title="Result"}
  ```shell
  +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [
  {
  "query": "hello world",
  "_score": 0.0325,
"content": "oceanbase Elasticsearch database"
},
  {
  "query": "hello world, what is your name",
  "_score": 0.0323,
"content": "oceanbase mysql database"
},
  {
  "query": "hello world, how are you",
  "_score": 0.0315,
"content": "oceanbase oracle database"
},
{
"query": "real world, how old are you",
"_score": 0.0161,

"content": "redis oracle database"

},

{
"query": "hello world, where are you from",