SEARCH

The SEARCH function returns search results in JSON format. Results are ordered by relevance.

Syntax

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

Parameters

Parameter	Description	Required
table_name	The name of the table. Heap tables and tables without a primary key are supported.	Yes
search_params	Search parameters, provided as a JSON-formatted string.	Yes

search_params structure

search_params is a JSON-formatted string. This section describes its structure. Read it together with the parameter descriptions and examples below.

Notation

This section explains the BNF notation used in the syntax definitions:

1. Optional elements

   • In BNF, [ ] indicates an optional, repeatable element. For example, param_list = param [, param]* means param_list can contain one or more param items.
   • In rank_feature and rank_expression, [ ] also indicates optional sub-parameters.
   • [, "boost" : boost_value] means the boost sub-parameter is optional. All boost sub-parameters are optional.

2. Arrays

   • In JSON, [ ] indicates an array, for example, [condition_list].

3. Alternatives

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

4. Repetition

   • * indicates repetition zero or more times. For example, param_list = param [, param]* means param_list can contain one or more param items.

5. JSON formatting requirements

   • All JSON field names and string values must be enclosed in double quotes.
   • Numeric values must not be enclosed in double quotes.

Syntax definition

This section describes the structure of search_params. For parameter details, see the table in the next section.

Top-level structure

-- Top-level keyword parameters
search_params = '{param_list}'
    param_list = param [, param]*
    -- query and knn are used for full-text/scalar retrieval and vector retrieval, respectively.
    -- You must specify at least one of them. For hybrid retrieval, specify both.
    param = "query"     : {query_expression}
          | "knn"       : {knn_expression}  
          | "rank"      : {rank_expression}
          | "_source"   : [field_list]
          | "from"      : number
          | "size"      : number
          | "es_mode"   : boolean

Query expression

query_expression = bool_query | single_term

-- bool_query structure
bool_query = "bool" : {bool_condition_list}
    bool_condition_list = bool_condition [, bool_condition]*
    -- boost is optional
    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

single_term = range_query | match_query | term_query | terms_query | query_string | multi_match | rank_feature

-- range_query structure
range_query = "range" : {"field_name" : {range_condition_list}}
    range_condition_list = range_condition [, range_condition]*
    -- boost is optional
    range_condition = "gte"    : number
                    | "gt"     : number
                    | "lte"    : number
                    | "lt"     : number
                    | "boost"  : boost_value

-- match_query structure
match_query = "match" : {"field_name" : {match_condition}}
    -- boost is optional
    match_condition = "query" : "string_value" [, "boost" : boost_value]

-- term_query structure
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 is optional
    term_value_object = "value" : scalar_value [, "boost" : boost_value]

-- terms_query structure
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 supports query_string and multi_match
query_string = "query_string" : {query_string_condition}
-- fields and query 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]"

-- multi_match structure
multi_match = "multi_match" : {multi_match_condition}
-- fields and query 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-based ranking
rank_feature = "rank_feature" : {"field_name" : {rank_algorithm}}
    rank_algorithm = "saturation" : {"pivot" : number[, "positive_score_impact" : boolean]}
                   | "sigmoid"    : {"pivot" : number, "exponent" : number[, "positive_score_impact" : boolean]}
                   | "log"        : {"scaling_factor" : number[, "positive_score_impact" : boolean]}
                   | "linear"     : {["positive_score_impact" : boolean]}

Vector retrieval

-- Single vector retrieval
knn_expression = "knn" : {knn_condition_list}
    knn_condition_list = knn_condition [, knn_condition]*
    -- field, k, and query_vector are required
    knn_condition = "field"         : "field_name"
                 | "k"             : number
                 | "query_vector"  : [vector_values]
                 | "filter"        : [condition_list]
                 | "similarity"    : number
                 | "boost"         : boost_value
    vector_values = float [, float]*
    condition_list = query_expression [, query_expression]*

-- Multi-vector retrieval
-- The [ ] around [multi_knn_condition_list] is part of the required structure and must not be changed.
multi_knn_expression = "knn" : [multi_knn_condition_list]
    -- The [ ] around [multi_knn_condition] indicates it is optional.
    multi_knn_condition_list = {multi_knn_condition} [, {multi_knn_condition}]*
    -- field, k, and query_vector are required
    multi_knn_condition = "field"         : "field_name"
                        | "k"             : number
                        | "query_vector"  : [vector_values]
                        | "filter"        : [condition_list]
                        | "similarity"    : number
                        | "boost"         : boost_value
    vector_values = float [, float]*
    condition_list = query_expression [, query_expression]*

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

Basic types

-- Basic types
field_name = "string_value"
field_list = field_name [, field_name]*
number = integer | decimal
boost_value = integer | float  -- boost must be >= 0
boolean = true | false
scalar_value = "string_value" | number | boolean

Parameter details

The following table describes the parameters in search_params.

Expression type	Parameter	Description
Top-level keyword parameters
	query	Used for full-text search. Can be used alone, or together with knn for hybrid search.
	knn	Used for single- or multi-vector retrieval. Can be used alone, or together with query for hybrid search.
	rank (optional)	Specifies the ranking strategy for hybrid search. The RRF (Reciprocal Rank Fusion) algorithm is supported.
	_source (optional)	Specifies which columns to include in the response. If not specified, all user-defined columns in the table are returned.
	from (optional)	Specifies the starting offset in the result set. If not specified, results are returned starting from the first row. Use it together with size.
	size (optional)	Limits the number of rows returned. If not specified, the default is 10.
	es_mode	Specifies whether to convert full-text search into the ESQL new syntax. The default is false.
bool	must	Must match and contributes to the score. If you need boolean logic inside, nest a bool expression. Multiple conditions in a bool expression are combined with AND by default.
	should	Should match (similar to OR) and contributes to the score. If you need boolean logic inside, nest a bool expression. Multiple conditions in a bool expression are combined with AND by default.
	must_not	Must not match and does not contribute to the score. It is converted to a NOT expression; multiple clauses inside must_not are combined with AND. If you need boolean logic inside, nest a bool expression. Multiple conditions in a bool expression are combined with AND by default.
	filter	Must match and does not contribute to the score. It is converted to an AND expression. If you need boolean logic inside, nest a bool expression. Multiple conditions in a bool expression are combined with AND by default.
	boost (optional)	Query weight. For details, see Boost parameter below.
rank_feature (scoring parameters)	pivot	A required scoring parameter for saturation and sigmoid. The default is the geometric mean of the values in the column.
	positive_score_impact (optional)	Controls whether the field has a positive or negative impact on the final relevance score.
	scaling_factor	A required parameter for the log scoring formula.
	exponent	A required parameter for the sigmoid scoring formula.
rank_feature (scoring algorithms)	saturation	Default scoring algorithm Positive impact: S / (S + pivot) Negative impact: pivot / (S + pivot) S is the value of the rank_feature column.
	sigmoid	Similar to saturation but adds the exponent parameter e. S^e / (S^e + pivot^e)
	log	Only supports positive impact. Formula: ln(scaling_factor + S) S is the value of the rank_feature column. scaling_factor must be specified in the request.
	linear	Positive impact score: S Negative impact score: 1/S S is the value of the rank_feature column.
single_term (single-term retrieval)
	range	Range query. Use with gte, gt, lte, lt, and boost. fieldname is required.
	match	Fuzzy match. Converted to a SQL match expression. Use with boost.
	term	Exact match. Supports scalar values such as strings, numbers, and booleans. Converted to a SQL = expression. Use with boost.
	terms	Exact match against any value in a set. Supports arrays of scalar values (strings, numbers, booleans, etc.). Converted to a SQL IN expression. Use with boost.
	query_string	Full-text match. Converted into a combination of multiple SQL match expressions.
	multi_match	Full-text match. Converted into a combination of multiple SQL match expressions. Similar to query_string, but it does not support weights for a single keyword.
	fields	A list of fields used for full-text retrieval. You can specify a weight for each field.
	query	A list of query terms. You can specify a weight for each term.
	minimum_should_match (optional)	Controls how many conditions in should or query_string must match. If omitted, the default is 1. Note: If must or filter exists in the bool expression and this parameter is omitted, the default becomes 0 (meaning none of the should clauses must match).
	boost (optional)	Query weight. For details, see Boost parameter below.
	type (optional)	Match mode. This release supports best_fields, cross_fields, most_fields, and phrase. If omitted, the default is best_fields.
	default_operator (optional)	A sub-field of query_string. Specifies how multiple query terms are combined.
	operator (optional)	A sub-field of multi_match. Specifies how multiple query terms are combined.
knn (vector retrieval)
	field	The vector field used for retrieval.
	k	The number of rows to return from vector retrieval.
	query_vector	The query vector.
	filter (optional)	Filter conditions.
	similarity (optional)	A filter condition based on vector distance.
	boost (optional)	Query weight. For details, see Boost parameter below.
rank (RRF ranking strategy)	rrf	RRF (Reciprocal Rank Fusion) ranking strategy. Used in hybrid search to fuse and re-rank results from multiple queries.
	rank_window_size (optional)	Specifies the size of the per-query result window. A larger value generally improves relevance but increases overhead. The final ranked result set is truncated to the size specified in the request. The value of rank_window_size must satisfy: Greater than or equal to size Greater than or equal to 1 The default is the value of size.
	rank_constant (optional)	Controls how much lower-ranked documents in each per-query result set influence the final ranking. A larger value gives more influence to lower-ranked documents. The default is 60.

Boost parameter

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 above, boost is supported by bool, single_term (except rank_feature), and both single- and multi-vector retrieval (knn). The following examples show how boost can be used:

1. Query-level boost

   Assigns a weight to the entire query condition. For example:

   {
     "bool": {
       "must": [{"term": {"category": "Gaming"}}],
       "boost": 2.0  // Weight of the entire bool query
     }
   }

2. Field-level boost

   Assigns a weight to a query on a specific field. For example:

   {
     "query_string": {
       "fields": ["product_name", "description"],
       "query": "gaming keyboard",
       "boost": 1.5  // Weight of the entire query_string query
     }
   }

3. Value-level boost

   Assigns a weight to a specific match value (supported by match, term, and terms queries). For example:

   {
     "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. For example:

   {
     "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 specify its own boost weight:

   Single vector retrieval:

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

   Multi-vector retrieval:

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

Examples

Basic scalar search

Create the example table products:

CREATE TABLE products (
  `product_id` varchar(50) DEFAULT NULL,
  `product_name` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `brand` varchar(100) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `tags` varchar(255) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `stock_quantity` int(11) DEFAULT NULL,
  `release_date` datetime DEFAULT NULL,
  `is_on_sale` tinyint(1) DEFAULT NULL,
  `vec` VECTOR(4) DEFAULT NULL
);

Insert data:

INSERT INTO products VALUES
('prod-004', 'Gamer-Pro Mechanical Keyboard', 'A responsive mechanical keyboard with customizable RGB lighting for the ultimate gaming experience.',
 'GamerZone', 'Gaming', 'best-seller,gaming-gear,rgb', 149.00, 100, '2023-07-20 00:00:00.000000', 1, '[0.5,0.1,0.6,0.9]'),

('prod-009', 'Gamer-Pro Headset', 'High-fidelity gaming headset with a noise-cancelling microphone.',
 'GamerZone', 'Gaming', 'best-seller,gaming-gear,audio', 149.00, 100, '2023-07-20 00:00:00.000000', 1, '[0.1,0.9,0.2,0]');

Set the search parameters:

SET @parm = '{
  "query": {
      "bool": {
        "must": [
         {"term": {"brand": "GamerZone"}}
        ]
    }
  }
}';

Search for rows where brand is "GamerZone":

SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products', @parm)); 

The return result is as follows:

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products', @parm))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [
  {
    "vec": "[0.5,0.1,0.6,0.9]",
    "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
  },
  {
    "vec": "[0.1,0.9,0.2,0]",
    "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

Hybrid full-text and vector search

Create an example table that includes a vector column and a vector index, and create full-text indexes on 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");

Set the search parameters:

SET @parm = '{
  "query": {
    "bool": {
      "should": [
        {"match": {"query": "hi hello"}}, 
        {"match": { "content": "oceanbase mysql" }}
      ]
    }
  },
    "knn" : {
      "field": "vector",
      "k": 5,
      "query_vector": [1,2,3]
  },
  "rank": {
    "rrf": {
      "rank_window_size": 10,
      "rank_constant": 60
    }
  },
  "_source" : ["query", "content"]
}';

Run the query:

SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm)); 

The return result is as follows:

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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",
    "_score": 0.0159,
    "content": "starrocks oceanbase database"
},
{
    "query": "real world, where are you from",
    "_score": 0.0156,
    "content": "postgres oracle database"
}
] |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

References

• DBMS_HYBRID_SEARCH subprograms overview
• GET_SQL