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.
Important
Partial indexes can function as sparse indexes, but also support filter expressions for conditions beyond whether a field exists. Use a partial index for greater control if you need precise filtering.
Create a Sparse Index
To create a sparse index, use the db.collection.createIndex()
method with the sparse option set to true.
For example, the following operation in mongosh creates a
sparse index on the plot field of the movies collection:
db.movies.createIndex( { "plot": 1 }, { sparse: true } )
The index does not index documents that do not include the plot
field.
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.
Behavior
Sparse Index and Incomplete Results
If a sparse index would result in an incomplete result set for queries
and sort operations, MongoDB will not use that index unless a
hint() explicitly specifies the index.
For example, the query { plot: { $exists: false } } will not use a
sparse index on the plot field unless explicitly hinted. See
Sparse Index On A Collection Cannot Return Complete Results for an example that details the
behavior.
If you include a hint() that specifies a
sparse index when you perform a
count() of all documents in a collection (i.e. with
an empty query predicate), the sparse index is used even if the sparse
index results in an incorrect count.
For example, create a sparse index on the rated field on the movies
collection.
db.movies.createIndex( { rated: 1 }, { sparse: true } )
If you count the number of documents in the movies collection and include a
hint that specifies that sparse index, the operation returns only the documents
that contain the rated field.
db.movies.countDocuments( {}, { hint: { rated: 1 } } )
To obtain the correct count of the number of documents in the movies
collection, do not hint() with a sparse index when performing a count of all documents in a collection.
db.movies.countDocuments()
Indexes that are Sparse by Default
The following index types are always sparse:
Sparse Compound Indexes
Compound indexes can contain different types of sparse indexes. The combination of index types determines how the compound index matches documents.
This table summarizes the behavior of a compound index that contains different types of sparse indexes:
Compound Index Components | Compound Index Behavior |
|---|---|
Ascending indexes Descending indexes | Only indexes documents that contain a value for at least one of the keys. |
Only indexes a document when it contains a value for one of
the | |
Only indexes a document when it matches one of the |
Sparse and Unique Properties
An index that is both sparse and unique prevents a collection from having documents with duplicate values for a field but allows multiple documents that omit the key.
Examples
Create a Sparse Index On A Collection
The following example creates a sparse index on the field password:
db.users.createIndex( { password: 1 } , { sparse: true } )
Then, the following query on the users collection uses the sparse
index to return the documents that have the password field:
db.users.find( { password: { $exists: true } } ).sort({ password: 1 }).limit(5)
If a user does not contain the
password field, the query does not return that user.
Sparse Index On A Collection Cannot Return Complete Results
Consider the movies collection where some documents do not have a plot
field.
The following example creates a sparse index on the field plot:
db.movies.createIndex( { "plot": 1 }, { sparse: true } )
Consider the following query to return all documents in the movies
collection, sorted by the plot field:
db.movies.find().sort( { plot: -1 } )
Even though the sort is by the indexed field, if some documents in the
movies collection do not have a plot field, MongoDB does not select
the sparse index to fulfill the query in order to return complete results.
To use the sparse index, explicitly specify the index with
hint():
db.movies.find().sort( { plot: -1 } ).hint( { plot: 1 } ).limit(5)
This query only returns documents in the movies collection that contain the
plot field.
Sparse Index with Unique Constraint
The following operation creates an index with a unique constraint and sparse filter on the password field in the
users:
db.users.createIndex( { password: 1 } , { sparse: true, unique: true } )
This index would permit the insertion of documents that had unique
values for the password field or did not include a password field.
As such, given the existing documents in the users collection, the
index permits the following insert operations:
db.users.insertMany( [ { "name": "Jon Snow", "email": "jon@gameofthron.es", "password": "$2b$12$newHashedPassword1234567890ABC" }, { "name": "Sansa Stark", "email": "sansa@gameofthron.es", "password": "$2b$12$anotherNewPassword1234567890DEF" }, { "name": "Bran Stark", "email": "bran@gameofthron.es" } ] )
However, the index would not permit the addition of the documents that contain email addresses that already exist in the collection.
Sparse and Non-Sparse Unique Indexes
Starting in MongoDB 5.0, unique sparse and unique non-sparse indexes with the same key pattern can exist on a single collection.
Unique and Sparse Index Creation
This example creates multiple indexes with the same key pattern and
different sparse options:
db.users.createIndex( { password : 1 }, { name: "unique_index", unique: true } )
db.users.createIndex( { password : 1 }, { name: "unique_sparse_index", unique: true, sparse: true } )
Basic and Sparse Index Creation
You can also create basic indexes with the same key pattern with and without the sparse option:
db.users.createIndex( { password : 1 }, { name: "sparse_index", sparse: true } )
db.users.createIndex( { password : 1 }, { name: "basic_index" } )