Error while setting up compound index

Question

I want to setup compound index on fb_id and ts in my mongoDB. So, I did:

PRIMARY> db.sessions.ensureIndex( { fb_id: 1, ts: 1 }, { unique:true } );

But I got following error:

E11000 duplicate key error index: tracking.sessions.$fb_id_1_ts_1  dup key: { : null, : null }

So I checked the indexes in this collection using db.sessions.getIndexes(), I got:

[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "ns" : "tracking.sessions",
        "name" : "_id_"
    }
]

It doesn't look like a duplicate key to me. What am I doing wrong here?

Answer 1

MongoDB tells you that you have several documents (more than one) with the same fb_id and ts values, null values. In other words, there are documents without fb_id and ts fields. So, it violates unique constraint across the collection.

As a workaround, you should take a look at sparse indexes. Quote from docs:

Sparse indexes only contain entries for documents that have the indexed field. Any document that is missing the field is not indexed. The index is “sparse” because of the missing documents when values are missing.

See also:

• sparse indexes and null values in mongo
• why DuplicateKeyError: E11000 duplicate key error index: test.test.$notification_1 dup key: { : null }

Hope that helps.