AnalyticDB:Nova BM25 common query scenarios

Natural text query (BM25 ranking)

Use the following query when you need relevance-ranked retrieval for natural language text. By default, bm25.match(text) is equivalent to operator => 'or', which returns a document if any token matches. For more information about function parameters, see Function API reference.

SELECT id, title, bm25.score(docs) AS score
FROM docs
WHERE body @@@ bm25.match('database search ranking')
ORDER BY bm25.score(docs) DESC
LIMIT 20;

All tokens must match (AND matching)

Use the following query when you need to ensure that all tokens from the query text are matched. Set operator to 'and' for AND matching. You can also use the &&& shorthand operator.

SELECT id, title, bm25.score(docs) AS score
FROM docs
WHERE body @@@ bm25.match('database search', operator => 'and')
ORDER BY bm25.score(docs) DESC
LIMIT 20;

You can also use the shorthand operator:

SELECT id, title, bm25.score(docs) AS score
FROM docs
WHERE body &&& 'database search'
ORDER BY bm25.score(docs) DESC
LIMIT 20;

Chinese phrase query

Use the following query when you need to match a contiguous Chinese phrase exactly. bm25.phrase requires the tokens to appear consecutively in their original order.

SELECT id, title, bm25.score(docs) AS score
FROM docs
WHERE body @@@ bm25.phrase('full-text search')
ORDER BY bm25.score(docs) DESC
LIMIT 20;

Log and JSON content query

Use the following query when you need to perform full-text search on unstructured text content such as logs, JSON, or HTML. We recommend that you pass the raw content directly to bm25.match as the query parameter.

SELECT id, title, bm25.score(docs) AS score
FROM docs
WHERE body @@@ bm25.match(
    $q${"level":"error","msg":"full-text search failed","trace_id":"abc-123"}$q$,
    operator => 'or'
)
ORDER BY bm25.score(docs) DESC
LIMIT 20;

Structured filtering (combined SQL conditions)

Use the following query when you need to further filter results by structured fields such as category, rating, or time alongside full-text search. Combine the @@@ full-text search condition with standard SQL conditions in the WHERE clause.

SELECT id, title, bm25.score(docs) AS score
FROM docs
WHERE body @@@ bm25.match('database search')
  AND category = 'tech'
  AND rating >= 4
  AND publish_at >= '2024-01-01'
ORDER BY bm25.score(docs) DESC
LIMIT 20;

Multi-field weighted query

Use the following query when you need to search across multiple fields simultaneously and assign different weights to each field. Use the bm25.multi_match function to specify weight coefficients for each field, such as a title weight of 5 and a body weight of 2.

SELECT id, title, bm25.score(docs) AS score
FROM docs
WHERE body @@@ bm25.multi_match(
    ARRAY['title^5', 'body^2'],
    query => 'full-text search',
    operator => 'or'
)
  AND category = 'tech'
ORDER BY bm25.score(docs) DESC
LIMIT 20;

BM25 + vector hybrid search (RRF)

Use the following query when you need to combine BM25 keyword matching with vector semantic similarity for hybrid retrieval. The Reciprocal Rank Fusion (RRF) algorithm merges the two ranking results for more comprehensive retrieval.

WITH
bm25_top AS (
    SELECT id, row_number() OVER (ORDER BY bm25.score(docs) DESC) AS bm25_rank
    FROM docs
    WHERE body @@@ bm25.match('database search ranking')
    ORDER BY bm25.score(docs) DESC
    LIMIT 100
),
vec_top AS (
    SELECT id, row_number() OVER (ORDER BY embedding <#> :query_embedding) AS vec_rank
    FROM docs
    ORDER BY embedding <#> :query_embedding
    LIMIT 100
),
rrf AS (
    SELECT COALESCE(b.id, v.id) AS id,
           COALESCE(1.0 / (60 + b.bm25_rank), 0) + COALESCE(1.0 / (60 + v.vec_rank), 0) AS rrf_score
    FROM bm25_top b FULL JOIN vec_top v USING (id)
)
SELECT d.id, d.title, r.rrf_score
FROM rrf r JOIN docs d USING (id)
ORDER BY r.rrf_score DESC
LIMIT 20;

BM25 + JOIN

Use the following query when you need to join BM25 full-text search results with other tables. Full-text search conditions can be freely combined with standard JOIN syntax.

SELECT d.id, d.title, c.label, bm25.score(d) AS score
FROM docs d
JOIN categories c ON d.category = c.category
WHERE d.body @@@ bm25.match('database search')
ORDER BY bm25.score(docs) DESC
LIMIT 20;

BM25 + GROUP BY aggregation

Use the following query when you need to perform grouped aggregation on BM25 search results. Full-text search conditions can be freely combined with GROUP BY and aggregate functions.

SELECT category, COUNT(*) AS cnt, SUM(rating) AS total_rating
FROM docs
WHERE body @@@ bm25.match('database')
GROUP BY category
ORDER BY cnt DESC;

Highlighting and snippets

Use the following query when you need to highlight matched keyword fragments in search results. bm25.snippet returns a single highlighted fragment, and bm25.snippets returns multiple highlighted fragments.

SELECT id, title, bm25.score(docs) AS score,
       bm25.snippet(body, '<em>', '</em>') AS snippet
FROM docs
WHERE body @@@ bm25.match('database search')
ORDER BY bm25.score(docs) DESC
LIMIT 20;

Multiple fragments:

SELECT id, bm25.snippets(body, '<em>', '</em>', 120, 3, 0, 'score') AS snippets
FROM docs
WHERE body @@@ bm25.match('full-text search')
ORDER BY bm25.score(docs) DESC
LIMIT 20;

More Like This (similar content retrieval)

Use the following query when you need to find similar documents based on existing content. The bm25.more_like_this function extracts keywords from the specified sample content and retrieves documents that are similar to it.

SELECT id, title, bm25.score(docs) AS score
FROM docs
WHERE body @@@ bm25.more_like_this(
    document => '{body:"machine learning neural network data"}',
    min_term_frequency => 1,
    min_doc_frequency => 1,
    max_query_terms => 10
)
ORDER BY bm25.score(docs) DESC
LIMIT 20;