本页尚未翻译

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

Metadata Filtering in LanceDB

Use predicates to select rows and control when filters apply to search.

Applies to: Embedded ✅, Enterprise ✅

A filter restricts results to rows that satisfy a condition. Use it on its own to select records, or combine it with vector, full-text or hybrid search. For example, search for similar documents only within a particular category.

Run a complete filtering example

These examples use three sample records and hand-written vectors, so no embedding service is needed. Each program creates a temporary local table and removes it when finished.

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

table = db.create_table(
    "documents",
    [
        {
            "id": 1,
            "text": "A puppy playing in the park",
            "category": "animals",
            "vector": [1.0, 0.0],
        },
        {
            "id": 2,
            "text": "A dog resting at home",
            "category": "animals",
            "vector": [0.9, 0.1],
        },
        {
            "id": 3,
            "text": "A guide to growing tomatoes",
            "category": "gardening",
            "vector": [0.0, 1.0],
        },
    ],
)
rows = (
    table.search([0.0, 1.0])
    .where("category = 'animals'", prefilter=True)
    .select(["id", "text", "category", "_distance"])
    .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",
      vector: [1.0, 0.0],
    },
    {
      id: 2,
      text: "A dog resting at home",
      category: "animals",
      vector: [0.9, 0.1],
    },
    {
      id: 3,
      text: "A guide to growing tomatoes",
      category: "gardening",
      vector: [0.0, 1.0],
    },
  ]);
  const rows = await table
    .vectorSearch([0.0, 1.0])
    .where("category = 'animals'")
    .select(["id", "text", "category", "_distance"])
    .limit(2)
    .toArray();
  console.log(rows);

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

use arrow_array::types::Float32Type;
use arrow_array::{FixedSizeListArray, Int64Array, RecordBatch, RecordBatchIterator, StringArray};
use arrow_schema::{DataType, Field, Schema};
use futures::TryStreamExt;
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),
        Field::new(
            "vector",
            DataType::FixedSizeList(Arc::new(Field::new("item", DataType::Float32, true)), 2),
            true,
        ),
    ]));
    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"])),
            Arc::new(
                FixedSizeListArray::from_iter_primitive::<Float32Type, _, _>(
                    [[1.0, 0.0], [0.9, 0.1], [0.0, 1.0]]
                        .into_iter()
                        .map(|v| Some(v.into_iter().map(Some).collect::<Vec<_>>())),
                    2,
                ),
            ),
        ],
    )?;
    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?;
    let query = table
        .vector_search(&[0.0_f32, 1.0])?
        .only_if("category = 'animals'")
        .select(Select::columns(&["id", "text", "category", "_distance"]))
        .limit(2);
    let rows: Vec<RecordBatch> = query.execute().await?.try_collect().await?;
    println!("{rows:?}");

    Ok(())
}

The query vector is closest to the gardening record, but the filter excludes that category. The results contain only the two animal records.

Prefiltering and postfiltering

• Prefiltering, the default, limits the rows eligible for retrieval before search chooses its candidates.
• Postfiltering removes nonmatching rows after candidates are selected. It can return fewer than limit rows, including zero, even when matching records exist elsewhere in the table.

Both modes exclude rows that fail the predicate. They differ in which matching rows the search can find. On hybrid queries, the filter applies to both the vector and full-text searches.

SDK filters use SQL expressions without requiring a SQL connection. For Enterprise SQL queries, see SELECT.

Filtering examples by language

The following excerpts use a connected db handle. Create this sample table before running the queries beneath it; each query uses the same four records.

data = [
    {"vector": [3.1, 4.1], "item": "foo", "price": 10.0},
    {"vector": [5.9, 26.5], "item": "bar", "price": 20.0},
    {"vector": [10.2, 100.8], "item": "baz", "price": 30.0},
    {"vector": [1.4, 9.5], "item": "fred", "price": 40.0},
]
table = db.create_table("metadata_filter_example", data=data, mode="overwrite")

const data = [
  { vector: [3.1, 4.1], item: "foo", price: 10.0 },
  { vector: [5.9, 26.5], item: "bar", price: 20.0 },
  { vector: [10.2, 100.8], item: "baz", price: 30.0 },
  { vector: [1.4, 9.5], item: "fred", price: 40.0 },
];

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

Combine predicates

Repeated filter calls combine predicates with logical AND. For example, where("a > 0").where("b < 10") is equivalent to where("(a > 0) AND (b < 10)").

Use a single expression for a fixed filter. Chain calls when your application builds the filter incrementally.

# Both filters apply: item is in the list AND price is above 15.
result = (
    table.search([100, 102])
    .where("item IN ('foo', 'bar', 'baz')")
    .where("price > 15.0")
    .limit(3)
    .to_arrow()
)

// Both filters apply: item is in the list AND price is above 15.
const result = await table
  .search([100, 102])
  .where("item IN ('foo', 'bar', 'baz')")
  .where("price > 15.0")
  .limit(3)
  .toArray();

注意

In Python SDK versions before 0.34.0 and Rust/TypeScript SDK versions before 0.31.0, a second where(...) or only_if(...) call replaced the first filter, so only the last predicate was applied. If your code needs to run on those older versions, write a single predicate with AND instead of chaining calls. When upgrading to the latest SDKs, review any existing chained filters and drop earlier calls you no longer want to apply.

Filtering Without Vector Search

You can always filter your data without search. This is useful when you need to query based on metadata:

filtered_no_search_result = (
    table.search()
    .where("(item IN ('foo', 'bar', 'baz')) AND (price > 15.0)")
    .limit(3)
    .to_arrow()
)

const filteredResult = await table
  .query()
  .where("(item IN ('foo', 'bar', 'baz')) AND (price > 15.0)")
  .limit(3)
  .toArray();

Set limit() explicitly to control how many matching records your application receives.

Pre-Filtering with Vector Search

filtered_result = (
    table.search([100, 102])
    .where("(item IN ('foo', 'bar')) AND (price > 15.0)")
    .limit(3)
    .to_arrow()
)

const results = await table
  .search([100, 102])
  .where("(item IN ('foo', 'bar')) AND (price > 15.0)")
  .limit(3)
  .toArray();

Post-Filtering with Vector Search

post_filtered_result = (
    table.search([100, 102])
    .where("(item IN ('foo', 'bar')) AND (price > 15.0)", prefilter=False)
    .limit(3)
    .to_arrow()
)

const postFilteredResult = await table.vectorSearch([100, 102])
  .where("(item IN ('foo', 'bar')) AND (price > 15.0)")
  .postfilter()
  .limit(3)
  .toArray();

Postfiltering can return fewer than the requested number of records because the predicate removes candidates after search.

Write SQL predicates

Pass a SQL expression as the filter string. These expressions also select rows for SDK update and delete operations; they are not standalone SQL statements.

LanceDB supports a growing list of SQL expressions:

SQL Expression	Description
>, >=, <, <=, =	Comparison operators
AND, OR, NOT	Logical operators
IS NULL, IS NOT NULL	Null checks
IS TRUE, IS NOT TRUE, IS FALSE, IS NOT FALSE	Boolean checks
IN	Value matching from a set
LIKE, NOT LIKE	Pattern matching
CAST	Type conversion
regexp_match(column, pattern)	Regular expression matching
DataFusion Functions	Additional SQL functions

Simple SQL Filters

For example, the following filter string is acceptable:

table.search([100, 102]).where(
    "(item IN ('foo', 'baz')) AND (price > 20.0)"
).to_arrow()

await table
  .search([100, 102])
  .where("(item IN ('foo', 'baz')) AND (price > 20.0)")
  .toArray();

Advanced SQL Filters

If your column name contains special characters, upper-case characters, or is a SQL Keyword, you can use backtick (`) to escape it. For nested fields, each segment of the path must be wrapped in backticks.

`CUBE` = 10 AND `UpperCaseName` = '3' AND `column name with space` IS NOT NULL
AND `nested with space`.`inner with space` < 2

说明

Periods separate nested fields. LanceDB does not allow a literal period in a top-level column name; backticks do not remove that restriction.

Dates, Timestamps, Decimals

Literals for dates, timestamps, and decimals can be written by writing the string value after the type name. For example:

date_col = date '2021-01-01'
and timestamp_col = timestamp '2021-01-01 00:00:00'
and decimal_col = decimal(8,3) '1.000'

For timestamp columns, the precision can be specified as a number in the type parameter. Microsecond precision (6) is the default.

SQL	Time unit
timestamp(0)	Seconds
timestamp(3)	Milliseconds
timestamp(6)	Microseconds
timestamp(9)	Nanoseconds

Apache Arrow Mapping

LanceDB uses Arrow types for schemas and in-memory data. On disk, tables use the Lance format. The mapping from SQL types to Arrow types is:

SQL type	Arrow type
boolean	Boolean
tinyint / tinyint unsigned	Int8 / UInt8
smallint / smallint unsigned	Int16 / UInt16
int or integer / int unsigned or integer unsigned	Int32 / UInt32
bigint / bigint unsigned	Int64 / UInt64
float	Float32
double	Float64
decimal(precision, scale)	Decimal128
date	Date32
timestamp	Timestamp
string	Utf8
binary	Binary

Speed up frequently used filters

Consider a scalar index on frequently filtered columns. Use the query plan to check whether a predicate uses that index. Functions or casts applied to a column can change which optimizations are available. For list columns, a label-list index supports membership filters such as array_has_any and array_has_all.

Understand missing matches

A restrictive predicate can legitimately leave fewer rows than the requested limit. Approximate vector search can also miss qualifying neighbors, and postfiltering can discard candidates without retrieving replacements.

For a recall comparison, run the same query and predicate while bypassing the vector index on a suitably sized test table. Then compare the indexed query's results and latency while changing probe or refinement settings. Increasing those settings does not guarantee a full result set. See vector search and query plans.