Nova BM25 Function API reference - AnalyticDB - Alibaba Cloud Documentation Center

The Nova BM25 Function API provides a set of SQL functions for expressing full-text search logic in WHERE clauses. Combined with the @@@ operator, these functions enable efficient BM25 relevance-based retrieval.

Overview

The Nova BM25 Function API integrates BM25 full-text search into SQL WHERE clauses through the @@@ operator. The left side of @@@ is the target column for BM25 retrieval, and the right side is a bm25.* query function. The basic syntax is as follows:

WHERE body @@@ bm25.match('full-text search')

Query functions support two invocation modes:

Current column query: The query function acts directly on the column specified to the left of @@@, without requiring an additional field name.
```
WHERE body @@@ bm25.match('full-text search')
```

Specified field query: Use the field parameter to explicitly specify the target field. This mode is suitable for compound queries that require search conditions across multiple fields.

WHERE body @@@ bm25.boolean(
    should => ARRAY[
        bm25.match('title', query => 'full-text search'),
        bm25.match('body', query => 'full-text search')
    ],
    must_not => ARRAY[
        bm25.term('category', 'deleted')
    ]
)

Query tokenizers

Query tokenizers control how query text is tokenized, which affects the granularity of search matching. Use the bm25.tokenizer() function to create a tokenizer instance and pass it to the tokenizer parameter of a query function.

WHERE body @@@ bm25.match(
    'PostgreSQL BM25',
    operator => 'and',
    tokenizer => bm25.tokenizer(name => 'default')
)

The following table lists common tokenizers:

Tokenizer name	Description	Example
jieba	Jieba Chinese tokenizer, suitable for general-purpose Chinese text tokenization.	`bm25.tokenizer(name => 'jieba')`
default	Default tokenizer that splits text by whitespace and punctuation, suitable for English, numbers, and symbol text.	`bm25.tokenizer(name => 'default')`
keyword	No tokenization. Treats the entire input as a single token, suitable for exact matching scenarios.	`bm25.tokenizer(name => 'keyword')`
ik_smart	Smart tokenization mode of the IK tokenizer, performing the coarsest-grained splitting.	`bm25.tokenizer(name => 'ik_smart')`
ik_max_word	Maximum word mode of the IK tokenizer, performing the finest-grained splitting to cover more possible combinations.	`bm25.tokenizer(name => 'ik_max_word')`
ngram	N-gram tokenizer that splits text into fixed-length substrings, suitable for fine-grained splitting, partial matching, and autocomplete scenarios.	`bm25.tokenizer(name => 'ngram')`

Text matching functions

Text matching functions perform relevance-based retrieval on tokenized text content. Different functions provide different matching strategies, including full-text matching, phrase matching, prefix matching, fuzzy matching, and regex matching.

bm25.match

Full-text matching function. Tokenizes the query text and retrieves documents that contain these tokens from the target field, then calculates relevance scores using the BM25 algorithm.

Parameters:

Parameter	Required	Description
query	Yes	The query text. For current column queries, it can be passed directly as the first positional argument. For specified field queries, it must be passed through the `query` named parameter.
field	No	The name of the target field. Required in compound queries that search across fields.
operator	No	The logical relationship between tokens. Set to `or` (default) to match any token, or `and` to require all tokens to match.
tokenizer	No	The query tokenizer configuration. Specify by using `bm25.tokenizer(name => '...')`. For more information, see Query tokenizers.

Examples:

WHERE body @@@ bm25.match('database search ranking')
WHERE body @@@ bm25.match('database search', operator => 'and')

Usage notes:

Using operator => 'and' improves matching precision and is suitable for scenarios that require all keywords in the query text to match.
The default operator => 'or' mode matches more documents and is suitable for recall-priority scenarios.

bm25.multi_match

Multi-field full-text matching function. Performs full-text search across multiple fields simultaneously and supports assigning different relevance weights to different fields by using the caret (^) symbol.

Parameters:

Parameter	Required	Description
fields	Yes	The target field array. Each element is a field name. You can set weights by using the `^` symbol. For example, `'title^5'` sets the weight of the title field to 5.
query	Yes	The query text.
operator	No	The logical relationship between tokens. Set to `or` (default) or `and`.

Example:

WHERE body @@@ bm25.multi_match(
    ARRAY['title^5', 'body^2'],
    query => 'full-text search',
    operator => 'or'
)

Usage notes:

A higher weight value increases the contribution of that field's matches to the final score. When no weight is specified, the default value is 1.

bm25.phrase

Phrase matching function. Requires all tokens from the tokenized query text to appear consecutively in their original order, with no other tokens allowed between them.

Parameters:

Parameter	Required	Description
query	Yes	The phrase text to match exactly.
field	No	The name of the target field.

Example:

WHERE body @@@ bm25.phrase('full-text search')

Usage notes:

Phrase matching is stricter than full-text matching and is suitable for scenarios that require exact token order.

bm25.phrase_prefix

Phrase prefix matching function. Requires the tokens from the tokenized query text to appear in order, with the last token matched as a prefix.

Parameters:

Parameter	Required	Description
terms	Yes	The token array. The last element is matched by prefix; all other elements are matched exactly.
field	No	The name of the target field.

Example:

WHERE body @@@ bm25.phrase_prefix(ARRAY['post'])

Usage notes:

Suitable for autocomplete or partial input matching scenarios, such as returning candidate results when a user enters partial keywords.

bm25.fuzzy_term

Fuzzy term matching function. Based on Levenshtein Distance, this function allows query tokens to differ from indexed tokens by a specified number of character edits. This is suitable for spell correction scenarios.

Parameters:

Parameter	Required	Description
query	Yes	The query token text.
field	No	The name of the target field.
distance	No	The maximum edit distance, which is the maximum number of character differences allowed. Default value: 1. Maximum value: 2. A larger value results in more relaxed matching and higher performance overhead.

Example:

WHERE body @@@ bm25.fuzzy_term('keybord', distance => 1)

Usage notes:

In the preceding example, 'keybord' has an edit distance of 1 from 'keyboard' (missing the letter e), so it can match documents containing 'keyboard'.
We recommend that you keep the distance value at 2 or below. A larger edit distance results in overly broad matching and degraded performance.

bm25.regex and bm25.regex_phrase

Regular expression matching functions. bm25.regex performs regex matching on a single token. bm25.regex_phrase performs regex matching at the phrase level, requiring multiple tokens to appear in order with each token satisfying its corresponding regex pattern.

Parameters:

Parameter	Required	Description
pattern	Yes	The regex pattern. `bm25.regex` accepts a single string. `bm25.regex_phrase` accepts a string array, where each element corresponds to the matching pattern for one position in the phrase.
field	No	The name of the target field.

Example:

WHERE body @@@ bm25.regex('post.*')

Usage notes:

Regex matching operates on tokens in the index, not on the original document text. Write regular expressions based on the tokenized format.
Complex regular expressions may incur significant performance overhead. Use them only when necessary.

bm25.span_near

Advanced proximity matching function. Requires multiple specified tokens to appear close to each other in the document, with control over the maximum allowed gap between tokens.

Parameters:

Parameter	Required	Description
clauses	Yes	An array of span query clauses, such as `bm25.span_term`.
slop	Yes	The maximum number of intervening tokens allowed. A value of 0 requires all tokens to be adjacent and in order.

Example:

WHERE body @@@ bm25.span_near(
    ARRAY[bm25.span_term('postgresql'), bm25.span_term('bm25')],
    2
)

Usage notes:

The preceding example requires a maximum gap of 2 tokens between the tokens 'postgresql' and 'bm25'.
This is an advanced feature suitable for complex retrieval scenarios that require precise control over token positional relationships.

Exact value and range functions

Exact value and range functions perform exact matching or range filtering on field values without tokenization. These functions are suitable for keyword, numeric, date, and other field types.

bm25.term

Exact value matching function. Retrieves documents where the field value exactly equals the specified value, without tokenization.

Parameters:

Parameter	Required	Description
value	Yes	The value to match exactly. Supports strings, numbers, and other types.
field	No	The name of the target field. Required in compound queries that search across fields.

Examples:

WHERE tag @@@ bm25.term('search')
WHERE rating @@@ bm25.term(5)

Usage notes:

For string-type fields, matching is case-sensitive.

bm25.term_set

Multi-value exact matching function. Retrieves documents where the field value exactly equals any value in the specified set.

Parameters:

Parameter	Required	Description
values	Yes	The array of values to match. A document matches if its field value equals any element in the array.
field	No	The name of the target field.

Example:

WHERE tag @@@ bm25.term_set(ARRAY['database', 'search'])

Usage notes:

This is equivalent to performing OR exact matching on multiple values, and is more concise and efficient than combining multiple bm25.term queries.

bm25.range

Range matching function. Retrieves documents where the field value falls within a specified range. Supports PostgreSQL range type syntax.

Parameters:

Parameter	Required	Description
range	Yes	A PostgreSQL range value. Use standard range syntax to specify upper and lower bounds: `[a,b]` for a closed interval, `(a,b)` for an open interval, `[a,)` for a range from a to positive infinity. A type cast is required to specify the data type, such as `::int4range`.
field	No	The name of the target field.

Example:

WHERE rating @@@ bm25.range('[4,)'::int4range)

Usage notes:

The preceding example matches all documents where rating is greater than or equal to 4.
The range type must match the field data type. Common range types include int4range (integer), numrange (numeric), and tsrange (timestamp).

bm25.exists

Field existence check function. Retrieves documents where the specified field contains a non-null value.

Parameters:

This function does not require any parameters.

Example:

WHERE rating @@@ bm25.exists()

Usage notes:

This function only checks whether the field contains a value, regardless of the specific value. It is suitable for filtering out documents that lack a specific field.

Compound query functions

Compound query functions combine multiple query conditions into complex retrieval logic. They support Boolean combination, weight adjustment, constant scoring, and optimal selection strategies.

bm25.boolean

Boolean compound query function. Combines multiple sub-queries along three dimensions: must (must match), should (should match, affects scoring), and must_not (must not match).

Parameters:

Parameter	Required	Description
must	No	The array of queries that must match. All `must` conditions must be satisfied, and they contribute to the relevance score.
should	No	The array of queries that should match. Documents that match score higher, but matching is not mandatory. When no `must` conditions are specified, at least one `should` condition must match.
must_not	No	The array of queries that must not match. Documents that satisfy `must_not` conditions are excluded. This dimension does not affect the relevance score.

Example:

WHERE body @@@ bm25.boolean(
    must => ARRAY[bm25.match('body', query => 'database', operator => 'and')],
    should => ARRAY[bm25.match('title', query => 'database')],
    must_not => ARRAY[bm25.term('category', 'deleted')]
)

Usage notes:

At least one of the three parameters must be specified.
Each array element can be any bm25.* query function, and nesting is supported.

bm25.boost

Score weighting function. Multiplies the relevance score of a specified sub-query by a fixed factor, used to increase or decrease the influence of specific conditions on the final score.

Parameters:

Parameter	Required	Description
factor	Yes	The score multiplier, a positive floating-point number. A value greater than 1 increases the weight; a value less than 1 decreases it.
query	Yes	The target query function to weight.

Example:

bm25.boost(5.0, bm25.match('title', query => 'full-text search', operator => 'and'))

Usage notes:

The preceding example amplifies the title field match score by 5 times, making title matches rank higher in results.

bm25.const_score

Constant score function. Replaces the relevance score of a specified sub-query with a fixed value, giving all matching documents the same score.

Parameters:

Parameter	Required	Description
score	Yes	The constant score value, a positive floating-point number.
query	Yes	The target query function. Used only for filtering documents; the score is replaced with the constant value.

Example:

WHERE body @@@ bm25.const_score(1.0, bm25.match('body', query => 'database', operator => 'and'))

Usage notes:

Suitable for scenarios where you only need filtering without relevance-based ranking, or compound queries where all matching documents should receive the same weight.

bm25.disjunction_max

Optimal selection query function. Selects the highest score from multiple sub-queries as the final score, and optionally applies additional weight to other matching sub-query scores through the tie_breaker parameter.

Parameters:

Parameter	Required	Description
disjuncts	Yes	The sub-query array. The final score is the highest score among all sub-queries.
tie_breaker	No	The additional weight coefficient, in the range `[0.0, 1.0]`. When multiple sub-queries match simultaneously, the scores of other matching sub-queries are multiplied by this coefficient and added to the highest score. Default value: 0, which means only the highest score is used.

Example:

WHERE description @@@ bm25.disjunction_max(
    disjuncts => ARRAY[
        bm25.boost(3.0, bm25.match('title', query => 'wireless headphones', operator => 'and')),
        bm25.match('description', query => 'wireless headphones', operator => 'and')
    ],
    tie_breaker => 0.2
)

Usage notes:

In the preceding example, if both the title and description match, the final score is the weighted title score plus 0.2 times the description score.
Suitable for multi-field retrieval scenarios where the best-matching field dominates the score while still accounting for contributions from other fields.