MongoDB – Sparse Index

We can create indexes on a collection’s fields but what happens when we are creating an index and that key is not present more then one document in that collection.

Suppose we have a collection with following documents

1. {a:1, b:2, c:3}

2. {a:1, b:2}

3. {a:1, b:2}

If we create an index on c then for document 1 it will be ok but for document 2 and 3 c’s value will be

c=null (c= null) will be in both 2 and 3 documents, but unique means that a key will have unique value for each document so DB will not create a unique index for this collection .

The problem is that for unique index, it allows each key to be in index once, If a key is missing from some documents they all map to null.

The answer to this problem is Sparse Indexes

Sparse Indexes only index the documents that have a key set for the key being index or that they omit references to documents that do not include the indexed field. So it will index document 1 in above example but will not index document 2, 3.

We can create index on field c by adding “sparse:true” in command as follows:

db.users.ensureIndex( { c: 1 }, { sparse: true } )

Here is a small challenge query using unique sparse index.

Suppose we had the following documents in a collection called people with the following docs:

> db.people.find()
{ "_id" : ObjectId("50a464fb0a9dfcc4f19d6271"), "name" : "Andrew", "title" : "Jester" }
{ "_id" : ObjectId("50a4650c0a9dfcc4f19d6272"), "name" : "Dwight", "title" : "CEO" }
{ "_id" : ObjectId("50a465280a9dfcc4f19d6273"), "name" : "John" }

And there is an index defined as follows:

db.people.ensureIndex({title:1}, {sparse:1})

If you perform the following query, what do you get back, and why?
.

.
.
.
Answer is No documents, because the query uses the index and there are no documents with title:null in the index.