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 map, list, set, and 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.

This is the default when you do not specify an operator.

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

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
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.

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
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.

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
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.

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
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.

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
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.

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
table = database.get_table("TABLE_NAME")

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

print(result)

In

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

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
table = database.get_table("TABLE_NAME")

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

print(result)

Not in

The $nin operator matches rows that don’t contain any of the specified values.

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
table = database.get_table("TABLE_NAME")

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

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
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
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
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
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)