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
  • Query — extended syntax with logical operators: required terms via +, excluded terms via -, exact phrases in double quotes
  • 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 — matching at least one term is sufficient; use MinimumShouldMatch to set a minimum threshold
• MinimumShouldMatch (String): minimum number of matched terms when DefaultOperator = "Or" — specified as an absolute number (for example, "3") or a percentage of query terms (for example, "50%")

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 over the indexed text column use the same logic as FulltextMatch(..., "Wildcard" AS Mode):

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

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 (at least one term must match)
• MinimumShouldMatch (String): when DefaultOperator = "Or", minimum number of matched terms — specified as an absolute number (for example, "2") or a percentage (for example, "50%")
• K1 (Double): term frequency saturation parameter in BM25 — controls how strongly repeated occurrences of a term affect the score; typical range: 1.2–2.0
• B (Double): document length normalization parameter in BM25 — 0.0 disables normalization, 1.0 fully normalizes by document length; typical value: 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;