Partial Indexes¶

On this page

New in version 3.2.

Partial indexes only index the documents in a collection that meet a specified filter expression. By indexing a subset of the documents in a collection, partial indexes have lower storage requirements and reduced performance costs for index creation and maintenance.

Create a Partial Index¶

To create a partial index, use the db.collection.createIndex() method with the partialFilterExpression option. The partialFilterExpression option accepts a document that specifies the filter condition using:

equality expressions (i.e. field: value or using the $eq operator),
$exists: true expression,
$gt, $gte, $lt, $lte expressions,
$type expressions,
$and operator at the top-level only

For example, the following operation creates a compound index that indexes only the documents with a rating field greater than 5.

db.restaurants.createIndex(
   { cuisine: 1, name: 1 },
   { partialFilterExpression: { rating: { $gt: 5 } } }
)

You can specify a partialFilterExpression option for all MongoDB index types.

Behavior¶

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.restaurants.createIndex(
   { cuisine: 1 },
   { partialFilterExpression: { rating: { $gt: 5 } } }
)

The following query can use the index since the query predicate includes the condition rating: { $gte: 8 } that matches a subset of documents matched by the index filter expression ratings: { $gt: 5 }:

db.restaurants.find( { cuisine: "Italian", rating: { $gte: 8 } } )

However, the following query cannot use the partial index on the cuisine field because using the index results in an incomplete result set. Specifically, the query predicate includes the condition rating: { $lt: 8 } while the index has the filter rating: { $gt: 5 }. That is, the query { cuisine: "Italian", rating: { $lt: 8 } } matches more documents (e.g. an Italian restaurant with a rating equal to 1) than are indexed.

db.restaurants.find( { cuisine: "Italian", rating: { $lt: 8 } } )

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.restaurants.find( { cuisine: "Italian" } )

Comparison with the `sparse` Index¶

Tip

Partial indexes represent a superset of the functionality offered by sparse indexes and should be preferred over sparse indexes.

Partial indexes offer a more expressive mechanism than Sparse Indexes indexes to specify which documents are indexed.

Sparse indexes select documents to index solely based on the existence of the indexed field, or for compound indexes, the existence of the indexed fields.

Partial indexes determine the index entries based on the specified filter. The filter can include fields other than the index keys and can specify conditions other than just an existence check. For example, a partial index can implement the same behavior as a sparse index:

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

This partial index supports the same queries as a sparse index on the name field.

However, a partial index can also specify filter expressions on fields other than the index key. For example, the following operation creates a partial index, where the index is on the name field but the filter expression is on the email field:

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

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

For example, the following query can use the index:

db.contacts.find( { name: "xyz", email: { $regex: /\.org$/ } } )

However, the following query cannot use the index:

db.contacts.find( { name: "xyz", email: { $exists: false } } )

Restrictions¶

In MongoDB, you cannot create multiple versions of an index that differ only in the options. As such, you cannot create multiple partial indexes that differ only by the filter expression.

You cannot specify both the partialFilterExpression option and the sparse option.

Earlier versions of MongoDB do not support partial indexes. For sharded clusters or replica sets, all nodes must be version 3.2.

_id indexes cannot be partial indexes.

Shard key indexes cannot be partial indexes.

Examples¶

Create a Partial Index On A Collection¶

Consider a collection restaurants containing documents that resemble the following

{
   "_id" : ObjectId("5641f6a7522545bc535b5dc9"),
   "address" : {
      "building" : "1007",
      "coord" : [
         -73.856077,
         40.848447
      ],
      "street" : "Morris Park Ave",
      "zipcode" : "10462"
   },
   "borough" : "Bronx",
   "cuisine" : "Bakery",
   "rating" : { "date" : ISODate("2014-03-03T00:00:00Z"),
                "grade" : "A",
                "score" : 2
              },
   "name" : "Morris Park Bake Shop",
   "restaurant_id" : "30075445"
}

You could add a partial index on the borough and cuisine fields choosing only to index documents where the rating.grade field is A:

db.restaurants.createIndex(
   { borough: 1, cuisine: 1 },
   { partialFilterExpression: { 'rating.grade': { $eq: "A" } } }
)

Then, the following query on the restaurants collection uses the partial index to return the restaurants in the Bronx with rating.grade equal to A:

db.restaurants.find( { borough: "Bronx", 'rating.grade': "A" } )

However, the following query cannot use the partial index because the query expression does not include the rating.grade field:

db.restaurants.find( { borough: "Bronx", cuisine: "Bakery" } )

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.

For example, a collection users contains the following documents:

{ "_id" : ObjectId("56424f1efa0358a27fa1f99a"), "username" : "david", "age" : 29 }
{ "_id" : ObjectId("56424f37fa0358a27fa1f99b"), "username" : "amanda", "age" : 35 }
{ "_id" : ObjectId("56424fe2fa0358a27fa1f99c"), "username" : "rajiv", "age" : 57 }

The following operation creates an index that specifies a unique constraint on the username field and a partial filter expression age: { $gte: 21 }.

db.users.createIndex(
   { username: 1 },
   { unique: true, partialFilterExpression: { age: { $gte: 21 } } }
)

The index prevents the insertion of the following documents since documents already exist with the specified usernames and the age fields are greater than 21:

db.users.insert( { username: "david", age: 27 } )
db.users.insert( { username: "amanda", age: 25 } )
db.users.insert( { username: "rajiv", age: 32 } )

However, the following documents with duplicate usernames are allowed since the unique constraint only applies to documents with age greater than or equal to 21.

db.users.insert( { username: "david", age: 20 } )
db.users.insert( { username: "amanda" } )
db.users.insert( { username: "rajiv", age: null } )

← Unique Indexes Case Insensitive Indexes →