Partial Indexes

New in version 3.2.

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

Create a Partial Index

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

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

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

You can specify a partialFilterExpression option for all MongoDBindex types.

Behavior

Query Coverage

MongoDB will not use the partial index for a query or sort operation ifusing 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 filterexpression) as part of its query condition.

For example, given the following index:

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

The following query can use the index since the query predicateincludes the condition rating: { $gte: 8 } that matches a subset ofdocuments matched by the index filter expression rating: { $gt: 5}:

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

However, the following query cannot use the partial index on thecuisine field because using the index results in an incompleteresult set. Specifically, the query predicate includes the conditionrating: { $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 ratingequal to 1) than are indexed.

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

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

  1. db.restaurants.find( { cuisine: "Italian" } )

Comparison with the sparse Index

Tip

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

Partial indexes offer a more expressive mechanism thanSparse Indexes indexes to specify which documents areindexed.

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

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

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

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

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

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

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

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

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

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

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

Restrictions

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

You cannot specify both the partialFilterExpression option andthe sparse option.

MongoDB 3.0 or earlier do not support partial indexes. To use partialindexes, you must use MongoDB version 3.2 or higher. For shardedclusters or replica sets, all nodes must be version 3.2 or higher.

_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 resemblethe following

  1. {
  2.    "_id" : ObjectId("5641f6a7522545bc535b5dc9"),
  3.    "address" : {
  4.       "building" : "1007",
  5.       "coord" : [
  6.          -73.856077,
  7.          40.848447
  8.       ],
  9.       "street" : "Morris Park Ave",
  10.       "zipcode" : "10462"
  11.    },
  12.    "borough" : "Bronx",
  13.    "cuisine" : "Bakery",
  14.    "rating" : { "date" : ISODate("2014-03-03T00:00:00Z"),
  15.                 "grade" : "A",
  16.                 "score" : 2
  17.               },
  18.    "name" : "Morris Park Bake Shop",
  19.    "restaurant_id" : "30075445"
  20. }

You could add a partial index on the borough and cuisine fieldschoosing only to index documents where the rating.gradefield is A:

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

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

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

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

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

Partial Index with Unique Constraint

Partial indexes only index the documents in a collection that meet aspecified filter expression. If you specify both thepartialFilterExpression and a unique constraint, the unique constraint only applies to thedocuments that meet the filter expression. A partial index with aunique constraint does not prevent the insertion of documents that donot meet the unique constraint if the documents do not meet the filtercriteria.

For example, a collection users contains the following documents:

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

The following operation creates an index that specifies a uniqueconstraint on the username field and a partialfilter expression age: { $gte: 21 }.

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

The index prevents the insertion of the following documents sincedocuments already exist with the specified usernames and the agefields are greater than 21:

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

However, the following documents with duplicate usernames are allowedsince the unique constraint only applies to documents with agegreater than or equal to 21.

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