mongoDB : Sparse Indexes

Let’s say I had employee collection with five documents.

> db.employee.find()
{ "_id" : 1, "firstName" : "Hari Krishna", "lastName" : "Gurram", "mailId" : "abcabc@abc.com" }
{ "_id" : 2, "firstName" : "Rama Krishna", "lastName" : "Gurram", "mailId" : "defabc@abc.com" }
{ "_id" : 3, "firstName" : "Murali Krishna", "lastName" : "Bachu", "mailId" : "abcdef@abc.com" }
{ "_id" : 4, "firstName" : "Gopi", "lastName" : "Battu" }
{ "_id" : 5, "firstName" : "Joel", "lastName" : "Chelli" }
>

I want to create unique index on field “mailID”.

> db.employee.ensureIndex({"mailId":1}, {"unique":true})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "ok" : 0,
        "errmsg" : "E11000 duplicate key error index: test.employee.$mailId_1  dup key: { : null }",
        "code" : 11000
}
>

I got “duplicate key error”, it is because documents 4 and 5 don’t have mailId associated with them, so by default these are mapped to null. So there is no uniqueness between documents 4 and 5.

How to solve this problem?

You can create sparse index. 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.

> db.employee.ensureIndex({"mailId":1}, {"unique":true, "sparse" : true})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}

If you query the collection using sparse index, then you won’t get the documents that don’t have mailId field.

> db.employee.find().hint({mailId:1})
{ "_id" : 1, "firstName" : "Hari Krishna", "lastName" : "Gurram", "mailId" : "abcabc@abc.com" }
{ "_id" : 3, "firstName" : "Murali Krishna", "lastName" : "Bachu", "mailId" : "abcdef@abc.com" }
{ "_id" : 2, "firstName" : "Rama Krishna", "lastName" : "Gurram", "mailId" : "defabc@abc.com" }
>

hint() method force mongoDB to use specified index.

Prevoius                                                 Next                                                 Home