In and not-in queries in Firebase - Deep Dive

Overview - In and not-in queries

What is it?

In and not-in queries in Firebase allow you to filter data by checking if a field's value matches any value in a list (in) or does not match any value in a list (not-in). These queries help you find documents that meet specific criteria quickly without scanning the entire database. They are useful when you want to find multiple possible matches or exclude certain values in your search.

Why it matters

Without in and not-in queries, you would have to fetch all data and filter it manually, which is slow and costly. These queries make your app faster and cheaper by letting Firebase do the filtering on the server side. This improves user experience by showing relevant data quickly and reduces the load on your app and database.

Where it fits

Before learning in and not-in queries, you should understand basic Firebase queries and how to filter data by single values. After mastering these, you can learn about more complex queries like array-contains, compound queries, and pagination to handle larger datasets efficiently.

Mental Model

Core Idea

In and not-in queries let you ask Firebase to find documents where a field's value is inside or outside a specific list of values.

Think of it like...

It's like asking a librarian to find all books whose authors are in your favorite authors list (in query) or to find all books whose authors are not in that list (not-in query).

Query Types
───────────────
│ Field Value  │
├─────────────┤
│ in: [a,b,c] │ → Matches if value is a, b, or c
│ not-in: [x,y,z] │ → Matches if value is NOT x, y, or z
───────────────

Build-Up - 7 Steps

1

Foundation

Basic equality filtering in Firebase

Concept: Learn how to filter documents by a single field value using simple equality.

In Firebase, you can query documents where a field equals a specific value using: firestore.collection('users').where('status', '==', 'active') This returns all documents where the 'status' field is exactly 'active'.

Result

You get only documents with 'status' equal to 'active'.

Understanding simple equality queries is the base for more complex filters like in and not-in.

2

Foundation

Limitations of single-value filters

Concept: Recognize why filtering by one value is not enough when you want multiple matches.

If you want users with status 'active' or 'pending', you might try: firestore.collection('users').where('status', '==', 'active').where('status', '==', 'pending') But this returns no results because a field can't be both values at once.

Result

No documents are returned because the query is impossible.

Knowing this limitation motivates the need for in and not-in queries to handle multiple values.

3

Intermediate

Using 'in' queries for multiple matches

🤔Before reading on: do you think 'in' queries can accept any number of values or only a few? Commit to your answer.

Concept: 'In' queries let you filter documents where a field matches any value in a list, up to a limit.

You can write: firestore.collection('users').where('status', 'in', ['active', 'pending', 'blocked']) This returns documents where 'status' is 'active', 'pending', or 'blocked'. Note: Firebase limits the list to 10 values max.

Result

Documents with any of the listed statuses are returned.

Understanding 'in' queries lets you efficiently find documents matching multiple possible values without multiple queries.

4

Intermediate

Using 'not-in' queries to exclude values

🤔Before reading on: do you think 'not-in' queries exclude documents with any listed value or only those with all listed values? Commit to your answer.

Concept: 'Not-in' queries filter documents where a field's value is not in a given list of values.

Example: firestore.collection('users').where('status', 'not-in', ['blocked', 'deleted']) This returns documents where 'status' is neither 'blocked' nor 'deleted'.

Result

Documents excluding the listed statuses are returned.

Knowing 'not-in' queries helps you exclude unwanted data efficiently, improving query precision.

5

Intermediate

Restrictions and limits on in/not-in queries

🤔Before reading on: do you think you can combine multiple 'in' or 'not-in' filters on different fields in one query? Commit to your answer.

Concept: Firebase imposes limits on in and not-in queries, such as max list size and query combinations.

Key restrictions: - Max 10 values in the list. - Cannot combine multiple 'in' or 'not-in' filters in one query. - Cannot use 'not-in' with '!=' or 'not-equal' filters. These rules ensure query performance and consistency.

Result

Queries violating these rules will fail with errors.

Understanding these limits prevents common errors and helps design queries that Firebase accepts.

6

Advanced

Combining in/not-in with other filters

🤔Before reading on: do you think 'in' queries can be combined with range filters like '>' or '<'? Commit to your answer.

Concept: You can combine in/not-in queries with some other filters, but there are rules to follow.

Example: firestore.collection('users') .where('status', 'in', ['active', 'pending']) .where('age', '>', 18) This works because 'in' is combined with a range filter on a different field. However, combining multiple 'in' or 'not-in' filters or mixing with '!=' filters is not allowed.

Result

You get documents matching the combined criteria.

Knowing how to combine filters lets you build powerful queries while respecting Firebase's rules.

7

Expert

Performance and indexing considerations

🤔Before reading on: do you think in/not-in queries require special indexes or run slower than simple equality queries? Commit to your answer.

Concept: In and not-in queries rely on indexes and can impact performance if not used carefully.

Firebase automatically creates indexes for simple queries, but complex in/not-in queries may need composite indexes. Also, large lists in 'in' queries can slow down queries. Monitoring query performance and creating necessary indexes ensures fast responses.

Result

Properly indexed queries run efficiently; missing indexes cause errors or slow queries.

Understanding indexing needs helps avoid runtime errors and keeps your app responsive.

Under the Hood

Firebase stores data in collections of documents indexed by fields. When you run an in or not-in query, Firebase uses these indexes to quickly find documents where the field's value matches or does not match any value in the provided list. Internally, it performs multiple index lookups and merges results efficiently without scanning all documents.

Why designed this way?

These queries were designed to balance flexibility and performance. Allowing multiple values in a single query reduces the number of round-trips to the server. Limits on list size and query combinations prevent excessive resource use and keep response times low. This design reflects trade-offs between query power and system scalability.

Firestore Query Flow
─────────────────────────────
│ Client sends query with  │
│ 'in' or 'not-in' filter   │
├──────────────────────────┤
│ Firestore checks indexes  │
│ for each value in list    │
├──────────────────────────┤
│ Merges matching documents │
│ from all index lookups    │
├──────────────────────────┤
│ Returns filtered results  │
─────────────────────────────

Myth Busters - 4 Common Misconceptions

Quick: Do you think 'not-in' queries return documents where the field is missing? Commit to yes or no.

Common Belief: Not-in queries exclude only documents with listed values but include documents missing the field.

Reality: Not-in queries exclude documents with listed values and also exclude documents where the field is missing.

Why it matters: If you expect documents missing the field to appear, your app may show incomplete data or behave unexpectedly.

Quick: Can you combine two 'in' filters on different fields in one query? Commit to yes or no.

Common Belief: You can combine multiple 'in' filters on different fields in a single query.

Reality: Firebase does not allow combining multiple 'in' or 'not-in' filters in one query.

Why it matters: Trying to combine them causes query failures, blocking your app's functionality.

Quick: Do you think 'in' queries can accept more than 10 values? Commit to yes or no.

Common Belief: 'In' queries can accept any number of values in the list.

Reality: Firebase limits 'in' query lists to a maximum of 10 values.

Why it matters: Ignoring this limit causes errors and forces you to split queries, affecting performance.

Quick: Does 'in' query return documents where the field is missing? Commit to yes or no.

Common Belief: 'In' queries include documents where the field is missing.

Reality: 'In' queries only return documents where the field exists and matches one of the listed values.

Why it matters: Expecting missing fields to appear can cause bugs or missing data in your app.

Expert Zone

1

Using 'not-in' queries excludes documents missing the field, which can be surprising and requires explicit handling.

2

Combining 'in' queries with range filters requires careful index management to avoid performance degradation.

3

Large 'in' lists can cause query slowdowns; batching queries or restructuring data may be better.

When NOT to use

Avoid 'in' and 'not-in' queries when you need to filter on more than 10 values or combine multiple such filters. Instead, use multiple queries or redesign your data model with flags or categories to simplify filtering.

Production Patterns

In production, 'in' queries are often used to fetch user data for multiple user IDs at once, improving efficiency. 'Not-in' queries help exclude banned or inactive users. Developers monitor index usage and errors to maintain query performance and adjust data models accordingly.

Connections

SQL IN and NOT IN clauses

Similar pattern of filtering by multiple values in a list.

Understanding Firebase in/not-in queries is easier if you know SQL's IN/NOT IN, as they serve the same purpose in different systems.

Set theory

In and not-in queries correspond to set membership and set complement operations.

Recognizing these queries as set operations helps in reasoning about query results and combining filters logically.

Library book search

Filtering books by authors included or excluded from a list mirrors in/not-in queries.

This real-world example clarifies how filtering by multiple criteria works in databases.

Common Pitfalls

#1 Expecting 'not-in' queries to include documents missing the field.

Wrong approach:

firestore.collection('users').where('status', 'not-in', ['blocked'])
// Assumes documents without 'status' appear

Correct approach:

firestore.collection('users').where('status', 'not-in', ['blocked'])
// Plus additional query or logic to include missing fields if needed

Root cause:

Misunderstanding that 'not-in' excludes missing fields as well as listed values.

#2 Combining multiple 'in' filters in one query.

Wrong approach:

firestore.collection('users')
.where('status', 'in', ['active', 'pending'])
.where('role', 'in', ['admin', 'editor'])

Correct approach:

Run separate queries for each 'in' filter or redesign data to avoid multiple 'in' filters.

Root cause:

Not knowing Firebase disallows multiple 'in' filters in a single query.

#3 Using more than 10 values in an 'in' query list.

Wrong approach:

firestore.collection('users').where('status', 'in', ['a','b','c','d','e','f','g','h','i','j','k'])

Correct approach:

Split into multiple queries with up to 10 values each or redesign filtering.

Root cause:

Ignoring Firebase's limit on list size in 'in' queries.

Key Takeaways

In and not-in queries let you filter documents by checking if a field's value is inside or outside a list of values.

These queries improve efficiency by letting Firebase do server-side filtering for multiple values at once.

Firebase limits in/not-in queries to lists of 10 values and disallows combining multiple such filters in one query.

Not-in queries exclude documents missing the field, which can cause unexpected results if not handled.

Understanding these queries' rules and limits helps you build fast, correct, and scalable Firebase applications.

Practice

1.

What does the in query do in Firebase Firestore?

easy

A. Finds documents where a field is null

B. Finds documents where a field does not exist

C. Finds documents where a field matches exactly one value only

D. Finds documents where a field matches any value in a given list

Solution

1. Step 1: Understand the purpose of in query

   The in query checks if a field's value matches any value from a list of values.

2. Step 2: Compare with other options

   Null fields and non-existent fields describe different query types, while limiting to exactly one value is incorrect for in.

3. Final Answer:

   Finds documents where a field matches any value in a given list -> Option D

4. Quick Check:

   in query = matches any value in list [OK]

Hint: Remember: 'in' means match any value from a list [OK]

Common Mistakes:

• Confusing 'in' with 'not-in' query
• Thinking 'in' matches only one value
• Assuming 'in' checks for field existence

2.

Which of the following is the correct syntax for a not-in query in Firestore?

db.collection('users').where('age', ___, [18, 21, 25])

easy

A. 'notin'

B. 'not-in'

C. 'not_in'

D. 'nin'

Solution

1. Step 1: Recall Firestore query operators

   Firestore uses exact strings for operators; the correct operator for excluding values is 'not-in'.

2. Step 2: Check each option's validity

   'notin', 'not_in', and 'nin' are invalid syntax and will cause errors.

3. Final Answer:

   'not-in' -> Option B

4. Quick Check:

   Correct operator syntax = 'not-in' [OK]

Hint: Use exact operator strings: 'not-in' with hyphen [OK]

Common Mistakes:

• Using 'notin' or 'not_in' instead of 'not-in'
• Missing quotes around operator
• Using unsupported operators like 'nin'

3.

Given this Firestore query:

db.collection('products').where('category', 'in', ['books', 'games']).get()

Which documents will be returned?

medium

A. Documents where category is either 'books' or 'games'

B. Documents where category is not 'books' or 'games'

C. Documents where category is exactly 'booksgames'

D. All documents regardless of category

Solution

1. Step 1: Understand the 'in' query behavior

   The query filters documents where the 'category' field matches any value in the list ['books', 'games'].

2. Step 2: Analyze each option

   Documents where category is either 'books' or 'games' matches the query logic. Documents where category is not 'books' or 'games' is the opposite. Documents where category is exactly 'booksgames' is a concatenation, which is incorrect. All documents regardless of category ignores the filter.

3. Final Answer:

   Documents where category is either 'books' or 'games' -> Option A

4. Quick Check:

   'in' query returns matching values in list [OK]

Hint: Think: 'in' means any value inside the list [OK]

Common Mistakes:

• Confusing 'in' with 'not-in'
• Assuming concatenation of list values
• Ignoring the filter and expecting all documents

4.

What is wrong with this Firestore query?

db.collection('orders').where('status', 'not-in', ['shipped', 'delivered', 'pending', 'cancelled', 'new', 'processing', 'confirmed', 'refunded', 'returned', 'failed', 'on_hold']).get()

It throws an error. Why?

medium

A. The 'not-in' operator cannot be used with more than 10 values in the list

B. The field 'status' does not exist in the collection

C. The 'not-in' operator is not supported in Firestore

D. The list has more than 20 values, exceeding Firestore limits

Solution

1. Step 1: Recall Firestore limits on 'not-in' queries

   Firestore limits 'not-in' and 'in' queries to a maximum of 10 values in the list.

2. Step 2: Count values in the list

   The list has 11 values, exceeding the 10-value limit for 'not-in' queries.

3. Step 3: Consider other options

   The list has more than 20 values, exceeding Firestore limits is incorrect because the list has 11 values. The 'not-in' operator is not supported in Firestore is wrong because 'not-in' is supported. The field 'status' does not exist in the collection is unrelated to the error.

4. Final Answer:

   The 'not-in' operator cannot be used with more than 10 values in the list -> Option A

5. Quick Check:

   'not-in' max 10 values limit causes errors [OK]

Hint: Check list size: 'not-in' max 10 values allowed [OK]

Common Mistakes:

• Assuming 'not-in' supports unlimited list size
• Blaming field existence without checking
• Confusing 'not-in' support in Firestore

5.

You want to find all users whose role is neither 'admin' nor 'moderator' nor 'guest'. Which Firestore query is correct?

hard

A. db.collection('users').where('role', '!=', ['admin', 'moderator', 'guest']).get()

B. db.collection('users').where('role', 'in', ['admin', 'moderator', 'guest']).get()

C. db.collection('users').where('role', 'not-in', ['admin', 'moderator', 'guest']).get()

D. db.collection('users').where('role', 'not-in', 'admin,moderator,guest').get()

Solution

1. Step 1: Understand the requirement

   You want users whose role is NOT any of the listed roles. This means excluding those roles.

2. Step 2: Choose the correct operator and syntax

   The 'not-in' operator excludes documents with any value in the list. db.collection('users').where('role', 'not-in', ['admin', 'moderator', 'guest']).get() uses correct syntax with a list. db.collection('users').where('role', 'in', ['admin', 'moderator', 'guest']).get() includes only those roles, which is opposite. db.collection('users').where('role', '!=', ['admin', 'moderator', 'guest']).get() uses '!=' which does not accept lists. db.collection('users').where('role', 'not-in', 'admin,moderator,guest').get() uses a string instead of a list, which is invalid.

3. Final Answer:

   db.collection('users').where('role', 'not-in', ['admin', 'moderator', 'guest']).get() -> Option C

4. Quick Check:

   Use 'not-in' with list to exclude multiple values [OK]

Hint: Use 'not-in' with array to exclude multiple values [OK]

Common Mistakes:

• Using '!=' with a list instead of a single value
• Passing a string instead of an array for 'not-in'
• Using 'in' instead of 'not-in' for exclusion