Filter operators for tables (Python)

Many Data API commands, like Find rows (Python), accept a filter parameter to determine which rows to return or update. The filter parameter uses one or more filter operators.

You can use filter operators on all supported types except vector. For information about vector search on tables, see Sort clauses for tables (Python).

Operators

You can use the following operators in a filter. All operators are case-sensitive.

Equal (default)

The $eq operator matches rows where the specified column’s value is equal to the specified value.

You can’t use this operator on a column that is associated with a text index.

This is the default when you don’t specify an operator and the column has a regular index or is not indexed.

$eq is not supported for map, list, or set columns.

This is the only filter operator allowed in updateOne and deleteOne.

from astrapy import DataAPIClient

# Get an existing table
client = DataAPIClient()
database = client.get_database(
    "API_ENDPOINT", token="APPLICATION_TOKEN"
)
table = database.get_table("TABLE_NAME")

# Find a row
result = table.find_one({"number_of_pages": {"$eq": 714}})

print(result)

Not equal

The $ne operator matches rows where the specified column’s value is not equal to the specified value.

You can’t use this operator on a column that is associated with a text index.

$ne is not supported for map, list, or set columns.

from astrapy import DataAPIClient

# Get an existing table
client = DataAPIClient()
database = client.get_database(
    "API_ENDPOINT", token="APPLICATION_TOKEN"
)
table = database.get_table("TABLE_NAME")

# Find a row
result = table.find_one({"number_of_pages": {"$ne": 300}})

print(result)

Greater than

The $gt operator matches rows where the specified column’s value is greater than the specified value.

You can’t use this operator on a column that is associated with a text index.

from astrapy import DataAPIClient

# Get an existing table
client = DataAPIClient()
database = client.get_database(
    "API_ENDPOINT", token="APPLICATION_TOKEN"
)
table = database.get_table("TABLE_NAME")

# Find a row
result = table.find_one({"number_of_pages": {"$gt": 300}})

print(result)

Greater than or equal

The $gte operator matches rows where the specified column’s value is greater than or equal to the specified value.

You can’t use this operator on a column that is associated with a text index.

from astrapy import DataAPIClient

# Get an existing table
client = DataAPIClient()
database = client.get_database(
    "API_ENDPOINT", token="APPLICATION_TOKEN"
)
table = database.get_table("TABLE_NAME")

# Find a row
result = table.find_one({"number_of_pages": {"$gte": 300}})

print(result)

Less than

The $lt operator matches rows where the specified column’s value is less than the specified value.

You can’t use this operator on a column that is associated with a text index.

from astrapy import DataAPIClient

# Get an existing table
client = DataAPIClient()
database = client.get_database(
    "API_ENDPOINT", token="APPLICATION_TOKEN"
)
table = database.get_table("TABLE_NAME")

# Find a row
result = table.find_one({"number_of_pages": {"$lt": 300}})

print(result)

Less than or equal

The $lte operator matches rows where the specified column’s value is less than or equal to the specified value.

You can’t use this operator on a column that is associated with a text index.

from astrapy import DataAPIClient

# Get an existing table
client = DataAPIClient()
database = client.get_database(
    "API_ENDPOINT", token="APPLICATION_TOKEN"
)
table = database.get_table("TABLE_NAME")

# Find a row
result = table.find_one({"number_of_pages": {"$lte": 300}})

print(result)

In

The behavior of $in depends on the type of column it is applied to:

  • List and set columns: The $in operator matches rows where the filtered column contains any of the specified values.

  • Map columns: The $in operator matches rows where the filtered column contains any of the specified key-value pairs. Each key-value pair is specified as an array.

    To match specific keys or specific values, rather than key-value pairs, use the $keys or $values operator.

  • Other column types: The $in operator matches rows where the filtered column has a value equal to any of the specified values.

For a non-map column, specify the values as an array:

You can’t use this operator on a column that is associated with a text index.

from astrapy import DataAPIClient

# Get an existing table
client = DataAPIClient()
database = client.get_database(
    "API_ENDPOINT", token="APPLICATION_TOKEN"
)
table = database.get_table("TABLE_NAME")

# Find a row
result = table.find_one(
    {"author": {"$in": ["Sara Jones", "Jennifer Ray"]}}
)

print(result)

For map columns, specify the values as an array of key-value pairs, where each key-value pair is an array:

from astrapy import DataAPIClient

# Get an existing table
client = DataAPIClient()
database = client.get_database(
    "API_ENDPOINT", token="APPLICATION_TOKEN"
)
table = database.get_table("TABLE_NAME")

# Find a row
result = table.find_one(
    {
        "metadata": {
            "$in": [
                ["language", "French"],
                ["edition", "Illustrated Edition"],
            ]
        }
    }
)

print(result)

Not in

The behavior of $nin depends on the type of column it is applied to:

  • List and set columns: The $nin operator matches rows where the column doesn’t contain any of the specified values.

  • Map columns: The $nin operator matches rows where the column doesn’t contain any of the specified key-value pairs. Each key-value pair is specified as an array.

    To match specific keys or specific values, rather than key-value pairs, use the $keys or $values operator.

  • Other column types: The $nin operator matches rows that don’t contain any of the specified values.

For a non-map column, specify the values as an array:

You can’t use this operator on a column that is associated with a text index.

from astrapy import DataAPIClient

# Get an existing table
client = DataAPIClient()
database = client.get_database(
    "API_ENDPOINT", token="APPLICATION_TOKEN"
)
table = database.get_table("TABLE_NAME")

# Find a row
result = table.find_one(
    {"author": {"$nin": ["Sara Jones", "Jennifer Ray"]}}
)

print(result)

For map columns, specify the values as an array of key-value pairs, where each key-value pair is an array:

from astrapy import DataAPIClient

# Get an existing table
client = DataAPIClient()
database = client.get_database(
    "API_ENDPOINT", token="APPLICATION_TOKEN"
)
table = database.get_table("TABLE_NAME")

# Find a row
result = table.find_one(
    {
        "metadata": {
            "$nin": [
                ["language", "French"],
                ["edition", "Illustrated Edition"],
            ]
        }
    }
)

print(result)

All

The behavior of $all depends on the type of column it is applied to:

  • List and set columns: The $all operator matches rows where the column contains all of the specified values.

  • Map columns: The $all operator matches rows where the column contains all of the specified key-value pairs. Each key-value pair is specified as an array.

    To match specific keys or specific values, rather than key-value pairs, use the $keys or $values operator.

The $all operator is only supported for map, list, and set columns.

For a list or set column, specify the values as an array:

from astrapy import DataAPIClient

# Get an existing table
client = DataAPIClient()
database = client.get_database(
    "API_ENDPOINT", token="APPLICATION_TOKEN"
)
table = database.get_table("TABLE_NAME")

# Find a row
result = table.find_one({"genres": {"$all": ["Fantasy", "Romance"]}})

print(result)

For map columns, specify the values as an array of key-value pairs, where each key-value pair is an array:

from astrapy import DataAPIClient

# Get an existing table
client = DataAPIClient()
database = client.get_database(
    "API_ENDPOINT", token="APPLICATION_TOKEN"
)
table = database.get_table("TABLE_NAME")

# Find a row
result = table.find_one(
    {
        "metadata": {
            "$all": [
                ["language", "Italian"],
                ["edition", "Illustrated Edition"],
            ]
        }
    }
)

print(result)

Keys

Only supported for map columns.

In conjunction with $in, the $keys operator matches rows where the map column contains any of the specified keys.

In conjunction with $all, the $keys operator matches rows where the map column contains all of the specified keys.

In conjunction with $nin, the $keys operator matches rows where the map column doesn’t contain any of the specified keys.

from astrapy import DataAPIClient

# Get an existing table
client = DataAPIClient()
database = client.get_database(
    "API_ENDPOINT", token="APPLICATION_TOKEN"
)
table = database.get_table("TABLE_NAME")

# Find a row
result = table.find_one(
    {"metadata": {"$keys": {"$in": ["language", "edition"]}}}
)

print(result)

Values

Only supported for map columns.

In conjunction with $in, the $values operator matches rows where the map column contains any of the specified values.

In conjunction with $all, the $values operator matches rows where the map column contains all of the specified values.

In conjunction with $nin, the $values operator matches rows where the map column doesn’t contain any of the specified values.

from astrapy import DataAPIClient

# Get an existing table
client = DataAPIClient()
database = client.get_database(
    "API_ENDPOINT", token="APPLICATION_TOKEN"
)
table = database.get_table("TABLE_NAME")

# Find a row
result = table.find_one(
    {"metadata": {"$values": {"$in": ["French", "Illustrated Edition"]}}}
)

print(result)

Match

Lexicographical matching is currently in public preview. Development is ongoing, and the features and functionality are subject to change. Astra DB Serverless, and the use of such, is subject to the DataStax Preview Terms.

The $match operator matches rows where the specified column’s value is a lexicographical match to the specified string of space-separated keywords or terms.

You can only use the $match operator on a column that is associated with a text index. This is the only filter operator that you can use on columns associated with a text index.

from astrapy import DataAPIClient

# Get an existing table
client = DataAPIClient()
database = client.get_database(
    "API_ENDPOINT", token="APPLICATION_TOKEN"
)
table = database.get_table("TABLE_NAME")

# Find a row
result = table.find_one(
    {"summary": {"$match": "futuristic laboratory discovery"}}
)

print(result)

Combine operators (And, Or)

The $and operator joins clauses with a logical AND. Only rows that match the conditions of all clauses are returned.

The $or operator joins clauses with a logical OR. Rows that match the condition of any of the clauses are returned.

You can use $and and $or separately or together. If you want to match multiple flexible conditions, use $and and $or together in the same filter:

from astrapy import DataAPIClient

# Get an existing table
client = DataAPIClient()
database = client.get_database(
    "API_ENDPOINT", token="APPLICATION_TOKEN"
)
table = database.get_table("TABLE_NAME")

# Find a row
result = table.find_one(
    {
        "$and": [
            {
                "$or": [
                    {"is_checked_out": False},
                    {"number_of_pages": {"$lt": 300}},
                ]
            },
            {
                "$or": [
                    {"rating": {"$lt": 4.3}},
                    {"publication_year": {"$gte": 2002}},
                ]
            },
        ]
    }
)

print(result)

To filter on a range of values, use $and with $lt/$lte and $gt/$gte:

from astrapy import DataAPIClient

# Get an existing table
client = DataAPIClient()
database = client.get_database(
    "API_ENDPOINT", token="APPLICATION_TOKEN"
)
table = database.get_table("TABLE_NAME")

# Find a row
result = table.find_one(
    {
        "$and": [
            {"number_of_pages": {"$lt": 300}},
            {"number_of_pages": {"$gte": 200}},
        ]
    }
)

print(result)

Was this helpful?

Give Feedback

How can we improve the documentation?

© Copyright IBM Corporation 2026 | Privacy policy | Terms of use |  Manage Privacy Choices

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: Contact IBM