SEARCH

Use the DBMS_HYBRID_VECTOR.SEARCH PL/SQL function to run textual queries, vector similarity queries, or hybrid queries against hybrid vector indexes.

Purpose

To search by vectors and keywords. This function lets you perform the following tasks:

Syntax

DBMS_HYBRID_VECTOR.SEARCH(
   json(
     '{  "hybrid_index_name"     :  "<hybrid_vector_index_name>",
         "partition_name"        :  "<index partition name>",
         "search_text"           :  "<query string for keyword-and-semantic search>",
         "search_fusion"         :  one of these values : "INTERSECT | UNION | TEXT_ONLY | VECTOR_ONLY | MINUS_TEXT |
                                         MINUS_VECTOR | RERANK",
         "search_scorer"         :  one of these values : "RRF | RSF | WRRF",
         "score_calc"            :  "<a mathematical expression for custom scoring calculation>",
         "vector":
           {
            "search_text"             :  "<query string for semantic search>",
            "search_vector"           :  "<vector_embedding>",
            "search_mode"             :  one of these values : "DOCUMENT | CHUNK",
            "aggregator"              :  one of these values : "COUNT | SUM | MIN | MAX | AVG | MEDIAN | BONUSMAX | WINAVG |
                                         ADJBOOST | MAXAVGMED",

            "result_max"              :  <maximum number of vector results>,
            "score_weight"            :  <weight of vector score for RSF>,
            "rank_penalty"            :  <penalty of vector ranking for RRF>,
            "inpath"                  :  <an array of valid JSON paths>,
            "accuracy"                :  <target accuracy for semantic search>,
            "index_probes"            :  <neighbor partitions for semantic search>,
            "index_efsearch"          :  <efsearch for semantic search>,
            "filter_type"             :  one of these values : "IN_WO | IN_W | PRE_WO | PRE_W | POST_WO | DEFAULT"
           },
         "text":
           {
            "contains"                :  "<query string for keyword search>",
            "search_text"             :  "<alternative text to use to construct a contains query automatically>",
            "json_textcontains"       :  <an array of valid JSON path and a query string>,
            "score_weight"            :  <weight of text score for RSF>,
            "rank_penalty"            :  <penalty of text ranking for RRF>,
            "result_max"              :  <maximum number of document results>,
            "inpath"                  :  <array of valid JSON paths>,
            "snippet"                 :  <length of the snippet>
           },
         "filter_by":
           {
            "op"                      :  one of these values: "< | > | <= | >= | = | != | ^= | <> | LIKE | LIKEC | LIKE2 | LIKE4 |
                                         REGEXP_LIKE | BETWEEN | EXISTS | INSTR | INSTRC | INSTR2 | INSTR4 | STSTR | STSTR2 | STSTR4 |
                                         STSTRB | STSTRC | <ANY | >ANY | <=ANY | >=ANY | =ANY | !=ANY | <SOME | >SOME |
                                         <=SOME | >=SOME | =SOME | !=SOME | <ALL | >ALL | <=ALL | >=ALL | =ALL | !=ALL | IN |
                                         AND | OR | NOT | NOTOR | NOTAND",
            "type"                    :  one of these values : "number | string | date | timestamp",
            "col"                     :  "<base table column name>",
            "path"                    :  "<JSON path dot notation within a base table JSON column>",
            "func"                    :  one of these values : "ABS | FLOOR | LENGTH | CEILING | UPPER | LOWER | TO_BOOLEAN |
                                         TO_DATE | TO_DOUBLE | TO_BINARYDOUBLE | TO_NUMBER | TO_CHAR | TO_TIMESTAMP",
            "args"                    :  <an array of arguments to the operator>,
            "passing"                 :  <an array of variable bindings for the EXISTS operator path expression>
           }
         "return":
           {
            "topN"                    :  <topN_value>,
            "values"                  :  one or more of these values : "rowid | score | vector_score | text_score | vector_rank |
                                         text_rank | chunk_text | chunk_id | paths",
            "format"                  :  one of these values : "JSON | XML"

           }
     }'
  )
)

Note: This API supports two constructs of search. One where you specify a single search_text field for both semantic search and keyword search (default setting). Another where you specify separate search_text and contains query fields using vector and text sub-elements for semantic search and keyword search, respectively. You cannot use both of these search constructs in one query.

hybrid_index_name

Specify the name of the hybrid vector index to use.

For information on how to create a hybrid vector index if not already created, see Manage Hybrid Vector Indexes.

partition_name

Specify the index partition name so that the results are returned only from the specified partition. Please refer to Manage Hybrid Vector Indexes to understand creation of Local Hybrid Vector Indexes which allows you to create local partitions.

SELECT DBMS_HYBRID_VECTOR.SEARCH(
    json('{ "hybrid_index_name" : "my_hybrid_idx",
            "partition_name"    : "SYS_IDX_P1",
            "search_text"       : "leadership experience"
          }'))
FROM DUAL;

Note: SYS_IDX_P1 is a sample partition name. Specify the custom partition name or system generated partition name which you used when creating a local hybrid vector index.

search_text

Specify a search text string (your query input) for both semantic search and keyword search.

The same text string is used for a keyword query on document text index (by converting the search_text into a CONTAINS ACCUM operator syntax) and a semantic query on vectorized chunk index (by vectorizing or embedding the search_text for a VECTOR_DISTANCE search).

For example:

SELECT DBMS_HYBRID_VECTOR.SEARCH(
    json('{ "hybrid_index_name" : "my_hybrid_idx",
            "search_text"       : "C, Python"
          }'))
FROM DUAL;

Note: The search_text parameter uses the same embedding model specified in the vectorizer during index creation. This parameter converts the textual input in search_text into a vector at query time. However, do not use the search_text parameter at query time for semantic searches in cases where an embedding model is not specified in the vectorizer because the input JSON data already contains vector fields. In such cases, you should explicitly specify the query vector by using the search_vector parameter.

search_fusion

Specify a fusion sort operator to define what you want to retain from the combined set of keyword-and-semantic search results.

Note: This search fusion operation is applicable only to non-pure hybrid search cases. Vector-only and text-only searches do not fuse any results.

Parameter Description
INTERSECT

Returns only the rows that are common to both text search results and vector search results.

Score condition: text_score > 0 AND vector_score > 0
UNION (default) Combines all distinct rows from both text search results and vector search results.
Score condition: text_score > 0 OR vector_score > 0
TEXT_ONLY Returns all distinct rows from text search results plus the ones that are common to both text search results and vector search results. Thus, the fused results contain the text search results that appear in text search, including those that appear in both.
Score condition: text_score > 0
VECTOR_ONLY Returns all distinct rows from vector search results plus the ones that are common to both text search results and vector search results. Thus, the fused results contain the vector search results that appear in vector search, including those that appear in both.
Score condition: vector_score > 0
MINUS_TEXT Returns all distinct rows from vector search results minus the ones that are common to both text search results and vector search results. Thus, the fused results contain the vector search results that appear in vector search, excluding those that appear in both.
Score condition: text_score = 0
MINUS_VECTOR Returns all distinct rows from text search results minus the ones that are common to both text search results and vector search results. Thus, the fused results contain the text search results that appear in text search, excluding those that appear in both.
Score condition: vector_score = 0
RERANK

Returns all distinct rows from text search ordered by the aggregated vector score of their respective vectors. There is no score condition for this field since the text search is followed by the use of the aggregated document vector scores.

Note:

See Also: Usage Notes

For example:

SELECT DBMS_HYBRID_VECTOR.SEARCH(
    json('{ "hybrid_index_name"     : "my_hybrid_idx",
            "search_fusion"         : "UNION",
            "vector":
                    { "search_text" : "leadership experience" },
             "text":
                    { "contains"    : "C and Python" }
          }'))
FROM DUAL;

Usage Notes

When using DBMS_HYBRID_VECTOR.SEARCH with search_fusion set to RERANK and search_scorer set to RRF (Reciprocal Rank Fusion) or WRRF (Weighted Reciprocal Rank Fusion), the results may not reflect the intended semantic ordering. Instead, the final ranking may prioritize rows based on their text rank rather than the semantic rerank score derived from vector distances. This behavior can lead to misleading results where rows with lower vector scores are ranked higher due to their higher text ranks.

RERANK mode is not a fusion search mode, despite being specified via the search_fusion parameter. Instead, RERANK performs a text keyword search and then uses vector scores to rerank the results. When RRF or WRRF is used with RERANK mode these scorers do not properly aggregate vector distances or semantic scores. Instead, it relies solely on the reciprocal text ranks and does not incorporate the semantic scores, even though they are computed. This behavior can lead to misleading results where rows with lower semantic scores (vector distances) are ranked higher due to their higher text ranks. Additionally, RERANK only considers documents retrieved by the initial text search (limited by text.result_max), meaning better semantically scoring documents may exist but are not evaluated. As a result, the final output may prioritize rows with higher text ranks, even if their vector scores are lower.

Applications relying on DBMS_HYBRID_VECTOR.SEARCH with RERANK and RRF or WRRF may receive results that appear valid but do not reflect the intended semantic ordering. This can lead to:

To achieve proper semantic reranking, avoid using RRF or WRRF scorers with search_fusion=RERANK. It is recommended to use search_scorer=RSF (Relative Score Fusion) for reranking, as it correctly prioritizes rows based on their vector scores.

search_scorer

Specify a method to evaluate the combined “fusion” search scores from both keyword and semantic search results.

For a deeper understanding of how these algorithms work in hybrid search modes, see Understand Hybrid Search.

For example:

With a single search text string for hybrid search:

SELECT DBMS_HYBRID_VECTOR.SEARCH(
    json(
      '{ "hybrid_index_name" : "my_hybrid_idx",
         "search_text"       : "C, Python",
         "search_scorer"     : "rsf"
      }'))
FROM DUAL;

With separate vector and text search strings:

SELECT DBMS_HYBRID_VECTOR.SEARCH(
    json(
      '{ "hybrid_index_name" : "my_hybrid_idx",
         "search_scorer"     : "rsf",
         "vector":
          { "search_text"    : "leadership experience" },
         "text":
          { "contains"       : "C and Python" }
      }'))
FROM DUAL;

score_calc

While you can use the standard algorithms such as RSF, RRF and WRRF to evaluate the combined “fusion” search scores from both keyword and semantic search results, you can also define and use your own custom score calculations. The score_calc field allows you to define the score computation. Note: You can only use one of the two fields to obtain the search score. Either use the field search_scorer which uses the standard RSF, RRF or WRRF algorithms. Or, use the score_calc to define a custom scoring algorithm.

The score_calc field accepts either a math expression or a case expression.

The expression consists of two parts: an operator (op) and one or more operands (opnds), depending on the chosen operator. The format of both math and case expressions are shown below:

A mathematical expression contains a math operator specified as op and corresponding operands specified as opnds. The number of operands depends on the choice of the operator. The table below lists the possible operators and operands.

MATH_EXPR := { "op" : "MATH_OPERATOR", "opnds" : [ "OPERAND1", ... ] }
OPERAND := COLUMN_NAME | PARAMETER_NAME | NUMBER | EXPR

The following table specifies the possible values for operators (op) and operands (OPERAND) specified in the score_calc expression.

Table 34 Possible Values for Operators and Operands

Math Expression Case Expression
operator - op One of the following mathematical operators:
  • MUL, ADD, SUB, DIV - requires two or more operands to be specified.
  • ABS, CEIL, EXP, FLOOR, LN, SQRT - requires only one operands to be specified.
  • LOG, MOD, POWER, REMAINDER, ROUND - requires only two operands to be specified.
  • LEAST, GREATEST - requires two or more operands to be specified.
case
operands - OPERAND

The OPERAND can be one of the following : COLUMN_NAME, PARAMETER_NAME, NUMBER or a EXPR The number of required operands depends on the operator.

Possible column names (COLUMN_NAME) :
  • text_score representing the text contains score
  • vector_score representing the vector distance score (aggregated if DOCUMENT mode)
  • text_rank representing the rank of the text result
  • vector_rank representing the rank of the vector result
parameter names :
  • text_weight refers to text.score_weight parameter
  • text_penalty refers to text.rank_penalty parameter
  • vector_weight refers to vector.score_weight parameter
  • vector_penalty refers to vector.rank_penalty parameter

NUMBER can contain only numbers

EXPR is a sub-expression

A case expression has a CASE operator and must have pairs of conditional expressions (COND_EXPR) and OPERANDs as shown below :

CASE_EXPR := { "op" : "case", "opnds" : [ "COND_EXPR", "OPERAND1", ..., "else", OPERAND-ELSE ] }

The possible OPERAND values are specified in the table above. The COND_EXPR is also a JSON element form containing operators (op) and operands (opnds). The possible formats of the COND_EXPR is specified below:

COND_EXPR  :=  { "op" : "COMPARATIVE_OPERATOR", "opnds" : ["ARGUMENT1", ... ] }
COND_EXPR  :=  { "op" : "LOGICAL_OPERATOR", "opnds" : ["COND_EXPR", ... ] }

Possible values of COMPARATIVE_OPERATOR:

Possible values of LOGICAL_OPERATOR:

Note: The comparative operator ARGUMENT can be any of the OPERAND types specified in the table above, except for a sub-expression (EXPR).

For example, this code defines a custom score depending on one of the following cases:

SELECT dbms_hybrid_vector.search(
       JSON('{ "hybrid_index_name" : "my_hybrid_idx",
               "vector" : { "search_text" : "database leadership" },
               "text" : { "contains" : "strong AND database" },
               "search_fusion" : "UNION",
               "score_calc" :
                  { "op" : "case",
                    "opnds" : [ { "op" : "eq", "opnds" : [ "text_score", 0 ]},
                                { "op" : "add", "opnds" : [
                                    50,
                                    { "op" : "mul",
                                      "opnds" : [ "vector_score", 0.25 ] } ] },
                                { "op" : "eq", "opnds" : [ "vector_score", 0]},
                                { "op" : "add", "opnds" : [
                                    25,
                                    { "op" : "mul",
                                      "opnds" : [ "text_score", 0.25 ] } ] },
                                "ELSE",
                                { "op" : "add", "opnds" : [
                                    75,
                                    { "op" : "mul",
                                      "opnds" : [ "text_score", 0.125] },
                                    { "op" : "mul",
                                      "opnds" : [ "vector_score", 0.125] } ] }
                               ] }
             }'))
FROM DUAL;

The score_calc defined in the above example translates to :

(CASE WHEN tscr > 0 AND vscr > 0 THEN
           75 + (tscr * 0.125) + (vscr * 0.125)
      WHEN tscr = 0 THEN
           50 + (vscr * 0.25)
      WHEN vscr = 0 THEN
           25 + (tscr * 0.25)
      ELSE 0.0 END)

vector

Specify query parameters for semantic search against the vector index part of your hybrid vector index:

rank_penalty: Penalty (denominator in RRF, represented as 1/(rank+penalty) to assign to vector query. This can help in balancing the relevance score by reducing the importance of unnecessary or repetitive words in a document. This value is used when combining the results of RRF ranking.

Value: 0 (zero) or any positive integer

Default: 1

For example:

SELECT DBMS_HYBRID_VECTOR.SEARCH(
    json(
      '{ "hybrid_index_name" : "my_hybrid_idx",
         "search_scorer"     : "rrf",
         "vector":
          {
             "search_text"   : "leadership experience",
             "search_mode"   : "DOCUMENT",
             "aggregator"    : "MAX",
             "score_weight"  : 5,
             "rank_penalty"  : 2
          }
      }'))
FROM DUAL;

text

Specify query parameters for keyword search against the Oracle Text index part of your hybrid vector index:

Note: It is an error to specify json_textcontains WITH either text.contains or text.search_text.

SELECT DBMS_HYBRID_VECTOR.SEARCH(
    json('{ "hybrid_index_name" : "my_hybrid_idx",
            "text":
                   { "json_textcontains"    : ["$.person", "$C and $Python"]
                   }
          }'))
FROM DUAL;

whether to enable the snippet depends on both the search_mode and the search_fusion settings that you specify.

search_mode determines how to query the hybrid vector index. In the CHUNK mode, results are fused with vector information. In the DOCUMENT mode, the results are not fused with vector information. The CHUNK mode does not require snippet as the results are fused with vector information. The DOCUMENT mode may require snippet depending upon the search_fusion value that you specify to combine the text and vector search results.

search_fusion determines how the results from text and vector searches are combined. Different fusion modes affect whether the combined (fused) result set contains text search results or vector search results. Text search results require a text snippet to provide meaningful summaries to users. The following table illustrates the seven search_fusion values, how the results are returned for each value, and whether a text snippet is required:

Table 36 Fusion Modes and Snippet Generation

search_fusion Description Only Text Search Results Only Vector Search Results Both Text and Vector Search Results Vector chunk_text Text Snippet Requirement
INTERSECT The fused results contain both text and vector search results. No No Yes Yes No
UNION The fused results contain either text or vector search results, or both. Yes Yes Yes Maybe Maybe
TEXT_ONLY The fused results contain the text search results that appear in text search, including those that appear in both text and vector search results. Yes No Yes Maybe Maybe
VECTOR_ONLY The fused results contain the vector search results that appear in vector search, including those that appear in both vector and text search results. No Yes Yes Yes No
MINUS_TEXT The fused results contain the vector search results that appear in vector search, excluding those that appear in both text and vector search results. No Yes No Yes No
MINUS_VECTOR The fused results contain the text search results that appear in text search, excluding those that appear in both text and vector search results. Yes No No No Yes
RERANK Text search results, re-ranked based on their vector score. Yes No No No Yes

Note: Alternatively snippets can be generated outside of the API using a syntax like below:

SELECT NVL(chunk_text, CTX_DOC.SNIPPET(...)) chunk_text FROM JSON_TABLE(dbms_hybrid_vector.search(params), COLUMNS ...)

Using this syntax is beneficial in the following cases: Require snippets for both text and vector search results: You want to create snippets for both text and vector search results to provide a uniform experience or to ignore semantic content in previews. When the snippet parameter is enabled, Oracle generates text snippets for text search results that are not fused with vector information. Therefore, you can use this API to generate snippets for both text and vector search results. Performance optimization: Generating snippets during the search can add overhead. You may prefer to skip snippet generation initially and produce snippets only on demand.

filter_by

To constrain the search results via standard relational logical constraints :

Parameter Value
op

Logical comparison operator. Accepted values - One of these operators :

  • Simple comparison operators : '<', '>', '<=', '>=', '=', '!=', '^=', '<>', 'LIKE', 'LIKEC', 'LIKE2', 'LIKE4', 'INSTR', 'INSTR2', 'INSTR4','INSTRB', 'INSTRC', 'STSTR', 'STSTR2', 'STSTR4','STSTRB', 'STSTRC', 'REGEXP_LIKE', 'BETWEEN', 'EXISTS'

    Note:

    1. STSTR is the only non-standard operator in the list. It stands for "START STRING" and is analogous to INSTR, but the result must be equal to position 1.
    2. The EXISTS operator translates into a JSON_EXISTS(col, arg1) condition. The first string argument is the path-expression. The full SQL JSON_EXISTS supports other parameters, including the PASSING clause which provides bind variables to reference in the path expression. To support the passing clause, the filter_by element has an optional passing parameter, which is explained here.
  • Group comparison operators :
    • 18 combinations of '<', '>', '<=', '>=', '=', '!=' with ANY, SOME, ALL
    • IN
  • Logical operators : 'AND', 'OR', 'NOT', 'NOTAND', 'NOTOR'

    Note:

    "NOTAND" and "NOTOR" are short-hand for the following expressions, useful in reducing the JSON expression tree. NOTOR is NOT ( arg1 OR arg2 ...). NOTAND is NOT ( arg1 AND arg2 ...)

col

Base table column name.

Note:

  • No column is required for logical operators.
  • Only one of col or path could be specified in the same element.

path

The JSON path dot notation within a base table JSON column.

Note:

  • No path is required for logical operators.
  • Only one of col or path could be specified in the same element.
  • If the base table has a JSON column called data, then the syntax would be "data.path" where the path is case-sensitive matching the JSON data schema. For more details, see JSON dot notation.

type The data type of the column. Accepted types include : number, date, timestamp and string.
func

For the comparison operators, an optional function can be applied to the column value before the comparison. These functions are the standard SQL functions. The one exception is "TO_DOUBLE" is provided as an alias to the full name "TO_BINARY_DOUBLE"

Accepted values include : ABS, FLOOR, LENGTH, CEILING, UPPER, LOWER, TO_BOOLEAN, TO_DATE, TO_DOUBLE, TO_BINARY_DOUBLE, TO_NUMBER, TO_CHAR, TO_TIMESTAMP.

args

An array of arguments to the operator:

  • For simple comparison operators, the args contains a single literal value. It is an error to provide 0 or more than 1 arguments.
  • For group comparison operators, the args contains 1 or more literal values. It is an error to provide 0 arguments.
  • For logical operators, the args contains sub-elements of the same structure, forming an expression tree.

passing

An array of variable bindings for the EXISTS operator path expression (ignored if not EXISTS). Each array element has three required attributes: var, type, val. For example, the following filter_by parameters translates to JSON_EXISTS(COLUMN_NAME, ARG1, PASSING TO_TYPE('VALUE') AS "VARIABLE", ....)

{ "op" : "EXISTS",  "col" : COLUMN_NAME,  "type" : "STRING",  "args" : [ ARG1 ],  "passing" : [ { "var" : VARIABLE, "type" : TYPE, "val" : VALUE }, ... ]}

More details on JSON_EXISTS condition can be found here.

For example: Using simple comparison operators

SELECT DBMS_HYBRID_VECTOR.SEARCH(
    json('{ "hybrid_index_name" : "my_hybrid_idx",
            "filter_by":
                    { "op"   : "<",
                      "col"  : "price",
                      "type" : "number",
                      "func" : "ABS"
                      "args" : ["10"] }
          }'))
FROM DUAL;

For example: Using group comparison operators

SELECT DBMS_HYBRID_VECTOR.SEARCH(
    json('{ "hybrid_index_name" : "my_hybrid_idx",
            "filter_by":
                    { "op"   :  "IN",
                      "path" : "DATA.brand",
                      "type" " "string",
                      "args" : ["nike", "adidas"] }
          }'))
FROM DUAL;

For example: Using logical operators

SELECT DBMS_HYBRID_VECTOR.SEARCH(
    json('{ "hybrid_index_name" : "my_hybrid_idx",
            "filter_by":
                    { "op"   :  "AND",
                      "args" : [
                      {"op" : "IN", "col" : "brand", "type" : "string", "args" : ["nike", "adidas"]},
                      {"op" : "<", "col" : "price", "type" : "number", "args" : ["10"]}]
                    }
          }'))
FROM DUAL;

For example: Passing a JSON array as a variable binding

SELECT DBMS_HYBRID_VECTOR.SEARCH(
    json('{ "hybrid_index_name" : "my_hybrid_idx",
            "filter_by":
                    { "op"      :  "EXISTS",
                      "col"     :  "data",
                      "type"    :  "string",
                      "args"    :  [ "$?(@.dateline == $v1[*])" ],
                      "passing" :  [ {"var" : "v1", "type" : "JSON", "val" : "[ "HOUSTON (AP)" ]" }]
                    }
          }'))
FROM DUAL;

return

Specify which fields to appear in the result set:

Parameter Description
topN

Maximum number of best-matched results to be returned

Value: Any integer greater than 0 (zero)

Default: 20
values

Return attributes for the search results. Values for scores range between 100 (best) to 0 (worse).

  • rowid: Row ID associated with the source document.

  • score: Final score computed from keyword-and-semantic search scores.

  • vector_score: Semantic score from vector search results.

  • text_score: Keyword score from text search results.

  • vector_rank: Ranking of chunks retrieved from semantic or VECTOR_DISTANCE search.

  • text_rank: Ranking of documents retrieved from keyword or CONTAINS search.

  • chunk_text: Human-readable content from each chunk.

  • chunk_id: ID of each chunk text.

  • paths: Paths from which the result occurred.

Default: All the above return attributes EXCEPT paths are shown by default. As there are no paths for non-JSON, you need to explicitly specify the paths field.

format

Format of the results as:

  • JSON (default)

  • XML

For example:

SELECT DBMS_HYBRID_VECTOR.SEARCH(
    json(
      '{ "hybrid_index_name" : "my_hybrid_idx",
         "search_text"       : "C, Python",
         "return":
          {
             "values"        : [ "rowid", "score", "paths" ],
             "topN"          : 10,
             "format"        : "JSON"

          }
      }'))
FROM DUAL;

Complete Example With All Query Parameters

The following example shows a hybrid search query that performs separate text and vector searches against my_hybrid_idx. This query specifies the search_text for vector search using the vector_distance function as prioritize teamwork and leadership experience and the keyword for text search using the contains operator as C and Python. The search mode is DOCUMENT to return the search results as topN documents.

SELECT JSON_SERIALIZE(
  DBMS_HYBRID_VECTOR.SEARCH(
    json(
      '{ "hybrid_index_name" : "my_hybrid_idx",
         "search_fusion"     : "INTERSECT",
         "search_scorer"     : "rsf",
         "vector":
          {
             "search_text"       : "prioritize teamwork and leadership experience",
             "search_mode"       : "DOCUMENT",
             "score_weight"      : 10,
             "rank_penalty"      : 1,
             "aggregator"        : "SCORE_AGGR",
             "aggregator_params" : ["AVGN", 5, 50],
             "inpath"            : ["$.main.body", "$.main.summary"],
             "accuracy"          : 95
          },
         "text":
          {
             "contains"      : "C and Python",
             "score_weight"  : 1,
             "rank_penalty"  : 5,
             "inpath"        : ["$.main.body"]
          },
         "return":
          {
             "format"        : "JSON",
             "topN"          : 3,
             "values"        : [ "rowid", "score", "vector_score",
                                 "text_score", "vector_rank",
                                 "text_rank", "chunk_text", "chunk_id", "paths" ]
          }
      }'
    )
  ) pretty)
FROM DUAL;

The top 3 rows are ordered by relevance, with higher scores indicating a better match. All the return attributes are shown by default:

[
  {
    "rowid"         : "AAAR9jAABAAAQeaAAA",
    "score"         : 58.64,
    "vector_score"  : 61,
    "text_score"    : 35,
    "vector_rank"   : 1,
    "text_rank"     : 2,
    "chunk_text"    : "Candidate 1: C Master. Optimizes low-level system (i.e. Database)
                       performance with C. Strong leadership skills in guiding teams to
                       deliver complex projects.",
    "chunk_id"      : "1",
    "paths"         : ["$.main.body","$.main.summary"]
  },
  {
    "rowid"         : "AAAR9jAABAAAQeaAAB",
    "score"         : 56.86,
    "vector_score"  : 55.75,
    "text_score"    : 68,
    "vector_rank"   : 3,
    "text_rank"     : 1,
    "chunk_text"    : "Candidate 3: Full-Stack Developer. Skilled in Database, C, HTML,
                       JavaScript, and Python with experience in building responsive web
                       applications. Thrives in collaborative team environments.",
    "chunk_id"      : "1",
    "paths"         : ["$.main.body", "$.main.summary"]
  },
  {
    "rowid"         : "AAAR9jAABAAAQeaAAD",
    "score"         : 51.67,
    "vector_score"  : 56.64,
    "text_score"    : 2,
    "vector_rank"   : 2,
    "text_rank"     : 3,
    "chunk_text"    : "Candidate 2: Database Administrator (DBA). Maintains and secures
                       enterprise database (Oracle, MySql, SQL Server). Passionate about
                       data integrity and optimization. Strong mentor for junior DBA(s).",
    "chunk_id"      : "1",
    "paths"         : ["$.main.body", "$.main.summary"]
  }
]

End-to-end example:

To see how to create a hybrid vector index and explore all types of queries against the index, see Query Hybrid Vector Indexes End-to-End Example.

Related Topics