Filtering

As a relational database, TiDB supports a rich set of SQL operators and flexible combinations of filtering conditions for precise queries.

Overview

You can filter on both scalar fields and JSON fields. Filtering on JSON fields is often used for metadata filtering in vector search.

pytidb is the official Python SDK for TiDB, designed to help developers build AI applications efficiently.

When using pytidb, you can apply filtering by passing the filters parameter to the table.query(), table.delete(), table.update(), and table.search() methods.

The filters parameter supports two formats: Dictionary filters and SQL string filters.

Dictionary filters

pytidb lets you define filter conditions using a Python dictionary with operators as the filters parameter.

The dictionary structure of filters is as follows:

{
    "<key>": {
        "<operator>": <value>
    },
    ...
}

• <key>: The key can be a column name, a JSON path expression to access a JSON field (see Metadata filtering), or a logical operator.
• <operator>: The operator can be a compare operator or an inclusion operator.
• <value>: The value can be a scalar value or an array, depending on the operator.

Example: Filter records where created_at is greater than 2024-01-01

table.query({
    # The `created_at` is a scalar field with DATETIME type
    "created_at": {
        "$gt": "2024-01-01"
    }
})

Example: Filter records where meta.category is in the array ["tech", "science"]

results = (
    table.search("some query", search_type="vector")
        .filter({
            # The `meta` is a JSON field, and its value is a JSON object like {"category": "tech"}
            "meta.category": {
                "$in": ["tech", "science"]
            }
        })
        .limit(10)
        .to_list()
)

Compare operators

You can use the following comparison operators to filter records:

Example: Filter records where user_id equals 1

{
    "user_id": {
        "$eq": 1
    }
}

You can omit the $eq operator. The following filter is equivalent to the preceding one:

{
    "user_id": 1
}

Inclusion operators

You can use the following inclusion operators to filter records:

Example: Filter records where category is in the array ["tech", "science"]

{
    "category": {
        "$in": ["tech", "science"]
    }
}

Logical operators

You can use the logical operators $and and $or to combine multiple filters.

Syntax for $and or $or:

{
    "$and|$or": [
        {
            "field_name": {
                <operator>: <value>
            }
        },
        {
            "field_name": {
                <operator>: <value>
            }
        }
        ...
    ]
}

Example: using $and to combine multiple filters:

{
    "$and": [
        {
            "created_at": {
                "$gt": "2024-01-01"
            }
        },
        {
            "meta.category": {
                "$in": ["tech", "science"]
            }
        }
    ]
}

SQL String Filters

You can also use a SQL string as filters. The string must be a valid SQL WHERE clause (without the WHERE keyword) in the TiDB SQL syntax.

Example: Filter records where created_at is greater than 2024-01-01

results = table.query(
    filters="created_at > '2024-01-01'",
    limit=10
).to_list()

Example: Filter records where the JSON field meta.category equals 'tech'

results = table.query(
    filters="meta->>'$.category' = 'tech'",
    limit=10
).to_list()

You can combine multiple conditions using AND, OR, and parentheses, and use any TiDB-supported SQL operators.