Full-Text Search

Applies to: Embedded ❌, Enterprise ✅ 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.

CREATE TABLE my_docs (id INT, text STRING, category STRING, author STRING);

INSERT INTO my_docs VALUES
    (1, 'The happy puppy runs merrily in the park', 'animals', 'Alice'),
    (2, 'A curious kitten jumps quickly over the fence', 'animals', 'Bob'),
    (3, 'The puppy catches a ball with great enthusiasm', 'sports', 'Alice'),
    (5, 'Puppy training requires patience and dedication', 'training', 'Alice');

CREATE INDEX ON my_docs USING fts (text);

Phrase queries need position information, which costs extra index size. Add WITH (with_position = true) to CREATE INDEX. Without it, phrase queries find nothing. 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.

from lancedb.query import MatchQuery

query = MatchQuery("puppy", "text")
json_query = query.to_json()

SELECT id, text, category
FROM fts(
    'my_docs',
    '{"match":{"column":"text","terms":"puppy"}}'
)
LIMIT 5

By default the top 5 matches come back in arbitrary order. Select _score and order by it for relevance ranking:

SELECT id, text, _score
FROM fts(
    'my_docs',
    '{"match":{"column":"text","terms":"puppy"}}'
)
ORDER BY _score DESC
LIMIT 5

_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.

Fuzzy

Tolerates typos, up to a character-edit budget:

MatchQuery("pupy", "text", fuzziness=2)

Phrase

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

from lancedb.query import PhraseQuery

PhraseQuery("happy puppy", "text")
PhraseQuery("puppy park", "text", slop=2)   # up to 2 words between them

Boolean

Combine queries with & (AND) or | (OR):

MatchQuery("puppy", "text") & MatchQuery("happy", "text")
MatchQuery("puppy", "text") | MatchQuery("kitten", "text")

Boost

Weight a query up or down relative to another:

from lancedb.query import BoostQuery

BoostQuery(
    positive=MatchQuery("puppy", "text"),
    negative=MatchQuery("kitten", "text"),
    negative_boost=0.2,
)

Multi-match

Search several columns at once, optionally weighting them differently:

from lancedb.query import MultiMatchQuery

MultiMatchQuery("puppy", ["text", "category"])
MultiMatchQuery("puppy", ["text", "category"], boosts=[2.0, 1.0])  # text weighted 2x

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:

SELECT id, text, category
FROM fts(
    'my_docs',
    '{"match":{"column":"text","terms":"puppy"}}'
)
WHERE category = 'animals'
LIMIT 5

Full-Text Search

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

FTS Index

Tokenizers, and the position information phrase queries need.

Introduction

Getting Started

Concepts

Data Ingestion

Search

Data Curation

Feature Engineering

Training

Management

Maintenance

Security

Deployment

SQL Reference

Full-Text Search

Setup

Basic search

Fuzzy

Phrase

Boolean

Boost

Multi-match

Combining with SQL

What to read next

Full-Text Search

FTS Index

​Setup

​Basic search

​Fuzzy

​Phrase

​Boolean

​Boost

​Multi-match

​Combining with SQL

​What to read next

Full-Text Search

FTS Index

Setup

Basic search

Fuzzy

Phrase

Boolean

Boost

Multi-match

Combining with SQL

What to read next