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 must be specified; 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 using mixed search, it is strongly recommended to specify the <code>_source</code> parameter to define the range of returned columns. If not specified, all columns of 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 syntax

```sql
single_term = range_query | match_query | term_query | terms_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]
    -- Syntax of terms_query
    terms_query = "terms" : {terms_condition_list}
                    terms_condition_list = terms_condition [, terms_condition]*
    terms_condition = "field_name" : [scalar_value_list]

| "boost"      : boost_value
scalar_value_list = scalar_value [, scalar_value]*
-- Full-text search expression structure, including 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
    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]

multi_knn_condition = "field"         : "field_name"
| "k"             : number
| "query_vector"  : [vector_values]
```sql
-- Basic Types
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
      ```
      rank_strategy = "rrf" : {rrf_params}
      rrf_params = "rank_window_size" : number [, "rank_constant" : number]
    ```
  #### Basic type definitions
  ```sql
      -- Basic type definitions
      field_name = "string_value"
    field_list = field_name [, field_name]*
    number = integer | decimal
      boost_value = integer | float  -- The boost parameter value must be >= 0.
      boolean = true | false
    scalar_value = "string_value" | number | boolean
    ```
      ### Parameter details
      The detailed parameters of `search_params` are described as follows:
    <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 and can be used with the <code>knn</code> parameter for hybrid search.</td>
      </tr>
    <tr>
    <td>knn</td>
      <td>Can be used alone for single/multi-vector search and can be used with the <code>query</code> parameter for hybrid search.</td>
      </tr>
    <tr>
    <td>rank (optional)</td>
      <td>Used to specify the sorting strategy for hybrid search, supporting the RRF (Reciprocal Rank Fusion) algorithm.</td>
      </tr>
      <tr>
    <td>_source (optional)</td>
    <td>Used to specify the columns to return in the search results. If not specified, all user-defined columns in the table are returned.</td>
      </tr>
      <tr>
    <td>from (optional)</td>
    <td>Used to specify the row from which to start returning results in the search result set. If not specified, the default is to start from the first row. This parameter must be used with the <code>size</code> parameter.</td>
      </tr>
      <tr>
    <td>size (optional)</td>
    <td>Used to limit the number of results returned. If not specified, the default is <code>10</code>.</td>
      </tr>
      <tr>
    <td>es_mode</td>
    <td>Used to specify whether to convert full-text search to the new ESQL syntax. The default is <code>false</code>.</td>
      </tr>
      <tr>
    <td rowspan="5">bool</td>
    <td>must</td>
      <td>Must be satisfied and requires scoring. When using boolean logic internally, a nested bool expression is required. Multiple conditions within a bool expression are combined using AND logic by default.</td>
      </tr>
      <tr>
    <td>should</td>
    <td>Should be satisfied, similar to OR, and requires scoring. When using boolean logic internally, a nested bool expression is required. Multiple conditions within a bool expression are combined using AND logic by default.</td>
      </tr>
      <tr>
    <td>must_not</td>
    <td>Must not be satisfied, does not require scoring, and is converted to a 'NOT' expression. Multiple conditions within must_not are connected using 'AND'. When using boolean logic internally, a nested bool expression is required. Multiple conditions within a bool expression are combined using AND logic by default.</td>
      </tr>
      <tr>
    <td>filter</td>
    <td>Must be satisfied, does not require scoring, and is converted to an 'AND' expression. When using boolean logic internally, a nested bool expression is required. Multiple conditions within a bool expression are combined using AND logic by default.</td>
      </tr>
      <tr>
    <td>boost (optional)</td>
    <td>Query weight. For more details, see the description of the boost parameter below.</td>
      </tr>
      <tr>
      <td rowspan="4">rank_feature (parameters for score calculation)</td>
    <td>pivot</td>
    <td>A required scoring parameter for saturation and sigmoid algorithms. The default value is the geometric mean of the data in this list.</td>
      </tr>
      <tr>
    <td>positive_score_impact (optional)</td>
    <td>Used to specify 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 calculation formula.</td>
      </tr>
      <tr>
    <td>exponent</td>
    <td>A required parameter for the sigmoid algorithm calculation formula.</td>
      </tr>
    <tr>
    <td rowspan="4">rank_feature (score calculation algorithms)</td>
      <td>saturation</td>
      <td>The default 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 with an additional exponent parameter. The exponent e must be specified during search.</li><li>S^e / (S^e + pivot^e)</li></td>
    </tr>
    <tr>
      <td>log</td>
      <td><ul><li>Only supports positive correlation.</li><li>Calculation formula: ln(scaling_factor + S)</li><li>S is the value in the rank_feature column.</li><li>The scaling_factor must be specified during search.</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="14">single term (single-term search)</td>
      </tr>
    <tr>
    <td>range</td>
      <td>Range search, used 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, used 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, used with boost.</td>
      </tr>
    <tr>
    <td>terms</td>
      <td>Exact match for any value in the specified set, supports arrays of scalar values such as strings, numbers, and booleans, converted to the SQL 'IN' expression, used 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 does not support weight for single keywords.</td>
      </tr>
    <tr>
    <td>fields</td>
      <td>List of text search fields, with configurable weights for each column.</td>
      </tr>
    <tr>
    <td>query</td>
      <td>List of search keywords, with configurable weights for each keyword.</td>
      </tr>
    <tr>
    <td>minimum_should_match (optional)</td>
      <td>Controls the number of conditions that must be met in the should and 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 will be <code>0</code>, meaning that the should conditions can be unmet.</td>
    </tr>
    <tr>
      <td>boost (optional)</td>
      <td>The query weight, see the detailed description of the boost parameter below.</td>
    </tr>
    <tr>
      <td>type (optional)</td>
      <td>Specifies the matching mode. This version supports best_fields, cross_fields, most_fields, and phrase. The default value is <code>best_fields</code> if not specified.</td>
    </tr>
    <tr>
      <td>default_operator (optional)</td>
      <td>A subfield of query_string that specifies the logical combination between multiple keywords.</td>
    </tr>
    <tr>
      <td>operator (optional)</td>
      <td>A subfield of multi_match that specifies the logical combination between 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 returned by the vector search.</td>
      </tr>
      <tr>
    <td>query_vector</td>
    <td>Specifies the search vector.</td>
      </tr>
      <tr>
    <td>filter (optional)</td>
    <td>The filter condition.</td>
      </tr>
      <tr>
    <td>similarity (optional)</td>
  <td>Specifies the vector distance filter condition.</td>
</tr>

<tr>

<td>boost (optional)</td>

<td>The query weight, 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, used to fuse and sort multiple query results during hybrid search.</td>
        </tr>
      <tr>
    <td>rank_window_size (optional)</td>
    <td>This value specifies the size of the result set returned for each query. A larger value indicates higher relevance but may incur performance overhead. The final sorted result set will be trimmed to the size specified in the search request's <code>size</code> parameter.<br/><br/>rank_window_size must meet the following conditions:<ul><li>Greater than or equal to the <code>size</code> parameter</li><li>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 value 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. The default value is 60.</td>
      </tr>
        </tbody>
        </table>
        #### boost parameter
      The `boost` parameter specifies the weight of the query conditions in the final relevance calculation. The value must be ≥ `0`, and the default value is `1` if not specified. In the syntax structure above, the `bool`, `single_term` (excluding `rank_feature`), single and multi-vector retrieval (`knn`) all support the `boost` parameter. The usage of the `boost` parameter for each retrieval type is as follows:
    1. Query-level boost
    Specify the weight for the entire query condition, for example:

```shell

    {

    "bool": {
    "must": [{"term": {"category": "Gaming"}}],
      "boost": 2.0  // Weight of the entire bool query
        }
        }
        ```
        2. Field-level boost
      Specify the weight for a specific field's query:
    ```shell
    {

"query_string": {

    "fields": ["product_name", "description"],

    "query": "gaming keyboard",
    "boost": 1.5  // Weight of the entire query_string query
      }
        }
        ```
      3. Match value-level boost
    Specify the weight for specific match values (supported by match, term, and terms queries):
    ```shell

{

    "match" : {

    "product_name":  {

    "query" : "gaming keyboard",
    "boost" : 1.5
      }
        }
        }
        ```
        4. Field weight syntax
      In query_string and multi_match, you can use the `field_name^weight` syntax:
    ```shell
    {

    "query_string": {

    "fields": ["product_name^2.0", "description^1.0"],
    "query": "gaming"
      }
        }
        ```
        5. Vector retrieval boost
        In single and multi-vector retrieval, each vector field can independently specify the `boost` weight:
      Single-vector retrieval:
        ```shell
        {
        "knn": {
        "field": "vector",
      "k": 5,
    "query_vector": [1, 2, 3],
    "boost": 1.5  // Weight of this vector field

}

}

{
  "knn": [{
  "field": "c2",
  "k": 5,
  "query_vector": [1, 2, 3],
  "boost": 2.0  // Weight of the c2 vector field
  }, {
  "field": "c4",
  "k": 5,
  "query_vector": [1, 2, 3],
  "boost": 1.0  // Weight of the c4 vector field
  }]
}

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="Return 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

:::
  ### Full-text and vector mixed retrieval
    Create an example table with a vector column, create a vector index for the vector column, 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));
```

SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm)); 
    ```
    The result is as follows:
    :::collapse{title="Return 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"