Partial indexes with filter in MongoDB - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a partial index on the 'status' field where status is 'active'.

MongoDB

db.users.createIndex({ status: 1 }, { partialFilterExpression: { status: [1] } })

Drag options to blanks, or click blank then click option'

A"active"

Bactive

C1

Dtrue

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting quotes around string values in the filter expression.

Using a boolean or number instead of a string.

The partialFilterExpression requires the value to be a string, so we use "active" with quotes.

2fill in blank

medium

Complete the code to create a partial index on the 'age' field only for documents where age is greater than 18.

MongoDB

db.people.createIndex({ age: 1 }, { partialFilterExpression: { age: { [1]: 18 } } })

Drag options to blanks, or click blank then click option'

A$lt

B$eq

C$gt

D$ne

Attempts:

3 left

💡 Hint

Common Mistakes

Using $lt which means 'less than' instead of 'greater than'.

Using $eq which means 'equal to' instead of 'greater than'.

The operator $gt means 'greater than', so it filters documents where age is greater than 18.

3fill in blank

hard

Fix the error in the partial index creation by completing the filter expression correctly.

MongoDB

db.orders.createIndex({ total: 1 }, { partialFilterExpression: { total: { [1]: 100 } } })

Drag options to blanks, or click blank then click option'

A$gte

Bgte

C$greater

D=>

Attempts:

3 left

💡 Hint

Common Mistakes

Leaving out the dollar sign in the operator.

Using invalid operators like 'gte' or '=>'.

The correct MongoDB operator for 'greater than or equal' is $gte with a dollar sign.

4fill in blank

hard

Fill both blanks to create a partial index on 'category' where category is either 'books' or 'magazines'.

MongoDB

db.library.createIndex({ category: 1 }, { partialFilterExpression: { category: { [1]: [[2]] } } })

Drag options to blanks, or click blank then click option'

A$in

B"books", "magazines"

C"books", "movies"

D$nin

Attempts:

3 left

💡 Hint

Common Mistakes

Using $nin which means 'not in' instead of $in.

Including wrong values like 'movies' instead of 'magazines'.

The $in operator filters documents where the field matches any value in the array. The array should contain "books" and "magazines".

5fill in blank

hard

Fill both blanks to create a partial index on 'score' where score is between 50 and 100 inclusive.

MongoDB

db.scores.createIndex({ score: 1 }, { partialFilterExpression: { score: { [1]: 50, [2]: 100 } } })

Drag options to blanks, or click blank then click option'

A$gte

B$lte

C$gt

D$lt

Attempts:

3 left

💡 Hint

Common Mistakes

Using $gt or $lt which exclude boundary values.

Mixing up $gte and $lte operators.

Use $gte for 'greater than or equal to' 50, $lte for 'less than or equal to' 100.

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'