Capsule 04: Where clauses in ChromaDB — the complete filter syntax

Capsule description

You have the schema (capsule 03) and the pre-filter decision (capsule 02). Now comes the practice: how filters are actually written in ChromaDB. The syntax is similar to MongoDB's, which is good news if you come from a JS stack, but it has its quirks. Learning the six operators and how to combine them will save you from the two common traps: filters so restrictive they return zero results, and filters so loose they don't exploit pre-filtering's potential.

This capsule teaches you the full syntax with practical examples, how to build filters dynamically from user input (without SQL injection), and the patterns for combining multiple filters with AND/OR/NOT correctly.

By the end of this capsule you'll be able to:

• ✅ Use the six main operators: equality, $ne, $gt/$gte/$lt/$lte, $in, $and, $or
• ✅ Build where clauses dynamically from user input with no risk
• ✅ Combine filters with AND/OR for complex queries
• ✅ Tell where (metadata) apart from where_document (a substring of the text)
• ✅ Anticipate the typical errors: incompatible types, unindexed keys, a misread implicit AND
• ✅ Implement guardrails for multi-tenant queries that can NOT run without a tenant_id

Estimated time: 30-35 minutes

The six operators that cover 95% of cases

Operator 1: equality (the default)

collection.query(
    query_texts=[query],
    where={"tenant_id": "acme_corp"},
    n_results=5,
)

When you pass a value with no explicit operator, ChromaDB assumes =. It's the most common case.

Operator 2: $ne (not equal)

where={"category": {"$ne": "marketing"}}  # everything except marketing

Useful for excluding specific categories.

Operator 3: numeric ranges ($gt, $gte, $lt, $lte)

import time
one_year_ago = int(time.time()) - (365 * 24 * 60 * 60)

where={"created_at": {"$gte": one_year_ago}}  # docs from the last year

It only works with numeric types. That's why created_at must be an int (a Unix timestamp), not a string.

Operator 4: $in and $nin (membership)

# Docs from the support OR docs categories
where={"category": {"$in": ["support", "docs"]}}

# Exclude multiple categories
where={"category": {"$nin": ["marketing", "internal"]}}

More readable and often faster than an $or with multiple equality checks.

Operator 5: $and (every filter must hold)

# Implicit AND (simpler for basic cases)
where={
    "tenant_id": "acme",
    "language": "en",
    "type": "tutorial",
}

# Explicit AND (necessary to combine with $or)
where={
    "$and": [
        {"tenant_id": "acme"},
        {"language": "en"},
        {"created_at": {"$gte": one_year_ago}},
    ]
}

Operator 6: $or (at least one must hold)

# Support OR documentation docs
where={
    "$or": [
        {"category": "support"},
        {"category": "docs"},
    ]
}

# A combination: tenant + (recent OR high priority)
where={
    "$and": [
        {"tenant_id": "acme"},
        {
            "$or": [
                {"created_at": {"$gte": one_year_ago}},
                {"priority": "high"},
            ]
        },
    ]
}

The critical pattern: when you combine $or with tenant_id, the tenant_id must ALWAYS sit outside the $or so the isolation doesn't get relaxed.

The difference between where and where_document

ChromaDB has two kinds of filter:

# where: filters by metadata
collection.query(
    query_texts=[query],
    where={"tenant_id": "acme"},
)

# where_document: filters by substring in the document's text
collection.query(
    query_texts=[query],
    where_document={"$contains": "OAuth2PasswordBearer"},
)

# Combine the two
collection.query(
    query_texts=[query],
    where={"tenant_id": "acme"},
    where_document={"$contains": "OAuth2"},
)

where_document is useful for cases where you need an exact substring match in the text. But watch out:

• It only supports $contains and $not_contains.
• It is NOT advanced full-text search (BM25). For that, hybrid search (M05).

Building filters dynamically

In production, the filters come from user input or from the app's context:

def build_filter(
    tenant_id: str,                    # mandatory
    category: str | None = None,
    language: str | None = None,
    days_ago: int | None = None,
    tags: list[str] | None = None,
) -> dict:
    """Builds the where clause dynamically, always enforcing tenant_id."""
    if not tenant_id:
        raise ValueError("tenant_id is mandatory")

    conditions = [{"tenant_id": tenant_id}]

    if category:
        conditions.append({"category": category})
    if language:
        conditions.append({"language": language})
    if days_ago:
        cutoff_ts = int(time.time()) - (days_ago * 86400)
        conditions.append({"created_at": {"$gte": cutoff_ts}})
    if tags:
        conditions.append({"tags": {"$in": tags}})

    # If there's only one condition, return it alone (don't wrap it in $and needlessly)
    if len(conditions) == 1:
        return conditions[0]
    return {"$and": conditions}


# Usage from an endpoint
filter_dict = build_filter(
    tenant_id=current_user.tenant_id,
    category="auth",
    language="en",
    days_ago=180,
)

results = collection.query(
    query_texts=[query],
    where=filter_dict,
    n_results=5,
)

Input validation (no injection)

ChromaDB validates types internally, but it's worth validating beforehand for clear error messages:

ALLOWED_CATEGORIES = {"auth", "billing", "support", "docs"}
ALLOWED_LANGUAGES = {"en", "es", "pt"}


def validate_inputs(category: str | None, language: str | None) -> None:
    if category and category not in ALLOWED_CATEGORIES:
        raise ValueError(f"Invalid category: {category}. Allowed: {ALLOWED_CATEGORIES}")
    if language and language not in ALLOWED_LANGUAGES:
        raise ValueError(f"Invalid language: {language}. Allowed: {ALLOWED_LANGUAGES}")

Typical use cases

Case 1: a filter for a multi-tenant SaaS

where = {"tenant_id": user.tenant_id}

Always apply it, no exceptions. Covered in capsule 05.

Case 2: a filter on recency + relevance

where = {
    "$and": [
        {"tenant_id": "acme"},
        {"created_at": {"$gte": six_months_ago_ts}},
    ]
}

For queries where old content is misleading (deprecated APIs, outdated info).

Case 3: a filter on the user's permissions

where = {
    "$and": [
        {"tenant_id": user.tenant_id},
        {"$or": [
            {"visibility": "public"},
            {"visibility": "private", "owner_id": user.id},
        ]}
    ]
}

Only public docs, or the user's own private ones.

Case 4: a filter on enabled features

# Docs about features the user's plan includes
allowed_features = user.subscription.features  # e.g. ["api_v2", "analytics"]
where = {
    "$and": [
        {"tenant_id": user.tenant_id},
        {"feature": {"$in": allowed_features}},
    ]
}

Case 5: a filter excluding deprecated content

where = {
    "$and": [
        {"tenant_id": "acme"},
        {"status": {"$nin": ["deprecated", "draft"]}},
    ]
}

Only approved, current content.

Traps and common mistakes

Trap 1: a misread implicit AND

The mistake: we want (category=A AND lang=en) OR (category=B AND lang=es).

# ❌ Bad: the implicit AND ANDs EVERYTHING together
where={
    "$or": [
        {"category": "A", "language": "en"},  # ← this is A AND en
        {"category": "B", "language": "es"},  # ← this is B AND es
    ]
}
# Result: (A AND en) OR (B AND es). In this case it's what you wanted,
# BUT some people misread it and put the pairs as an AND at the root level.

How to avoid the confusion: use an explicit $and whenever you combine with $or:

where={
    "$or": [
        {"$and": [{"category": "A"}, {"language": "en"}]},
        {"$and": [{"category": "B"}, {"language": "es"}]},
    ]
}

Trap 2: ranges over strings

The mistake:

where={"created_at": {"$gte": "2024-01-01"}}  # a string

The symptom: it works by coincidence if the strings sort lexicographically (e.g. ISO dates), but it fails with anything else.

How to prevent it: Unix timestamps as int, always.

Trap 3: a filter on a key that doesn't exist in the metadata

where={"nonexistent_field": "value"}

The symptom: ChromaDB returns zero results with no error. You think your filter is excluding everything, but you're actually filtering on a field that doesn't exist.

How to prevent it: pre-ingest validation enforces a consistent schema. Without valid metadata, the doc doesn't get indexed.

Trap 4: inconsistent types in the metadata

# Doc 1: priority=1 (int)
# Doc 2: priority="1" (string)

where={"priority": {"$lte": 2}}  # only matches Doc 1

The symptom: the filter looks like it works but it silently loses docs with inconsistent types.

How to prevent it: the Pydantic schema (capsule 03) enforces types at ingest time.

Trap 5: $in with an empty list

where={"category": {"$in": []}}  # ← an empty list

The symptom: ChromaDB can behave inconsistently (some versions return everything, others nothing).

How to prevent it: validate beforehand:

if not allowed_categories:
    # Decide: reject it, or don't apply the filter
    raise ValueError("allowed_categories cannot be empty")
where = {"category": {"$in": allowed_categories}}

Trap 6: forgetting tenant_id in internal queries

The mistake: admin scripts that call collection.query(query_texts=[q]) with no filter.

The symptom: internal logs show cross-tenant data. The audit fails.

How to prevent it: use secure_query always (capsule 02), even in scripts.

Applied exercise

Scenario: you're an AI Engineer at a project management SaaS platform. The data:

• 100 tenants
• Each tenant has tasks, comments, documents
• Each user has a role: admin, member, viewer
• Some docs are private (creator only), team (the project's members), tenant (everyone at the company)

The API endpoint: /api/search receives the user's query.

Your job:

1. Design the build_secure_filter function that builds the appropriate where.
2. Implement the visibility logic by role and permission.
3. Define the edge cases that need testing.

# secure_filter.py
from typing import Optional
import time


def build_secure_filter(
    user_id: str,
    user_role: str,
    tenant_id: str,
    project_ids: list[str],         # the projects the user belongs to
    optional_filters: dict = None,
) -> dict:
    """
    Builds a where clause with multi-level security:
    - tenant_id is mandatory
    - a visibility filter based on role and permissions
    - the optional filters are applied as an AND
    """
    if not tenant_id or not user_id:
        raise PermissionError("tenant_id and user_id are mandatory")

    # The visibility filter, based on role
    visibility_clauses = []

    # 1. The tenant's public docs are always visible
    visibility_clauses.append({"visibility": "tenant"})

    # 2. Team docs: only if the user is a member of the project
    if project_ids:
        visibility_clauses.append({
            "$and": [
                {"visibility": "team"},
                {"project_id": {"$in": project_ids}},
            ]
        })

    # 3. Private docs: only the user's own
    visibility_clauses.append({
        "$and": [
            {"visibility": "private"},
            {"owner_id": user_id},
        ]
    })

    # 4. If admin: they also see the internal_admin docs
    if user_role == "admin":
        visibility_clauses.append({"visibility": "admin_only"})

    # Combine: tenant + (any valid visibility) + the optional filters
    base_clauses = [
        {"tenant_id": tenant_id},
        {"$or": visibility_clauses},
    ]

    # Add the optional filters (category, date, etc.)
    if optional_filters:
        for key, value in optional_filters.items():
            base_clauses.append({key: value})

    return {"$and": base_clauses}


# Usage from the endpoint
def search_endpoint(query: str, user, optional_filters: dict = None):
    where = build_secure_filter(
        user_id=user.id,
        user_role=user.role,
        tenant_id=user.tenant_id,
        project_ids=user.project_ids,
        optional_filters=optional_filters,
    )

    return collection.query(
        query_texts=[query],
        where=where,
        n_results=10,
    )

def test_security_filters():
    # Test 1: a user with no tenant_id → PermissionError
    with pytest.raises(PermissionError):
        build_secure_filter(user_id="x", user_role="member", tenant_id="", project_ids=[])

    # Test 2: a viewer does NOT see admin_only docs
    where = build_secure_filter("u1", "viewer", "tenant_a", project_ids=[])
    # Verify it doesn't include visibility="admin_only"

    # Test 3: a member does NOT see team docs from projects they do NOT belong to
    where = build_secure_filter("u1", "member", "tenant_a", project_ids=["proj_a"])
    # A team doc from proj_b should NOT pass the filter

    # Test 4: an admin DOES see admin_only
    where = build_secure_filter("u1", "admin", "tenant_a", project_ids=[])
    # Verify admin_only is in visibility_clauses

    # Test 5: cross-tenant always fails
    where = build_secure_filter("u1", "admin", "tenant_a", project_ids=[])
    # Index docs in tenant_a and tenant_b. Run the query with this where.
    # NO doc from tenant_b may show up.

    # Test 6: the optional filters do NOT bypass tenant_id
    where = build_secure_filter("u1", "member", "tenant_a", project_ids=[], optional_filters={"category": "auth"})
    # Verify tenant_id is still present

Summary and next step

What you learned:

• Six operators cover 95% of cases: equality, $ne, $gt/$gte/$lt/$lte, $in/$nin, $and, $or.
• The implicit AND is fine for simple cases; use explicit AND/OR when you combine them.
• where filters metadata, where_document filters substrings of the text.
• Build filters dynamically with a dedicated function that enforces a mandatory tenant_id.
• The typical cases: multi-tenant, recency, permissions, features.
• The traps: a badly nested AND, ranges over strings, nonexistent keys, inconsistent types, an empty list in $in.

Checkpoint: before moving on, you should be able to:

• Use the six operators in real queries.
• Build dynamic filters while validating the inputs.
• Design a visibility filter for multiple roles with security tests.

Next capsule: 05 — Multi-tenant isolation.

We covered the operators. Capsule 05 digs into the most operationally critical case: isolation between tenants. You'll see implementation patterns, automated isolation tests, and how to prevent the "silent leak" that only gets discovered when someone reports having seen another company's data.

Resources

1. ChromaDB — Metadata Filtering Operators — The complete syntax
2. MongoDB Query Operators — ChromaDB inherits many of these operators
3. Pinecone — Filter Reference — For comparison
4. LangChain — Self Query Retriever — Automatic filter construction
5. OWASP Multi-Tenancy — Security
6. Pydantic for Validation — Input validation

Estimated time: 30-35 minutes Next: 05-multi-tenant-isolation.md