> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lancedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Full-Text Search

> The fts() table function: match, fuzzy, phrase, boolean, boost and multi-match queries from SQL.

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.

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
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) WITH (with_position = true);
```

> [!WARNING]
>
> 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](https://docs.lancedb.com/enterprise/v1-13-0/indexing/fts-index.md#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.

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
from lancedb.query import MatchQuery

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

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
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:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
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](https://docs.lancedb.com/enterprise/v1-13-0/sql/data-types.md#pseudo-columns).

## 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:

```python theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
from lancedb.query import FullTextOperator, MatchQuery

query = MatchQuery("happy puppy", "text", operator=FullTextOperator.AND)
json_query = query.to_json()
```

```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT id, text, _score
FROM fts('my_docs', '{"match":{"column":"text","terms":"happy puppy","operator":"And"}}')
ORDER BY _score DESC
```

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:

```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT COUNT(*) AS matching_rows
FROM fts('my_docs', '{"match":{"column":"text","terms":"happy puppy","operator":"And"}}')
```

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:

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
MatchQuery("pupy", "text", fuzziness=2)
```

## Phrase

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

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
from lancedb.query import PhraseQuery

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

```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT id, text, _score
FROM fts('my_docs', '{"phrase":{"column":"text","terms":"happy puppy","slop":0}}')
ORDER BY _score DESC
```

## Boolean

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

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
MatchQuery("puppy", "text") & MatchQuery("happy", "text")
MatchQuery("puppy", "text") | MatchQuery("kitten", "text")
```

For SQL, serialize the composed query rather than writing occurrence enums
by hand:

```python theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
query = MatchQuery("puppy", "text") & MatchQuery("happy", "text")
json_query = query.to_json()
```

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:

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
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:

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
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:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT id, text, category
FROM fts(
    'my_docs',
    '{"match":{"column":"text","terms":"puppy"}}'
)
WHERE category = 'animals'
LIMIT 5
```

## What to read next

> [card] [Full-Text Search](https://docs.lancedb.com/enterprise/v1-13-0/search/full-text-search.md)
>
> The query-builder equivalent, and every query form in full.

> [card] [FTS Index](https://docs.lancedb.com/enterprise/v1-13-0/indexing/fts-index.md)
>
> Tokenizers, and the position information phrase queries need.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.