Firestore Compound Queries — Combining Multiple Conditions with Composite Indexes

In this tutorial, you will learn about Firestore Compound Queries. We cover key concepts, practical examples, and best practices to help you master this topic.

Firestore compound queries combine multiple where conditions, range filters, and orderBy clauses, requiring Composite indexes to support efficient query execution across multiple fields.

What You'll Learn

• Writing compound queries with multiple conditions
• Creating composite indexes automatically and manually
• Understanding query planning and index selection

Why It Matters

Compound queries power complex application features like filtered search, sorted views, and range-based reporting. Poor index design causes query failures or high costs. DodaTech's reporting dashboard uses compound queries for filtered date ranges.

flowchart TD
    A["Compound Query"] --> B{"Fields used"}
    B --> C["Equality fields"]
    B --> D["Range fields"]
    B --> E["Order field"]
    C --> F["Composite Index Required"]
    D --> F
    E --> F
    F --> G["Index: (equality, range, order)"]
    G --> H["Efficient query execution"]
    style H fill:#86efac,stroke:#16a34a

Code Examples

// Compound query with equality + range + orderBy
import { collection, query, where, orderBy, limit, getDocs } from 'firebase/firestore';

// Find active users with high scores, ordered by score
// Requires composite index on (isActive, score)
const q = query(
  collection(db, 'users'),
  where('isActive', '==', true),     // Equality
  where('score', '>=', 100),          // Range
  orderBy('score', 'desc'),           // Order
  limit(20)
);

// Compound with multiple equality filters
// Requires composite index on (role, isActive, createdAt)
const q2 = query(
  collection(db, 'users'),
  where('role', '==', 'admin'),       // Equality 1
  where('isActive', '==', true),      // Equality 2
  orderBy('createdAt', 'desc'),       // Order
  limit(50)
);

const snapshot = await getDocs(q2);

# Python compound queries
from google.cloud import firestore
from google.cloud.firestore import FieldFilter

db = firestore.Client()

# Compound query with equality and range
query = (
    db.collection('orders')
    .where(filter=FieldFilter('status', '==', 'completed'))
    .where(filter=FieldFilter('total', '>=', 100))
    .order_by('total', direction=firestore.Query.DESCENDING)
    .limit(25)
)

# Compound with IN clause (OR-like behavior)
query_in = (
    db.collection('products')
    .where(filter=FieldFilter('category', 'in', ['electronics', 'software']))
    .where(filter=FieldFilter('price', '<=', 50))
    .order_by('price')
)

for doc in query_in.stream():
    print(f'{doc.id}: {doc.to_dict()}')

// firestore.indexes.json - Predefined composite indexes
{
  "indexes": [
    {
      "collectionGroup": "users",
      "queryScope": "COLLECTION",
      "fields": [
        { "fieldPath": "isActive", "order": "ASCENDING" },
        { "fieldPath": "score", "order": "DESCENDING" }
      ]
    },
    {
      "collectionGroup": "orders",
      "queryScope": "COLLECTION",
      "fields": [
        { "fieldPath": "status", "order": "ASCENDING" },
        { "fieldPath": "total", "order": "ASCENDING" },
        { "fieldPath": "createdAt", "order": "DESCENDING" }
      ]
    }
  ]
}

# Deploy indexes
firebase deploy --only firestore:indexes

# View current indexes
# Console > Firestore > Indexes

Common Mistakes

1. Not Including OrderBy When Using Range Filters

Range filters (<, >, <=, >=) require an orderBy on the range field.

2. Creating Too Many Composite Indexes

Each index adds write overhead. Index only fields that are actually queried together.

3. Using the Wrong Field Order in Indexes

Indexes must follow the pattern: equality fields first, then range/order field.

4. Forgetting to Deploy Indexes After Adding Queries

New queries fail without the required indexes. Deploy indexes as part of your deployment.

5. Not Using the Auto-Create Feature

Firestore CLI logs include the exact index creation command. Use the link in error messages to auto-create indexes.

Practice Questions

1. What is a composite index in Firestore?
2. What is the correct field order for composite indexes?
3. How do you create a composite index?
4. What happens when a query needs a missing composite index?
5. How many composite indexes can you have per Firestore database?

Answers:

1. An index on multiple fields that supports compound queries.
2. Equality fields first, then range field, then orderBy field.
3. Automatically via the link in error messages, manually in the console, or via firestore.indexes.json.
4. The query fails with an error and provides a link to create the required index.
5. Up to 200 composite indexes per database.

Challenge: Design a query system for an e-commerce application. Create compound queries for filtering products by category, price range, and availability, sorted by popularity. Create all required composite indexes and verify query performance.

FAQ

Mini Project

Build a Firestore index management tool: scan your application code for compound queries, detect which composite indexes are required, compare against existing indexes, generate the firestore.indexes.json file, and report missing indexes. Create a dashboard showing index usage statistics.

What's Next

Deep dive into Firestore indexes including composite indexes and query performance, then explore real-time listeners with onSnapshot.