OPTIONS

Sparse Indexes

Sparse indexes only contain entries for documents that have the indexed field, even if the index field contains a null value. The index skips over any document that is missing the indexed field. The index is “sparse” because it does not include all documents of a collection. By contrast, non-sparse indexes contain all documents in a collection, storing null values for those documents that do not contain the indexed field.

The following example in the mongo shell creates a sparse index on the xmpp_id field of the addresses collection:

db.addresses.ensureIndex( { "xmpp_id": 1 }, { sparse: true } )

By default, sparse is false on MongoDB indexes.

Warning

Using these indexes will sometimes result in incomplete results when filtering or sorting results, because sparse indexes are not complete for all documents in a collection.

Note

Do not confuse sparse indexes in MongoDB with block-level indexes in other databases. Think of them as dense indexes with a specific filter.

Tip

You can specify a sparse and unique index, that rejects documents that have duplicate values for a field, but allows multiple documents that omit that key.

Examples

Sparse Index On A Collection Can Result In Incomplete Results

Consider a collection scores that contains the following documents:

{ "_id" : ObjectId("523b6e32fb408eea0eec2647"), "userid" : "newbie" }
{ "_id" : ObjectId("523b6e61fb408eea0eec2648"), "userid" : "abby", "score" : 82 }
{ "_id" : ObjectId("523b6e6ffb408eea0eec2649"), "userid" : "nina", "score" : 90 }

The collection has a sparse index on the field score:

db.scores.ensureIndex( { score: 1 } , { sparse: true } )

Then, the following query to return all documents in the scores collection sorted by the score field gives incomplete results:

db.scores.find().sort( { score: -1 } )

Because the document for the userid "newbie" does not contain the score field, the query, which uses the sparse index, will return incomplete results that omit that document:

{ "_id" : ObjectId("523b6e6ffb408eea0eec2649"), "userid" : "nina", "score" : 90 }
{ "_id" : ObjectId("523b6e61fb408eea0eec2648"), "userid" : "abby", "score" : 82 }

Sparse Index with Unique Constraint

Consider a collection scores that contains the following documents:

{ "_id" : ObjectId("523b6e32fb408eea0eec2647"), "userid" : "newbie" }
{ "_id" : ObjectId("523b6e61fb408eea0eec2648"), "userid" : "abby", "score" : 82 }
{ "_id" : ObjectId("523b6e6ffb408eea0eec2649"), "userid" : "nina", "score" : 90 }

You could create an index with a unique constraint and sparse filter on the score field using the following operation:

db.scores.ensureIndex( { score: 1 } , { sparse: true, unique: true } )

This index would permit inserting documents that had unique values for the score field or did not include a score field. Consider the following insert operation:

db.scores.insert( { "userid": "PWWfO8lFs1", "score": 43 } )
db.scores.insert( { "userid": "XlSOX66gEy", "score": 34 } )
db.scores.insert( { "userid": "nuZHu2tcRm" } )
db.scores.insert( { "userid": "HIGvEZfdc5" } )

However, this index would not permit adding the following documents:

db.scores.insert( { "userid": "PWWfO8lFs1", "score": 82 } )
db.scores.insert( { "userid": "XlSOX66gEy", "score": 90 } )