The GET_SQL function is used to obtain the actual SQL statement executed and return it as a string.
Syntax
FUNCTION GET_SQL (IN table_name VARCHAR(65535),
IN search_params LONGTEXT)
RETURN LONGTEXT;
Parameters
Parameter |
Description |
Required |
|---|---|---|
| table_name | Table name, which can be a heap table or a table without a primary key. | Required |
| search_params | The search parameter, which is a string in JSON format. | Required |
Syntax of search_params
search_params is a JSON-formatted string. Its syntax is described in detail in this section. Please understand it in conjunction with the following parameters and examples.
Syntax
This section describes the meanings and usage rules of BNF syntax symbols:
Optional parameters
[ ]indicates that multiple elements are optional in BNF. For example,param_list = param [, param]*indicates thatparam_listcan contain one or moreparamelements.- In
rank_featureandrank_expression,[ ]also indicates that the sub-parameter is optional. [, "boost" : boost_value]indicates that the boost subparameter is optional and all boost subparameters are optional.
Array representation
[ ]indicates an array in a JSON structure, such as[condition_list].
Select relationships
|indicates an OR relationship. For example,param = "query" | "knn"means param can be either "query" or "knn".
Repetitive representation
*indicates zero or more repetitions. For example,param_list = param [, param]*meansparam_listcan contain one or moreparamelements.
JSON Format Requirements
- All JSON field names and string values must be enclosed in double quotes.
- Numbers do not need to be enclosed in double quotes.
Syntax definition
This section describes the syntax of search_params. For more information about the parameters, see the table below.
Top-level parameter structure
-- 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
Query expression syntax
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
-- Supports nested bool_query
condition_list = query_expression [, query_expression]*
Single entry query structure
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]*
-- 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
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" : {"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]}
Structure of a vector retrieval expression
-- Single-Path Vector Retrieval Expression Structure
knn_expression = "knn" : {knn_condition_list}
knn_condition_list = knn_condition [, knn_condition]*
-- The field, k, and query_vector subparameters 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 expression structure
-- The [] in [multi_knn_condition_list] is a fixed format and cannot be modified.
multi_knn_expression = "knn" : [multi_knn_condition_list]
-- The [] in [multi_knn_condition] indicates that the element is optional.
multi_knn_condition_list = {multi_knn_condition} [, {multi_knn_condition}]*
-- The field, k, and query_vector subparameters are required.
multi_knn_condition = "field" : "field_name"
| "k" : number
| "query_vector" : [vector_values]
| "filter" : [condition_list]
| "similarity" : number
| "boost" : boost_value
-- Syntax of rank_expression
rank_expression = "rank" : {rank_strategy}
rank_strategy = "rrf" : {rrf_params}
rrf_params = "rank_window_size" : number [, "rank_constant" : number]
Basic type definitions
-- 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
Detailed parameter description
The detailed parameter description of search_params is as follows:
Expression type |
Parameter name |
Parameter description |
|---|---|---|
| Top-level keyword parameters | ||
| query | Can be used alone for full-text search, or together with the knn parameter for hybrid retrieval. |
|
| knn | Can be used separately for single-path or multi-path vector retrieval, and can also be used together with the query parameter for hybrid retrieval. |
|
| rank (optional) | Specifies the sorting strategy during hybrid retrieval. The RRF (Reciprocal Rank Fusion) algorithm is supported. | |
| _source (optional) | Specifies the columns to return in the query result. If this parameter is omitted, all user-defined columns of the table are returned. | |
| from (optional) | Specifies the row number in the result set to start returning results from. If not specified, the first row is returned by default. This parameter must be used together with the size parameter. |
|
| size (optional) | The function used to limit the returned results. If not specified, no limit is imposed. | |
| es_mode | Specifies whether to convert the full-text search to the new ESQL syntax. Default: false. |
|
| bool | must | Must be met; a score must be calculated. When boolean logic is required internally, a bool expression must be nested. Multiple conditions in a bool expression are combined by default using the AND logic. |
| should | Must be satisfied, similar to OR, requiring score calculation. When boolean logic is needed internally, a bool expression must be nested. Multiple conditions within a bool expression are combined by default using the AND logic. | |
| must_not | Must Not: the condition must not be met; no score is calculated. This is represented as a 'NOT' expression, with multiple 'AND' links within must_not. When boolean logic is required internally, a bool expression must be nested. Multiple conditions in a bool expression are combined by default using the AND logic. | |
| filter | Must be met. No score is calculated. Converted to an 'AND' expression. When boolean logic is required internally, a bool expression must be nested. Multiple conditions in a bool expression are combined by default using the AND logic. | |
| boost (optional) | The query weight. For more information, see the description of the boost parameter below. | |
| rank_feature (parameter for calculating the correlation score) | pivot | saturation and sigmoid are required scoring parameters. The default value is the geometric mean of the data in this list. |
| positive_score_impact (Optional) | Specifies whether the field contributes positively or negatively to the final correlation. | |
| scaling_factor | A required parameter in the log-based scoring formula. | |
| exponent | A required parameter for the sigmoid algorithm calculation formula. | |
| rank_feature (Relevance Score Algorithm) | saturation | Default correlation-based partitioning algorithm
|
| sigmoid |
|
|
| log |
|
|
| linear |
|
|
| single term (search by single entry) | ||
| range | Range search, used with gte, gt, lte, lt, and boost. The fieldname parameter is required. |
|
| match | Fuzzy match. Converted to the SQL 'match' expression for use with Boost. | |
| term | Exact match. Supports scalar values such as strings, numbers, and boolean values. Converts them into SQL's '=' expression for use with Boost. | |
| terms | Matches any value in the specified set with high precision. Supports arrays of scalar values such as strings, numbers, and booleans, which can be converted into an SQL 'IN' expression for use with Boost. | |
| query_string | Full-text match, which is converted into a combination of multiple 'match' expressions in SQL. | |
| multi_match | Full-text match. It is converted into a combination of multiple 'match' expressions in SQL. Similar to query_string, single keywords do not support weights. | |
| fields | The list of text retrieval fields, where you can configure the weight for each column. | |
| query | The list of search keywords. You can configure the weight for each keyword. | |
| minimum_should_match (Optional) | Specifies the number of conditions that must be met in the should or query_string clause. If this parameter is not specified, the default value is 1. Note: If a bool expression contains must/filter and this parameter is not specified, the default value will be 0, meaning any condition in the should clause can be unsatisfied. |
|
| boost (optional) | The query weight. For more information, see the description of the boost parameter below. | |
| type (optional) | Specifies the matching mode. Valid values: best_fields, cross_fields, most_fields, and phrase. If not specified, the default is best_fields. |
|
| default_operator (optional) | A subfield of query_string that specifies the combination logic among multiple keywords. | |
| operator (optional) | The subfield of multi_match, which specifies the combination logic among multiple keywords. | |
| knn (Vector Retrieval) | ||
| field | The field for vector retrieval. | |
| k | The number of rows returned by the vector retrieval query. | |
| query_vector | Specifies the retrieval vector. | |
| filter (optional) | The filter condition. | |
| similarity (optional) | Specifies the filter condition for vector distance. | |
| boost (optional) | The query weight. For more information, see the description of the boost parameter below. | |
| rank (RRF sorting strategy) | rrf | RRF (Reciprocal Rank Fusion) is a sorting strategy used to fuse and rank multiple query results during hybrid retrieval. |
| rank_window_size (optional) | This value specifies the size of the single result set returned by each query. A larger value indicates a higher degree of result correlation but incurs performance overhead. The final sorted result set will be trimmed to the size specified by the size parameter in the search request.The rank_window_size must satisfy both of the following conditions:
size parameter. |
|
| rank_constant (optional) | This value controls the influence of each document pair in the single result set returned by each query on the final sorting result. A larger value indicates that documents with lower rankings have a greater impact on the final result. The default value is 60. |
Detailed description of boost parameters
The boost parameter specifies the weight of a query condition in the final relevance calculation. Its value must be ≥ 0. If not specified, the default value is 1. In the syntax above, specifying the boost parameter is supported for bool, single_term (except for rank_feature), and both single- and multi-way vector retrieval (knn). The usage of the boost parameter varies by retrieval type as follows:
Query-level boosting
Specify a weight for the entire query condition, for example:
{ "bool": { "must": [{"term": {"category": "Gaming"}}], "boost": 2.0 // The weight of the entire Boolean query. } }Field-level boosting
Specify weights for queries on specific fields:
{ "query_string": { "fields": ["product_name", "description"], "query": "gaming keyboard", "boost": 1.5 // The weight of the entire query_string query } }Value-level boosting
Specify weights for specific match values (supported for MATCH, TERM, and TERMS queries):
{ "match" : { "product_name": { "query" : "gaming keyboard", "boost" : 1.5 } } }Syntax of field weights
In query_string and multi_match, you can use the
field_name^weightsyntax:{ "query_string": { "fields": ["product_name^2.0", "description^1.0"], "query": "gaming" } }Vector retrieval boost
In single- and multi-vector retrieval, you can independently specify the
boostweight for each vector field:Single-path vector retrieval:
{ "knn": { "field": "vector", "k": 5, "query_vector": [1, 2, 3], "boost": 1.5 // The weight of this vector field. } }Multi-vector retrieval:
{ "knn": [{ "field": "c2", "k": 5, "query_vector": [1, 2, 3], "boost": 2.0 // The weight of the C2 vector field. }, { "field": "c4", "k": 5, "query_vector": [1, 2, 3], "boost": 1.0 // The weight of the C4 vector field. }] }
Examples
Create a table containing one vector column, create a vector index on it, 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));
Set the retrieval parameters.
SET @parm = '{
"query": {
"bool": {
"should": [
{"match": {"query": "hi hello"}},
{"match": { "content": "oceanbase mysql" }}
],
"filter": [
{"term": { "content" : "postgres" }}
]
}
},
"knn" : {
"field": "vector",
"k": 5,
"query_vector": [1,2,3]
},
"_source" : ["query", "content", "_keyword_score", "_semantic_score"]
}';
Execute the query and return the query results.
SELECT DBMS_HYBRID_SEARCH.GET_SQL('doc_table', @parm);
The returned result is as follows:
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dbms_hybrid_search.get_sql('doc_table', @parm) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT query, content, _keyword_score, _semantic_score, (ifnull(_fts._keyword_score, 0) + ifnull(_vs._semantic_score, 0)) as _score FROM ((SELECT /*+ opt_param('hidden_column_visible', 'true') union_merge( doc_table idx2 idx3)*/__pk_increment, (match(query) against('hi hello' in boolean mode) + match(content) against('oceanbase mysql' in boolean mode)) as _keyword_score FROM wxj.doc_table WHERE (content = 'postgres') ORDER BY _keyword_score DESC) _fts right join (SELECT /*+ opt_param('hidden_column_visible', 'true') */l2_distance(vector, '[1, 2, 3]') as _distance, __pk_increment, query, content, round((1 / (1 + l2_distance(vector, '[1, 2, 3]'))), 8) as _semantic_score FROM wxj.doc_table ORDER BY _distance APPROXIMATE LIMIT 5) _vs on (_fts.__pk_increment = _vs.__pk_increment)) ORDER BY _score DESC |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+