本页尚未翻译

本页内容仍为英文原文，源自官方文档 search/full-text-search。中文翻译正在分批进行中。

Full-Text Search (FTS)

Find records by words, phrases or partial matches using a full-text index.

Applies to: Embedded ✅, Enterprise ✅

Full-text search finds documents by matching words and ranks them with BM25. Use it to search for names, product codes, technical terms or phrases in your text columns. Create an FTS index on the column before running a query.

You can also combine keyword matches with vector similarity using hybrid search.

Run a complete full-text search example

Each example creates a temporary local table, runs the query and removes the table when finished. No embedding model is needed for a text query.

• Python: Install with pip install lancedb, save as text_search.py, then run python text_search.py.
• TypeScript: Install with npm install @lancedb/lancedb, save as text_search.mts, then run node text_search.mts with Node.js 22.18 or later.
• Rust: Download and extract the complete Rust examples, then run cargo run --locked --bin text_search from the extracted directory.

table = db.create_table(
    "documents",
    [
        {
            "id": 1,
            "text": "A puppy playing in the park",
            "category": "animals",
        },
        {
            "id": 2,
            "text": "A dog resting at home",
            "category": "animals",
        },
        {
            "id": 3,
            "text": "A guide to growing tomatoes",
            "category": "gardening",
        },
    ],
)
table.create_index("text", config=FTS())
rows = (
    table.search("puppy", query_type="fts", fts_columns="text")
    .select(["id", "text", "_score"])
    .limit(2)
    .to_list()
)
print(rows)

const lancedb = await import("@lancedb/lancedb");
const { mkdtemp, rm } = await import("node:fs/promises");
const { tmpdir } = await import("node:os");
const { join } = await import("node:path");
const uri = await mkdtemp(join(tmpdir(), "lancedb-search-"));
try {
  const db = await lancedb.connect(uri);
  const table = await db.createTable("documents", [
    { id: 1, text: "A puppy playing in the park", category: "animals" },
    { id: 2, text: "A dog resting at home", category: "animals" },
    { id: 3, text: "A guide to growing tomatoes", category: "gardening" },
  ]);
  await table.createIndex("text", { config: lancedb.Index.fts() });
  const rows = await table
    .query()
    .fullTextSearch("puppy", { columns: ["text"] })
    .select(["id", "text", "_score"])
    .limit(2)
    .toArray();
  console.log(rows);

} finally {
  await rm(uri, { recursive: true, force: true });
}

use arrow_array::{Int64Array, RecordBatch, RecordBatchIterator, StringArray};
use arrow_schema::{DataType, Field, Schema};
use futures::TryStreamExt;
use lance_index::scalar::FullTextSearchQuery;
use lancedb::index::{Index, scalar::FtsIndexBuilder};
use lancedb::query::{ExecutableQuery, QueryBase, Select};
use std::sync::Arc;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let directory = tempfile::tempdir()?;
    let db = lancedb::connect(directory.path().to_str().unwrap())
        .execute()
        .await?;
    let schema = Arc::new(Schema::new(vec![
        Field::new("id", DataType::Int64, false),
        Field::new("text", DataType::Utf8, false),
        Field::new("category", DataType::Utf8, false),
    ]));
    let batch = RecordBatch::try_new(
        schema.clone(),
        vec![
            Arc::new(Int64Array::from(vec![1, 2, 3])),
            Arc::new(StringArray::from(vec![
                "A puppy playing in the park",
                "A dog resting at home",
                "A guide to growing tomatoes",
            ])),
            Arc::new(StringArray::from(vec!["animals", "animals", "gardening"])),
        ],
    )?;
    let reader = RecordBatchIterator::new(vec![Ok(batch)].into_iter(), schema);
    let table = db
        .create_table(
            "documents",
            Box::new(reader) as Box<dyn arrow_array::RecordBatchReader + Send>,
        )
        .execute()
        .await?;
    table
        .create_index(&["text"], Index::FTS(FtsIndexBuilder::default()))
        .execute()
        .await?;
    let query = table
        .query()
        .full_text_search(FullTextSearchQuery::new("puppy".into()).with_column("text".into())?)
        .select(Select::columns(&["id", "text", "_score"]))
        .limit(2);
    let rows: Vec<RecordBatch> = query.execute().await?.try_collect().await?;
    println!("{rows:?}");

    Ok(())
}

Each example searches the indexed text column and returns the puppy record. Higher _score values rank first; they are relevance scores, not probabilities.

Basic usage

The steps below create a table, index its text column and search for a word. For a standalone program with imports and sample data, expand the complete example above.

Create the sample table

First, open or create the table you want to search:

from lancedb.index import FTS

uri = "data/sample-lancedb"
db = lancedb.connect(uri)

table = db.create_table(
    "my_table_fts",
    data=[
        {"vector": [3.1, 4.1], "text": "Frodo was a happy puppy"},
        {"vector": [5.9, 26.5], "text": "There are several kittens playing"},
    ],
)

import * as lancedb from "@lancedb/lancedb";
const uri = "data/sample-lancedb"
const db = await lancedb.connect(uri);

const data = [
    { vector: [3.1, 4.1], text: "Frodo was a happy puppy" },
    { vector: [5.9, 26.5], text: "There are several kittens playing" },
];
const tbl = await db.createTable("my_table", data, { mode: "overwrite" });

let uri = "data/sample-lancedb";
let db = connect(uri).execute().await?;
let initial_data: Box<dyn RecordBatchReader + Send> = create_some_records()?;
let tbl = db
    .create_table("my_table", initial_data)
    .execute()
    .await?;

Build the text index

Create a full-text search index on your text column:

For asynchronous Python index creation, use await table.create_index("text", config=FTS()). See FTS index.

table.create_index("text", config=FTS())

await tbl.createIndex("text", {
    config: lancedb.Index.fts(),
});

tbl
    .create_index(&["text"], Index::FTS(FtsIndexBuilder::default()))
    .execute()
    .await?;

LanceDB Embedded builds the index before the call returns. Enterprise builds it in the background. Wait for completion with Python’s table.wait_for_index(["text_idx"]) or TypeScript’s await tbl.waitForIndex(["text_idx"], 300) before querying it. Use your actual index name if you chose a different one.

Run the query

Perform full-text search and retrieve results:

results = (
    table.search("puppy", query_type="fts", fts_columns="text")
    .limit(10)
    .select(["text", "_score"])
    .to_list()
)
# Returns the puppy record with its BM25 score.

const results = await tbl
    .search("puppy", "fts")
    .select(["text"])
    .limit(10)
    .toArray();

let results = tbl
    .query()
    .full_text_search(FullTextSearchQuery::new("puppy".to_owned()))
    .select(lancedb::query::Select::Columns(vec!["text".to_owned()]))
    .limit(10)
    .execute()
    .await?;

Specify the text column with fts_columns="text" in synchronous Python, especially when the table contains multiple text indexes.

说明

A string query can also invoke an embedding function when the table has embedding metadata. Use query_type="fts" in Python when you specifically want full-text search.

If a table has more than one FTS index, specify the indexed text column in the query. In Python you can use fts_columns or the query builder's nearest_to_text(..., columns=...); in TypeScript, use query().nearestToText(..., columns). The newer Lance-native FTS does not accept legacy Tantivy-only index parameters.

BM25 scores depend on the indexed corpus and its term statistics. Compare rankings on the dataset you will search; treat scores as relative relevance values rather than probabilities.

Keeping the index up to date

In LanceDB Embedded, new rows can remain outside the FTS index until you optimize the table. Search includes those rows by scanning them, but that work can increase query latency. Call optimize() to update the index:

table.add([{"vector": [3.1, 4.1], "text": "Frodo was a happy puppy"}])
table.optimize()

await tbl.add([{ vector: [3.1, 4.1], text: "Frodo was a happy puppy" }]);
await tbl.optimize();

tbl.add(new_data).execute().await?;
tbl.optimize(OptimizeAction::All).await?;

Choose a LanceDB Embedded maintenance schedule based on write volume and query latency. Inspect index coverage with table.index_stats(...) and optimize when the unindexed data adds too much work. .fast_search() skips unindexed rows, so use it only when omitting recent records is acceptable.

Enterprise manages index updates automatically. You do not call optimize() on an Enterprise table.

Choose the matching behavior

You need	Use
Words in any order	A terms query
Words in a particular sequence	A phrase query and an index with token positions
Tolerance for typing errors	Fuzzy search
Parts of words	An n-gram index
Required, optional or excluded terms	Boolean queries

Use a metadata predicate to restrict qualifying records. See filtering options for prefiltering and postfiltering. Enterprise SQL provides fts() for scored text retrieval.

Configure tokenization and matching

Tokenize Table Data

The default tokenizer splits text at whitespace and punctuation, lowercases tokens and removes tokens longer than 40 characters. Stemming, stop-word removal and accent folding are also enabled by default.

Stemming is useful for improving search results by reducing words to their root form, e.g. "running" to "run". LanceDB supports stemming for Arabic, Danish, Dutch, English, Finnish, French, German, Greek, Hungarian, Italian, Norwegian, Portuguese, Romanian, Russian, Spanish, Swedish, Tamil, and Turkish. You should set the base_tokenizer parameter rather than tokenizer_name because you cannot customize the tokenizer if tokenizer_name is specified.

Tokenization and language filters are separate settings. base_tokenizer controls how text is split into searchable tokens. language controls stemming and stop-word removal when stem=True or remove_stop_words=True; choose the tokenizer for CJK or mixed-language segmentation.

For example, to enable stemming for English:

table.create_fts_index("text", language="English", replace=True)

Configure tokenization when you create the index. Rebuild the index to change how existing text is tokenized.

Default index parameters:

• base_tokenizer: "simple"
• language: English
• with_position: false
• max_token_length: 40
• lower_case: true
• stem: true
• remove_stop_words: true
• ascii_folding: true
• custom_stop_words: None; pass a list[str] to drop additional words beyond the language defaults. Requires remove_stop_words=True.

For multilingual use cases, use base_tokenizer="icu" for unicode-aware word segmentation on mixed-language text. ICU stands for International Components for Unicode. The ICU tokenizer uses bundled ICU4X segmenter data, so it does not require external tokenizer model files. It is a good default when documents mix languages or include scripts where the simple tokenizer would keep an unspaced span as one large token.

The Python API also supports tokenizer implementations that load language model files. Use base_tokenizer="jieba/default" for Jieba tokenization, which segments Chinese text into searchable word tokens when the text is written without spaces between words. Use Lindera-backed tokenizers for dictionary-based East Asian morphological segmentation, such as base_tokenizer="lindera/ipadic" for Japanese or base_tokenizer="lindera/ko-dic" for Korean when you have installed and compiled that Lindera model. These are language-specific tokenizers; ICU is the broader mixed-language option.

table.create_fts_index(
    "text",
    base_tokenizer="jieba/default",
    stem=False,
    remove_stop_words=False,
    ascii_folding=False,
    replace=True,
)

Model-backed tokenizers require tokenizer model files in Lance's language model home. Lance looks under the default platform data directory for lance/language_models, or you can set LANCE_LANGUAGE_MODEL_HOME to point to a different model root:

export LANCE_LANGUAGE_MODEL_HOME=/path/to/lance/language_models

For example, jieba/default is resolved under <model-home>/jieba/default/..., lindera/ipadic under <model-home>/lindera/ipadic/..., and lindera/ko-dic under <model-home>/lindera/ko-dic/....

Model files must be installed where indexing and queries run: on your machine for LanceDB Embedded, or on the Enterprise cluster for an Enterprise table.

说明

Built-in stop-word removal supports Danish, Dutch, English, Finnish, French, German, Hungarian, Italian, Norwegian, Portuguese, Russian, Spanish, and Swedish. If you use another stemming language, such as Arabic, Greek, Romanian, Tamil, or Turkish, set remove_stop_words=False or pass custom_stop_words.

Use ascii_folding=True to match accented and unaccented forms, such as “é” and “e”:

table.create_fts_index(
        "text",
        language="French",
        stem=True,
        ascii_folding=True,
        replace=True,
    )

Filtering Options

Use where(...) to restrict text results to rows that match a predicate. Prefiltering applies the restriction before retrieval; postfiltering removes nonmatching candidates afterward and can leave fewer results.

With pre-filtering:

table.search("puppy").limit(10).where("text='foo'", prefilter=True).to_list()

await tbl
.search("puppy")
.select(["id", "doc"])
.limit(10)
.where("meta='foo'")
.prefilter(true)
.toArray();

table
    .query()
    .full_text_search(FullTextSearchQuery::new("puppy".to_owned()))
    .select(lancedb::query::Select::Columns(vec!["doc".to_owned()]))
    .limit(10)
    .only_if("meta='foo'")
    .execute()
    .await?;

With post-filtering:

table.search("puppy").limit(10).where("text='foo'", prefilter=False).to_list()

await tbl
.search("apple")
.select(["id", "doc"])
.limit(10)
.where("meta='foo'")
.prefilter(false)
.toArray();

table
    .query()
    .full_text_search(FullTextSearchQuery::new(words[0].to_owned()))
    .select(lancedb::query::Select::Columns(vec!["doc".to_owned()]))
    .postfilter()
    .limit(10)
    .only_if("meta='foo'")
    .execute()
    .await?;

Phrase vs. Terms Queries

注意

Lance-based FTS doesn't support queries using boolean operators OR, AND in the search string.

For full-text search you can specify either a phrase query like "the old man and the sea", or a terms search query like old man sea.

Enable token positions to search for phrases. Set remove_stop_words=False if you also need to match common words such as “the” and “and”:

from lancedb.index import FTS

table.create_index("text", config=FTS(with_position=True, remove_stop_words=False), replace=True)

Storing token positions increases the index size and adds indexing work.

Fuzzy Search

Fuzzy search allows you to find matches even when the search terms contain typos or slight variations. LanceDB uses the classic Levenshtein distance to find similar terms within a specified edit distance.

Parameter	Type	Default	Description
fuzziness	int	0	Maximum edit distance allowed for each term. The default, 0, requires an exact match; set 1 or 2 to allow typos.
max_expansions	int	50	Maximum number of terms to consider for fuzzy matching. Higher values may improve recall but increase search time

For a complete walkthrough that creates a sample table and demonstrates fuzzy search and relevance boosting, see the worked example below.

Search for Substring

LanceDB supports searching for substrings in the text column, you can set the base_tokenizer parameter to "ngram" to enable this feature, and use the parameters ngram_min_length and ngram_max_length to control the length of the substrings:

Parameter	Type	Default	Description
ngram_min_length	int	3	Minimum length of the n-grams to search for
ngram_max_length	int	3	Maximum length of the n-grams to search for
prefix_only	bool	false	Whether to only search for prefixes of the n-grams

Expected performance at scale

Enterprise

At 1 billion rows, LanceDB Enterprise reaches p50 62 ms and p99 274 ms on fully cached data with no custom tuning -- a representative floor to plan around, not a ceiling. Measured under a mixed workload of phrase, match-any and match-all queries, with an ICU-tokenized FTS v3 index (positions and lowercasing enabled, stemming disabled). The exact numbers depend on your deployment's shape (cluster size, storage class, dataset); for higher performance, talk to the sales team.

Fuzzy Search and Boosting Example

Worked examples: fuzzy, phrase, boosted and Boolean queries

Generate Data

Run the following steps in order. They use a temporary local database with four records, so the same data is available for each query.

from tempfile import TemporaryDirectory

import pandas as pd
from lancedb.index import FTS
from lancedb.query import MatchQuery, PhraseQuery, BoostQuery, MultiMatchQuery

directory = TemporaryDirectory()
db = lancedb.connect(directory.name)
table_name = "fts_examples"
text = ["puppy runs merrily", "car drives crazily", "puppy jumps crazily", "car runs slowly"]
text2 = ["rabbit jumps crazily", "girl runs merrily", "monkey jumps slowly", "rabbit runs merrily"]

import * as lancedb from "@lancedb/lancedb";
import { Index, MatchQuery, PhraseQuery, BoostQuery, MultiMatchQuery, BooleanQuery, Occur } from "@lancedb/lancedb";
import { mkdtemp, rm } from "node:fs/promises";
import { tmpdir } from "node:os";
import { join } from "node:path";

const directory = await mkdtemp(join(tmpdir(), "lancedb-fts-"));
const db = await lancedb.connect(directory);
const tableName = "fts_examples";
const text = ["puppy runs merrily", "car drives crazily", "puppy jumps crazily", "car runs slowly"];
const text2 = ["rabbit jumps crazily", "girl runs merrily", "monkey jumps slowly", "rabbit runs merrily"];

Create Table

# Create table with sample data
table = db.create_table(
    table_name,
    data=pd.DataFrame({
        "id": range(len(text)),
        "text": text,
        "text2": text2,
    }),
    mode="overwrite"
)

// Create table with sample data
const data = text.map((value, i) => ({ id: i, text: value, text2: text2[i] }));

const table = await db.createTable(tableName, data, { mode: "overwrite" });

Construct the index for these examples

Create a full-text search index on the first text column:

# Create FTS index on first text column
table.create_index("text", config=FTS(with_position=True, remove_stop_words=False))
table.wait_for_index(["text_idx"])

// Create FTS index on first text column
await table.createIndex("text", { config: Index.fts({ withPosition: true, removeStopWords: false }) });
await table.waitForIndex(["text_idx"], 300);

Then, create an index on the second text column:

# Create FTS index on second text column
table.create_index("text2", config=FTS(with_position=True, remove_stop_words=False))
table.wait_for_index(["text2_idx"])

// Create FTS index on second text column
await table.createIndex("text2", { config: Index.fts({ withPosition: true, removeStopWords: false }) });
await table.waitForIndex(["text2_idx"], 300);

Basic and Fuzzy Search

Now we can perform basic, fuzzy, and prefix match searches:

Basic Exact Search

from lancedb.query import MatchQuery

# Basic match (exact search)
basic_match_results = (
    table.search(MatchQuery("crazily", "text"))
    .select(["id", "text", "_score"])
    .limit(100)
    .to_arrow().to_pandas()
)

// Basic match (exact search)
const basicMatchResults = await table.query()
    .fullTextSearch(new MatchQuery("crazily", "text"))
    .select(["id", "text", "_score"])
    .limit(100)
    .toArray();

Fuzzy Search with Typos

# Fuzzy match (allows typos)
fuzzy_results = (
    table.search(MatchQuery("craziou", "text", fuzziness=2))
    .select(["id", "text", "_score"])
    .limit(100)
    .to_arrow().to_pandas()
)

// Fuzzy match (allows typos)
const fuzzyResults = await table.query()
    .fullTextSearch(new MatchQuery("craziou", "text", {
        fuzziness: 2,
    }))
    .select(["id", "text", "_score"])
    .limit(100)
    .toArray();

Require an exact prefix in fuzzy matching

prefix_length keeps the first characters unchanged when matching a misspelled term. It does not turn a short query into a prefix-completion search.

# Fuzzy match (allows typos)
prefix_results = (
    table.search(MatchQuery("craziou", "text", fuzziness=2, prefix_length=3))
    .select(["id", "text", "_score"])
    .limit(100)
    .to_arrow().to_pandas()
)

// Fuzzy match (allows typos)
const prefixResults = await table.query()
    .fullTextSearch(new MatchQuery("craziou", "text", {
        fuzziness: 2,
        prefixLength: 3,
    }))
    .select(["id", "text", "_score"])
    .limit(100)
    .toArray();

Phrase Match

Phrase matching enables you to search for exact sequences of words. Unlike regular text search which matches individual terms independently, phrase matching requires words to appear in the specified order with no intervening terms.

NOTE

Phrase queries are supported but only for a single column; providing multiple columns with a quoted phrase raises an error.

Phrase matching is particularly useful for:

• Searching for specific multi-word expressions
• Matching exact titles or quotes
• Finding precise word combinations in a specific order

# Exact phrase match
from lancedb.query import PhraseQuery

phrase_results = (
    table.search(PhraseQuery("puppy runs", "text"))
    .select(["id", "text", "_score"])
    .limit(100)
    .to_arrow().to_pandas()
)

// Exact phrase match
const phraseResults = await table.query()
  .fullTextSearch(new PhraseQuery("puppy runs", "text"))
  .select(["id", "text", "_score"])
  .limit(100)
  .toArray();

Flexible Phrase Match

To provide more flexible phrase matching, LanceDB supports the slop parameter. This allows you to match phrases where the terms appear close to each other, with gaps between them. The slop value limits the extra positions allowed in the phrase.

For example, the phrase query "puppy merrily" would not return any results by default. However, if you set slop=1, it will match phrases like "puppy jumps merrily", "puppy runs merrily", and similar variations where one word appears between "puppy" and "merrily".

# Flexible phrase match with slop=1 for 'puppy merrily'
from lancedb.query import PhraseQuery

slop_results = (
    table.search(PhraseQuery("puppy merrily", "text", slop=1))
    .select(["id", "text", "_score"])
    .limit(100)
    .to_arrow().to_pandas()
)

// Flexible phrase match with slop=1 for 'puppy merrily'
const slopResults = await table.query()
  .fullTextSearch(new PhraseQuery("puppy merrily", "text", { slop: 1 }))
  .select(["id", "text", "_score"])
  .limit(100)
  .toArray();

Search with Boosting

Boosting allows you to control the relative importance of different search terms or fields in your queries. This feature is particularly useful when you need to:

• Prioritize matches in certain columns
• Promote specific terms while demoting others
• Fine-tune relevance scoring for better search results

Parameter	Type	Default	Description
positive	Query	required	The primary query terms to match and promote in results
negative	Query	required	Terms to demote in the search results
negative_boost	float	0.5	Multiplier for negative matches (lower values = stronger demotion)

from lancedb.query import MatchQuery, BoostQuery, MultiMatchQuery

# Boost data with 'runs' in text more than 'puppy' in text
boosting_results = (
  table.search(
      BoostQuery(
          MatchQuery("runs", "text"),
          MatchQuery("puppy", "text"),
          negative_boost=0.2,
      ),
  )
  .select(["id", "text", "_score"])
  .limit(100)
  .to_arrow().to_pandas()
)

# Search across both text and text2
multi_match_results = (
    table.search(MultiMatchQuery("crazily", ["text", "text2"]))
    .select(["id", "text", "text2", "_score"])
    .limit(100)
    .to_arrow().to_pandas()
)

# Search with field boosting
multi_match_boosting_results = (
    table.search(
        MultiMatchQuery("crazily", ["text", "text2"], boosts=[1.0, 2.0]),
    )
    .select(["id", "text", "text2", "_score"])
    .limit(100)
    .to_arrow().to_pandas()
)

// Boosting Example
const boostingResults = await table.query()
  .fullTextSearch(new BoostQuery(new MatchQuery("runs", "text"), new MatchQuery("puppy", "text"), {
    negativeBoost: 0.2,
  }))
  .select(["id", "text", "_score"])
  .limit(100)
  .toArray();

// Search across both text fields
const multiMatchResults = await table.query()
  .fullTextSearch(new MultiMatchQuery("crazily", ["text", "text2"]))
  .select(["id", "text", "text2", "_score"])
  .limit(100)
  .toArray();

// Search with field boosting
const multiMatchBoostingResults = await table.query()
  .fullTextSearch(new MultiMatchQuery("crazily", ["text", "text2"], {
    boosts: [1.0, 2.0],
  }))
  .select(["id", "text", "text2", "_score"])
  .limit(100)
  .toArray();

Search Columns as One Field

MultiMatchQuery scores each column on its own and keeps the best match. CombinedFieldsQuery treats the columns as one field (BM25F), so with the AND operator a row matches when every term appears in at least one of the columns. The query below finds car in text and merrily in text2, returning rows 1 and 3. The same query with MultiMatchQuery returns no rows, because neither column contains both words on its own.

Every column must use the same tokenizer configuration. The two indexes in this example are built with the same FTS settings. Boosts work as they do for MultiMatchQuery: one weight per column, each at least 1.

from lancedb.query import CombinedFieldsQuery, FullTextOperator

# Every term must appear in at least one of the two columns
combined_results = (
    table.search(
        CombinedFieldsQuery("car merrily", ["text", "text2"], operator=FullTextOperator.AND),
    )
    .select(["id", "text", "text2", "_score"])
    .limit(100)
    .to_arrow().to_pandas()
)

# Weight matches in text2 twice as much as matches in text
combined_boosting_results = (
    table.search(
        CombinedFieldsQuery("car merrily", ["text", "text2"], boosts=[1.0, 2.0]),
    )
    .select(["id", "text", "text2", "_score"])
    .limit(100)
    .to_arrow().to_pandas()
)

The typed CombinedFieldsQuery convenience class and this example are specific to the Python SDK. Rust applications can construct the core query type exposed by lancedb::index::scalar. TypeScript and Java do not yet provide typed convenience entry points for this query.

Boolean Queries

LanceDB supports boolean logic in full-text search, allowing you to combine multiple queries using and and or operators. This is useful when you want to match documents that satisfy multiple conditions (intersection) or at least one of several conditions (union).

Combining Two Match Queries

In Python, combine two MatchQuery objects with & for intersection or | for union. Python's and and or operators return one operand and do not construct a boolean query.

from lancedb.query import MatchQuery

intersection = MatchQuery("puppy", "text") & MatchQuery("merrily", "text")
union = MatchQuery("puppy", "text") | MatchQuery("merrily", "text")

In TypeScript, pass [Occur, subquery] pairs to BooleanQuery. Use Must for required matches, Should for alternatives, and MustNot to exclude matches.

NOTE

Which queries are allowed?

A boolean query must include at least one SHOULD or MUST clause. Queries that contain only a MUST_NOT clause are not allowed.

from lancedb.query import MatchQuery

# Example: Find documents containing both "puppy" and "merrily"
and_query = MatchQuery("puppy", "text") & MatchQuery("merrily", "text")
and_results = (
    table.search(and_query)
    .select(["id", "text", "_score"])
    .limit(100)
    .to_arrow().to_pandas()
)

# Example: Find documents containing either "puppy" or "merrily"
or_query = MatchQuery("puppy", "text") | MatchQuery("merrily", "text")
or_results = (
    table.search(or_query)
    .select(["id", "text", "_score"])
    .limit(100)
    .to_arrow().to_pandas()
)

// Flexible boolean queries with MatchQuery

// Find documents containing both "puppy" and "merrily"
const mustResults = await table
    .search(
      new BooleanQuery([
        [Occur.Must, new MatchQuery("puppy", "text")],
        [Occur.Must, new MatchQuery("merrily", "text")],
      ]),
    )
    .select(["id", "text", "_score"])
    .limit(100)
    .toArray();

// Find documents containing either "puppy" or "merrily"
const shouldResults = await table
    .search(
      new BooleanQuery([
        [Occur.Should, new MatchQuery("puppy", "text")],
        [Occur.Should, new MatchQuery("merrily", "text")],
      ]),
    )
    .select(["id", "text", "_score"])
    .limit(100)
    .toArray();

NOTE

How to use booleans?

• Use & (Python), Occur.Must (TypeScript) for intersection (documents must match all queries).
• Use | (Python), Occur.Should (TypeScript) for union (documents must match at least one query).

Clean up the example

Remove the temporary database when you have finished running the queries.

directory.cleanup()

table.close();
db.close();
await rm(directory, { recursive: true, force: true });

Substring Search Example

Worked examples: substring search

LanceDB supports searching for substrings in text columns using n-gram tokenization. This is useful for finding partial matches within text content.

Setting Up the Table

First, create a table with sample text data and configure n-gram tokenization:

import pyarrow as pa
from tempfile import TemporaryDirectory
from lancedb.index import FTS

directory = TemporaryDirectory()
db = lancedb.connect(directory.name)

data = pa.table({"text": ["hello world", "lance database", "lance is cool"]})
table = db.create_table("test", data=data)
table.create_index("text", config=FTS(base_tokenizer="ngram"))

Basic Substring Search

With the default n-gram settings (minimum length of 3), you can search for substrings of length 3 or more:

results = table.search("lan", query_type="fts").limit(10).to_list()
assert len(results) == 2
assert set(r["text"] for r in results) == {"lance database", "lance is cool"}

results = (
    table.search("nce", query_type="fts").limit(10).to_list()
)  # spellchecker:disable-line
assert len(results) == 2
assert set(r["text"] for r in results) == {"lance database", "lance is cool"}

Handling Short Substrings

By default, the minimum n-gram length is 3, so shorter substrings like "la" won't match:

results = table.search("la", query_type="fts").limit(10).to_list()
assert len(results) == 0

Customizing N-gram Parameters

You can customize the n-gram behavior by adjusting the minimum length and using prefix-only matching:

table.create_index(
    "text",
    config=FTS(base_tokenizer="ngram", ngram_min_length=2, prefix_only=True),
    replace=True,
)

Testing Custom N-gram Settings

With the new settings, you can now search for shorter substrings and use prefix-only matching:

results = table.search("lan", query_type="fts").limit(10).to_list()
assert len(results) == 2
assert set(r["text"] for r in results) == {"lance database", "lance is cool"}

results = (
    table.search("nce", query_type="fts").limit(10).to_list()
)  # spellchecker:disable-line
assert len(results) == 0

results = table.search("la", query_type="fts").limit(10).to_list()
assert len(results) == 2
assert set(r["text"] for r in results) == {"lance database", "lance is cool"}

directory.cleanup()

Full-Text Search on Array Fields

Worked examples: searching string arrays

LanceDB supports full-text search on string array columns, enabling efficient keyword-based search across multiple values within a single field (e.g., tags, keywords).

Setting Up the Connection

This example uses a temporary local database. Run the steps in order.

from tempfile import TemporaryDirectory

import pyarrow as pa
from lancedb.index import FTS
from lancedb.query import MatchQuery, PhraseQuery

directory = TemporaryDirectory()
db = lancedb.connect(directory.name)

import * as lancedb from "@lancedb/lancedb";
import { Index, MatchQuery, PhraseQuery, makeArrowTable } from "@lancedb/lancedb";
import { Schema, Field, List, Utf8 } from "apache-arrow";
import { mkdtemp, rm } from "node:fs/promises";
import { tmpdir } from "node:os";
import { join } from "node:path";

const directory = await mkdtemp(join(tmpdir(), "lancedb-array-fts-"));
const db = await lancedb.connect(directory);

Defining the Schema

Create a schema that includes an array field for tags:

table_name = "fts-array-field-test"
schema = pa.schema([
    pa.field("id", pa.string()),
    pa.field("tags", pa.list_(pa.string())),
    pa.field("description", pa.string())
])

const tableName = "fts-array-field-test-ts";

// Create schema
const schema = new Schema([
  new Field("id", new Utf8(), false),
  new Field("tags", new List(new Field("item", new Utf8()))),
  new Field("description", new Utf8(), false)
]);

Creating Sample Data

Generate sample data with array fields containing tags:

# Generate sample data
data = {
    "id": [f"doc_{i}" for i in range(10)],
    "tags": [
        ["python", "machine learning", "data science"],
        ["deep learning", "neural networks", "AI"],
        ["database", "indexing", "search"],
        ["vector search", "embeddings", "AI"],
        ["full text search", "indexing", "database"],
        ["python", "web development", "flask"],
        ["machine learning", "deep learning", "pytorch"],
        ["database", "SQL", "postgresql"],
        ["search engine", "elasticsearch", "indexing"],
        ["AI", "transformers", "NLP"]
    ],
    "description": [
        "Python for data science projects",
        "Deep learning fundamentals",
        "Database indexing techniques",
        "Vector search implementations",
        "Full-text search guide",
        "Web development with Python",
        "Machine learning with PyTorch",
        "Database management systems",
        "Search engine optimization",
        "AI and NLP applications"
    ]
}

// Generate sample data
const data = makeArrowTable(
  Array(10).fill(0).map((_, i) => ({
    id: `doc_${i}`,
    tags: [
      ["python", "machine learning", "data science"],
      ["deep learning", "neural networks", "AI"],
      ["database", "indexing", "search"],
      ["vector search", "embeddings", "AI"],
      ["full text search", "indexing", "database"],
      ["python", "web development", "flask"],
      ["machine learning", "deep learning", "pytorch"],
      ["database", "SQL", "postgresql"],
      ["search engine", "elasticsearch", "indexing"],
      ["AI", "transformers", "NLP"]
    ][i],
    description: [
      "Python for data science projects",
      "Deep learning fundamentals",
      "Database indexing techniques",
      "Vector search implementations",
      "Full-text search guide",
      "Web development with Python",
      "Machine learning with PyTorch",
      "Database management systems",
      "Search engine optimization",
      "AI and NLP applications"
    ][i]
  })),
  { schema }
);

Creating the Table and Adding Data

Create the table and populate it with the sample data:

# Create table and add data
table = db.create_table(table_name, schema=schema, mode="overwrite")
table_data = pa.Table.from_pydict(data, schema=schema)
table.add(table_data)

// Create table
const table = await db.createTable(tableName, data, { mode: "overwrite" });
console.log(`Created table: ${tableName}`);

Building the Full-Text Search Index

Store token positions for phrase queries. This example disables stemming so fuzzy matching compares the original word forms:

# Create FTS index
table.create_index("tags", config=FTS(with_position=True, stem=False))
table.wait_for_index(["tags_idx"])

// Create FTS index
console.log("Creating FTS index on 'tags' column...");
await table.createIndex("tags", {
  config: Index.fts({ withPosition: true, stem: false })
});

// Wait for index
const ftsIndexName = "tags_idx";
await table.waitForIndex([ftsIndexName], 300);

Performing Fuzzy Search

Search for terms with typos using fuzzy matching:

# Search examples
print("\nSearching for 'learning' in tags with a typo:")
result = (
    table.search(MatchQuery("learnin", column="tags", fuzziness=1))
    .select(["id", "tags", "description", "_score"])
    .to_arrow()
)

// Search examples
console.log("\nSearching for 'learning' in tags with a typo:");
const fuzzyResults = await table.query()
  .fullTextSearch(new MatchQuery("learnin", "tags", {
    fuzziness: 1,
  }))
  .select(["id", "tags", "description", "_score"])
  .toArray();
console.log(fuzzyResults);

A string-array query can return a row more than once when several elements match. Deduplicate by your record ID if your application needs one result per row.

Performing Phrase Search

Search for exact phrases within the array fields:

print("\nSearching for 'machine learning' in tags:")
result = (
    table.search(PhraseQuery("machine learning", column="tags"))
    .select(["id", "tags", "description", "_score"])
    .to_arrow()
)

console.log("\nSearching for 'machine learning' in tags:");
const phraseResults = await table.query()
  .fullTextSearch(new PhraseQuery("machine learning", "tags"))
  .select(["id", "tags", "description", "_score"])
  .toArray();
console.log(phraseResults);

Clean up the array example

Remove the temporary database when you have finished running the queries.

directory.cleanup()

table.close();
db.close();
await rm(directory, { recursive: true, force: true });