Partial Indexes

Query Coverage

MongoDB will not use the partial index for a query or sort operation if using the index results in an incomplete result set.

To use the partial index, a query must contain the filter expression (or a modified filter expression that specifies a subset of the filter expression) as part of its query condition.

For example, given the following index:

db.users.createIndex(
   { name: 1 },
   { partialFilterExpression: { password: { $exists: true } } }
)

The following query can use the index since the query predicate includes the condition password: { $exists: true } that matches documents matched by the index filter expression password: { $exists: true }:

db.users.find( { name: "Ned Stark", password: { $exists: true } } )

However, the following query cannot use the partial index on the name field because using the index results in an incomplete result set. Specifically, the query predicate includes the condition password: { $exists: false } while the index has the filter password: { $exists: true }. That is, the query { name: "Ned Stark", password: { $exists: false } } matches more documents (users without passwords) than the index covers.

db.users.find( { name: "Ned Stark", password: { $exists: false } } )

Similarly, the following query cannot use the partial index because the query predicate does not include the filter expression and using the index would return an incomplete result set.

db.users.find( { name: "Ned Stark" } )

Comparison with Sparse Indexes

Use partial indexes over sparse indexes if you want more precise control over which documents to index:

• Sparse indexes include or exclude documents solely based on the presence of the indexed field (or multiple fields, for sparse compound indexes).

• Partial indexes include or exclude documents based on the filter expression. The expression can include fields other than index keys, and can specify conditions other than a field existing.

For example, a partial index can implement the same behavior as a sparse index. This partial index supports the same queries as a sparse index on the name field:

db.users.createIndex(
   { name: 1 },
   { partialFilterExpression: { name: { $exists: true } } }
)

However, a partial index can also filter on fields other than the index key. For example, a partial index on the name field can check for the existence of the email field:

db.users.createIndex(
   { name: 1 },
   { partialFilterExpression: { email: { $exists: true } } }
)

For the query optimizer to choose this partial index, the query predicate must include a condition on the name field as well as a non-null match on the email field.

For example, the following query can use the index because it includes both a condition on the name field and a non-null match on the email field:

db.users.find( { name: "Ned Stark", email: { $regex: /gameofthron\.es$/ } } )

However, the following query cannot use the index because it includes a null match on the email field, which is not permitted by the filter expression { email: { $exists: true } }:

db.users.find( { name: "Ned Stark", email: { $exists: false } } )

Partial TTL Indexes

Partial indexes can also be TTL indexes. Partial TTL indexes match the specified filter expression and expire only those documents. For details, see Expire Documents with Filter Conditions.

Equivalent Indexes

Starting in MongoDB 7.3, you cannot create equivalent indexes, which are partial indexes with the same index keys and the same partial expressions that use a collation.

For databases in MongoDB 7.3 with existing equivalent indexes, the indexes are retained but only the first equivalent index is used in queries. This is the same behavior as MongoDB versions earlier than 7.3.

For example, you can't create two indexes that only differ in the text case in the partial filter expression.

Create a Partial Index On A Collection

The following example adds a partial index on the title and genres fields. The operation only indexes documents where the rating field is PG:

db.movies.createIndex(
   { title: 1, genres: 1 },
   { partialFilterExpression: { rated: "PG" } }
)

The following query on the movies collection uses the partial index to return the writers of the all movies titled "The Three Musketeers":

db.movies.find( { title: "The Three Musketeers", genres: ["Action", "Adventure", "Comedy"], rated: "PG" }, { writers: 1 } )

However, the following query cannot use the partial index because the query predicate does not include the rating filter:

db.movies.find( { genres: "Drama" }, { title: 1 } ).limit(5)

Partial Index with Unique Constraint

Partial indexes only index the documents in a collection that meet a specified filter expression. If you specify both the partialFilterExpression and a unique constraint, the unique constraint only applies to the documents that meet the filter expression. A partial index with a unique constraint does not prevent the insertion of documents that do not meet the unique constraint if the documents do not meet the filter criteria.

The following operation on the users collection creates an index that specifies a unique constraint on the email field and a partial filter expression password: { $exists: true }.

db.users.createIndex(
   { name: 1 },
   { name: "name_partial_unique_idx", unique: true, partialFilterExpression: { password: { $exists: true } } }
)

The index prevents the insertion of the documents that both an email addresses that already exist in the collection and an existing password field.

However, the following documents with duplicate email addresses are allowed since the unique constraint only applies to documents with a password field.

db.users.insertMany( [
   // This document does NOT have a password field, so it's NOT indexed
   // The unique constraint does not apply to it
   { name: "Jon Snow", email: "jon1@example.com" },
   
   // This document has a password field, so it IS indexed
   // The unique constraint applies to it
   { name: "Sansa Stark", email: "sansa@example.com", password: "password123" },
   
   // This document does NOT have a password field, so it's NOT indexed
   // We can insert it even though it has the same name as the first document
   // This demonstrates that the unique constraint only applies to indexed documents
   { name: "Jon Snow", email: "jon2@example.com" }
] )