VIEW (Fulltext index)

To select data from a row-oriented table using a fulltext index, use the VIEW expression:

SELECT ...
FROM TableName VIEW IndexName
WHERE FulltextMatch(TextColumn, "query")
ORDER BY ...

FulltextMatch

FulltextMatch(text, query) filters rows by whether the text matches the fulltext query:

SELECT id, title
FROM articles VIEW ft_idx
WHERE FulltextMatch(body, "machine learning")
LIMIT 20;

Only the first two arguments can be positional. Additional parameters must be passed as named arguments:

• Mode (String): query mode:
  • Keywords (default) — the query is split into individual terms; how they are combined is determined by DefaultOperator
  • Wildcard — wildcard search: % matches any substring, _ matches a single character (similar to LIKE); requires an n-gram index
• DefaultOperator (String): term combination operator in Keywords mode:
  • And (default) — all query terms must be present in the text
  • Or — terms split into required (prefixed with +, must appear in every result) and optional (no prefix, count toward MinimumShouldMatch). Without any + prefix, all terms are optional and at least one must match (see example)
• MinimumShouldMatch (String): minimum number of optional terms that must match when DefaultOperator = "Or" — specified as an absolute number (for example, "3") or a percentage of the optional terms (for example, "50%"). Required (+) terms are not counted

Wildcard mode and % / _ patterns (requires n-grams)

If the index is created with n-gram filtering, you can use % and _ patterns similar to LIKE.
To explicitly enable this behavior, pass "Wildcard" AS Mode as a named argument:

SELECT id, title
FROM articles VIEW ft_idx
WHERE FulltextMatch(body, "mach% learn%", "Wildcard" AS Mode)
LIMIT 20;

LIKE / ILIKE (use the fulltext index)

For fulltext indexes with n-grams, LIKE/ILIKE predicates use the index to find candidates and then check the pattern against the original text. LIKE is case-sensitive and ILIKE is case-insensitive:

SELECT id, title
FROM articles VIEW ft_idx
WHERE body ILIKE "%learn%ing%"
LIMIT 20;

For a filtered fulltext index, WHERE must contain equality predicates for every prefix column.

FulltextScore (BM25 relevance)

FulltextScore(text, query) returns a relevance score and can be used for ranking.
Relevance scoring requires the fulltext_relevance index type.

SELECT id, title, FulltextScore(body, "machine learning") AS relevance
FROM articles VIEW ft_idx
WHERE FulltextScore(body, "machine learning") > 0
ORDER BY relevance DESC
LIMIT 10;

Optional parameters

Additional parameters must be passed as named arguments:

• DefaultOperator (String): term combination operator — And (default, all terms must be present) or Or (terms split into required with + prefix and optional without; at least MinimumShouldMatch optional terms must match, and every required term must be present)
• MinimumShouldMatch (String): when DefaultOperator = "Or", minimum number of optional terms that must match — specified as an absolute number (for example, "2") or a percentage of the optional terms (for example, "50%"). Required (+) terms are not counted
• K1 (Double or Float): term frequency saturation parameter in BM25 — controls how strongly repeated occurrences of a term affect the score; default: 1.2
• B (Double or Float): document length normalization parameter in BM25 — 0.0 disables normalization, 1.0 fully normalizes by document length; default: 0.75

Example:

SELECT id, FulltextScore(body, "machine learning", "Or" AS DefaultOperator, "50%" AS MinimumShouldMatch) AS relevance
FROM articles VIEW ft_idx
WHERE FulltextScore(body, "machine learning", "Or" AS DefaultOperator, "50%" AS MinimumShouldMatch) > 0
ORDER BY relevance DESC;