Skip to main content
Enterprise only
Full-text search is a table function, fts(table, query), rather than a predicate: it produces a relevance score alongside the matching rows, and a WHERE clause has nowhere to put one. The two string-literal arguments are the table name and a JSON-encoded query, built with the same query-builder classes used from the SDK and serialized with .to_json().

Setup

Run these statements in order, sending one statement per SQL request. Semicolons separate the statements for clients that execute a script.
Phrase queries need position information, which costs extra index size. The setup above enables it with WITH (with_position = true). An existing index without positions must be rebuilt before running a phrase query; the server rejects the query and asks you to recreate the index with positions. It does not silently return no matches. See FTS parameters.

Basic search

The query builder produces the JSON string used as the second SQL argument. The SQL examples below spell out the equivalent match object so they can be run directly after the setup above.
By default the top 5 matches come back in arbitrary order. Select _score and order by it for relevance ranking:
_score is a pseudo-column carrying the query’s BM25 relevance for the row; it behaves like any other column, but exists only in a relation that came from fts(). See Data types.

Match any term or every term

The default match operator is OR. "happy puppy" matches either analyzed term. SQL JSON uses "Or" and "And", with this casing; Python uses the FullTextOperator.OR and FullTextOperator.AND enum members:
Both terms must occur, but they need not be adjacent. Do not embed AND or OR in terms to select an operator.

Count matches

Count the fts() relation before applying a top-k limit:
This is an Enterprise SQL workaround when the SDK’s FTS query builder does not expose a match-count operation. table.count_rows() without an FTS relation counts table rows, and taking the length of a limited search counts only that result page. For list-element indexes, multiple matching elements can produce multiple rows; use COUNT(DISTINCT id) when you need unique application records.

Fuzzy

Tolerates typos, up to a character-edit budget:

Phrase

Exact phrase, or with slop to allow words between terms:

Boolean

Combine queries with & (AND) or | (OR):
For SQL, serialize the composed query rather than writing occurrence enums by hand:
Use that string as the second fts() argument. .to_json() supplies the serialized Boolean field names for the SDK version you use.

Boost

Weight a query up or down relative to another:

Multi-match

Search several columns at once, optionally weighting them differently:

Combining with SQL

fts() is a relation, so it composes with the rest of the SQL surface. Filter, join, or aggregate over it same as any table:

What to read next

Full-Text Search

The query-builder equivalent, and every query form in full.

FTS Index

Tokenizers, and the position information phrase queries need.