Build a dialect-correct full-text relevance-SCORE SQL expression, for use
in SELECT/attributes or ORDER BY/order — NOT a boolean WHERE
predicate (use fullTextMatch() for that).
Returns { $literal: sql }. This shape is safe to use directly in
attributes: [...] and order: [...] today (see module header) —
both channels render raw literal SQL fragments correctly, unlike
the WHERE-clause builder.
mysql/mariadb: MATCH(col1, col2) AGAINST('term') — in MySQL/MariaDB,
using MATCH() AGAINST() outside of a boolean context (e.g. in
SELECT/ORDER BY) naturally evaluates to the relevance score
(a floating point number), so the same construct doubles as both a
predicate (via fullTextMatch) and a score.
oracle: SCORE(1) (Oracle Text). REQUIRES a CONTAINS(col, 'term', 1) > 0
predicate using the same label (1) to appear in the query's WHERE
clause (e.g. from fullTextMatch(dialect, col, term)), per Oracle Text
rules — SCORE(n) reads the score computed by the CONTAINS call
labeled n elsewhere in the same query.
mssql, sqlite, clickhouse, db2, redshift, snowflake: THROWS. None of
these has a genuine, verifiable relevance-scoring mechanism usable as
a plain SQL expression:
mssql: CONTAINS/FREETEXT are boolean predicates only; ranked
results require CONTAINSTABLE/FREETEXTTABLE (table-valued
functions joined in, not an expression usable inline).
sqlite FTS5: ranking requires the bm25() auxiliary function,
which (like MSSQL's *TABLE functions) can only be selected from
the FTS5 virtual table itself, not expressed against an arbitrary
base-table column the way fullTextMatch()'s column argument
implies — so it does not fit this API's per-column shape.
clickhouse: hasToken/multiSearchAny return booleans; there is
no built-in relevance score function.
db2/redshift/snowflake: no native full-text search at all (see
fullTextMatch() docs above), so there is nothing to rank.
Build a dialect-correct full-text relevance-SCORE SQL expression, for use in
SELECT/attributesorORDER BY/order— NOT a boolean WHERE predicate (usefullTextMatch()for that).Returns
{ $literal: sql }. This shape is safe to use directly inattributes: [...]andorder: [...]today (see module header) — both channels render raw literal SQL fragments correctly, unlike the WHERE-clause builder.Support by dialect:
ts_rank(to_tsvector('english', col), plainto_tsquery('english', 'term')).MATCH(col1, col2) AGAINST('term')— in MySQL/MariaDB, usingMATCH() AGAINST()outside of a boolean context (e.g. inSELECT/ORDER BY) naturally evaluates to the relevance score (a floating point number), so the same construct doubles as both a predicate (viafullTextMatch) and a score.SCORE(1)(Oracle Text). REQUIRES aCONTAINS(col, 'term', 1) > 0predicate using the same label (1) to appear in the query's WHERE clause (e.g. fromfullTextMatch(dialect, col, term)), per Oracle Text rules —SCORE(n)reads the score computed by theCONTAINScall labelednelsewhere in the same query.CONTAINS/FREETEXTare boolean predicates only; ranked results requireCONTAINSTABLE/FREETEXTTABLE(table-valued functions joined in, not an expression usable inline).bm25()auxiliary function, which (like MSSQL's *TABLE functions) can only be selected from the FTS5 virtual table itself, not expressed against an arbitrary base-table column the wayfullTextMatch()'s column argument implies — so it does not fit this API's per-column shape.hasToken/multiSearchAnyreturn booleans; there is no built-in relevance score function.fullTextMatch()docs above), so there is nothing to rank.