Full-Text Search in ScyllaDB¶

Basic query¶

Filter the rows that contain the search term and rank them by relevance:

SELECT * FROM ks.t
    WHERE BM25(v, 'search term') > 0
    ORDER BY BM25(v, 'search term')
    LIMIT 10;

In the WHERE clause, > is the only supported operator and the right-hand side must be the literal 0. Operators such as >=, =, <, <=, and != are rejected, as is any non-zero threshold.

WHERE BM25(v, 'search term') > 0 compares the score, and is the same as WHERE BM25_SCORE(v, 'search term') > 0. Those are the only two calls the clause accepts.

BM25() may also be selected. It returns a tuple<float, int>: the score the index gave the row, and the row’s rank, its position in the index’s result counted from 1:

SELECT id, BM25(v, 'search term') AS score_and_rank FROM ks.t
    WHERE BM25(v, 'search term') > 0
    ORDER BY BM25(v, 'search term')
    LIMIT 10;

BM25_SCORE() and BM25_RANK() return the two values on their own, which is usually what a query wants:

SELECT id, BM25_SCORE(v, 'search term') AS score, BM25_RANK(v, 'search term') AS rank
    FROM ks.t
    WHERE BM25(v, 'search term') > 0
    ORDER BY BM25(v, 'search term')
    LIMIT 10;

All three describe the search the rows are ranked by, so each needs a BM25 search on the same column in the ORDER BY clause, and has to use the same search term as that search. A query using several of them still makes a single request.

The rank is the position in the index’s result, not in the result set. If the index returns a row that is no longer in the base table, that row’s rank is missing from the result and the ranks after it are not renumbered.

Highlighting¶

BM25_HIGHLIGHT() returns an excerpt of the searched text with the matched terms marked, for showing the reader why a row was returned:

SELECT id, BM25_HIGHLIGHT(v, 'search term') AS excerpt FROM ks.t
    WHERE BM25(v, 'search term') > 0
    ORDER BY BM25(v, 'search term')
    LIMIT 10;

It is accepted only in the SELECT clause, under the same rules as BM25() there: the ORDER BY clause must have a BM25 search on the same column, and the call must use the same search term as that search.

The result is a single fragment of type text, holding HTML: the matched terms are wrapped in <b> and </b>, and the text around them is HTML-escaped, so the fragment can be inserted into an HTML document as returned.

The value is null for a row in which no fragment could be found, as happens when the query consists only of stop words. Such a row is still returned.

A query that asks for an excerpt costs one additional round trip to the full-text index, made after the matching rows have been read, carrying the text of the highlighted column of those rows. In a query that ranks the rows by several searches, only the rows the full-text search returned are sent, and the others get a null excerpt. Queries that do not ask for an excerpt are unaffected.

Filtering support¶

Additional WHERE restrictions (such as partition key equality) are not currently supported alongside BM25() and will be rejected. Support for combined filtering is planned for a future release.

Using bind markers¶

The query term can be supplied with a bind marker in prepared statements:

SELECT * FROM ks.t
    WHERE BM25(v, ?) > 0
    ORDER BY BM25(v, ?)
    LIMIT 10;

All the search-term bind markers are checked at execution time and must be given the same value; binding different values for the WHERE and ORDER BY clauses or for a selected BM25() causes the query to be rejected.

Disambiguating from a user-defined function¶

BM25 is not a reserved word. If a keyspace defines a user-defined function named bm25, an unqualified BM25() call becomes ambiguous; qualify the built-in operator as system.bm25(...) to select it explicitly.