Partial indexes with filter in MongoDB - Mini Project: Build & Apply

Creating and Using Partial Indexes with Filters in MongoDB

📖 Scenario: You are managing a MongoDB collection that stores information about books in a library. Some books are currently available for borrowing, while others are checked out. You want to speed up queries that find only the available books by creating a partial index that includes only those books.

🎯 Goal: Build a MongoDB partial index on the books collection that indexes only the documents where the available field is true. This will help queries that search for available books run faster.

📋 What You'll Learn

Create a books collection with specific book documents

Add a filter condition variable for the partial index

Create a partial index on the available field with the filter

Verify the partial index creation command includes the filter correctly

💡 Why This Matters

🌍 Real World

Partial indexes help speed up queries by indexing only relevant documents, saving space and improving performance in large databases.

💼 Career

Database administrators and backend developers use partial indexes to optimize query speed and resource usage in real-world applications.

1

Create the books collection with sample documents

Create a books collection and insert these exact documents: { title: "The Hobbit", author: "J.R.R. Tolkien", available: true }, { title: "1984", author: "George Orwell", available: false }, and { title: "To Kill a Mockingbird", author: "Harper Lee", available: true }.

MongoDB

// Insert the specified documents into the books collection
// Your code here

Hint

Use db.books.insertMany() with an array of the three book objects exactly as given.

2

Define the filter condition for the partial index

Create a variable called filterCondition that holds the filter { available: true } to use for the partial index.

MongoDB

db.books.insertMany([
  { title: "The Hobbit", author: "J.R.R. Tolkien", available: true },
  { title: "1984", author: "George Orwell", available: false },
  { title: "To Kill a Mockingbird", author: "Harper Lee", available: true }
])

// Define the filterCondition variable with the filter for available books
// Your code here

Hint

Use const filterCondition = { available: true } to define the filter.

3

Create the partial index using the filter condition

Use db.books.createIndex() to create an index on the available field with the partial filter expression set to the filterCondition variable.

MongoDB

db.books.insertMany([
  { title: "The Hobbit", author: "J.R.R. Tolkien", available: true },
  { title: "1984", author: "George Orwell", available: false },
  { title: "To Kill a Mockingbird", author: "Harper Lee", available: true }
])

const filterCondition = { available: true }

// Create a partial index on the available field using filterCondition
// Your code here

Hint

Use db.books.createIndex({ available: 1 }, { partialFilterExpression: filterCondition }) to create the partial index.

4

Confirm the partial index creation command includes the filter

Add a command to list all indexes on the books collection using db.books.getIndexes() to verify the partial index was created with the filter.

MongoDB

db.books.insertMany([
  { title: "The Hobbit", author: "J.R.R. Tolkien", available: true },
  { title: "1984", author: "George Orwell", available: false },
  { title: "To Kill a Mockingbird", author: "Harper Lee", available: true }
])

const filterCondition = { available: true }

db.books.createIndex({ available: 1 }, { partialFilterExpression: filterCondition })

// List all indexes on the books collection to verify the partial index
// Your code here

Hint

Use db.books.getIndexes() to see all indexes on the collection.

Practice

1. What is the main purpose of a partial index in MongoDB?

easy

A. To index only documents that match a specific filter condition

B. To index all documents in a collection

C. To create a backup of the index

D. To speed up writes by disabling indexing

Solution

1. Step 1: Understand what partial indexes do

   Partial indexes only include documents that meet a filter condition, unlike normal indexes that include all documents.

2. Step 2: Compare options

   To index only documents that match a specific filter condition correctly describes this behavior. Options A, B, and D describe other unrelated or incorrect behaviors.

3. Final Answer:

   To index only documents that match a specific filter condition -> Option A

4. Quick Check:

   Partial index = filtered indexing [OK]

Hint: Partial indexes filter documents before indexing [OK]

Common Mistakes:

• Thinking partial indexes index all documents
• Confusing partial indexes with backups
• Assuming partial indexes disable indexing

2. Which of the following is the correct syntax to create a partial index on the status field where status equals 'active'?

easy

A. db.collection.createIndex({status: 1}, {filter: {status: 'active'}})

B. db.collection.createIndex({status: 1}, {partialFilterExpression: {status: 'active'}})

C. db.collection.createPartialIndex({status: 1}, {status: 'active'})

D. db.collection.createIndex({status: 1}, {partialIndex: {status: 'active'}})

Solution

1. Step 1: Recall the correct option name for partial indexes

   The option to specify a filter for partial indexes is partialFilterExpression.

2. Step 2: Match syntax with options

   db.collection.createIndex({status: 1}, {partialFilterExpression: {status: 'active'}}) uses partialFilterExpression correctly. Options B, C, and D use incorrect option names or methods.

3. Final Answer:

   db.collection.createIndex({status: 1}, {partialFilterExpression: {status: 'active'}}) -> Option B

4. Quick Check:

   Use partialFilterExpression for partial indexes [OK]

Hint: Use partialFilterExpression option to filter index documents [OK]

Common Mistakes:

• Using 'filter' instead of partialFilterExpression
• Trying to use createPartialIndex method (doesn't exist)
• Using partialIndex option instead of partialFilterExpression

3. Given the following partial index creation:

db.users.createIndex({age: 1}, {partialFilterExpression: {age: {$gte: 18}}})

Which documents will be included in this index?

medium

A. All documents regardless of age

B. Only documents where age is less than 18

C. Only documents where age is 18 or older

D. Documents where age is exactly 18

Solution

1. Step 1: Understand the filter condition

   The filter {age: {$gte: 18}} means age greater than or equal to 18.

2. Step 2: Determine which documents match

   Only documents with age 18 or older satisfy this condition and will be indexed.

3. Final Answer:

   Only documents where age is 18 or older -> Option C

4. Quick Check:

   Filter {$gte: 18} means age ≥ 18 [OK]

Hint: Partial index includes only documents matching filter [OK]

Common Mistakes:

• Thinking all documents are indexed
• Confusing $gte with $lt
• Assuming only exact matches are indexed

4. You tried to create a partial index with this command:

db.orders.createIndex({price: 1}, {partialFilterExpression: {price: {$gt: 100}}})

But you get an error. What is the most likely cause?

medium

A. The filter expression uses an unsupported operator

B. The field name in the index is missing

C. Partial indexes cannot use comparison operators

D. The partialFilterExpression option is misspelled or misplaced

Solution

1. Step 1: Check syntax correctness

   The syntax shown is correct if partialFilterExpression is spelled and placed properly.

2. Step 2: Identify common mistakes

   Errors often happen if partialFilterExpression is misspelled or placed outside the options object.

3. Final Answer:

   The partialFilterExpression option is misspelled or misplaced -> Option D

4. Quick Check:

   Check spelling and placement of partialFilterExpression [OK]

Hint: Check spelling and position of partialFilterExpression [OK]

Common Mistakes:

• Misspelling partialFilterExpression
• Placing filter outside options object
• Assuming comparison operators are unsupported

5. You want to speed up queries on a collection products where only documents with stock greater than zero are frequently queried. Which partial index creation command is best?

hard

A. db.products.createIndex({stock: 1}, {partialFilterExpression: {stock: {$gt: 0}}})

B. db.products.createIndex({stock: 1})

C. db.products.createIndex({stock: 1}, {partialFilterExpression: {stock: 0}})

D. db.products.createIndex({stock: 1}, {filter: {stock: {$gt: 0}}})

Solution

1. Step 1: Identify the filter condition for partial index

   The goal is to index only documents where stock is greater than zero.

2. Step 2: Match the correct syntax and filter

   db.products.createIndex({stock: 1}, {partialFilterExpression: {stock: {$gt: 0}}}) uses partialFilterExpression with {stock: {$gt: 0}}, which matches the requirement.

3. Step 3: Evaluate other options

   db.products.createIndex({stock: 1}) indexes all documents (not partial). db.products.createIndex({stock: 1}, {partialFilterExpression: {stock: 0}}) filters only stock equal to zero, which is wrong. db.products.createIndex({stock: 1}, {filter: {stock: {$gt: 0}}}) uses incorrect option name filter.

4. Final Answer:

   db.products.createIndex({stock: 1}, {partialFilterExpression: {stock: {$gt: 0}}}) -> Option A

5. Quick Check:

   Use partialFilterExpression with correct filter [OK]

Hint: Use partialFilterExpression with correct filter condition [OK]

Common Mistakes:

• Indexing all documents instead of partial
• Using wrong filter values
• Using incorrect option names like 'filter'