How do you get around missing values in a unique index using mongo db?

Question

The mongo documentation states that "When a document is saved to a collection with unique indexes, any missing indexed keys will be inserted with null values. Thus, it won't be possible to insert multiple documents missing the same indexed key."

So is it impossible to create a unique index on an optional field? Should I create a compound index with say a userId as well to solve this? In my specific case I have a user collection that has an optional embedded oauth object. e.g.

>db.users.ensureIndex( { "name":1, "oauthConnections.provider" : 1, "oauthConnections.providerId" : 1 } );

My sample user

{  name: "Bob"
   ,pwd: "myPwd"
   ,oauthConnections [
      {
         "provider":"Facebook",
         "providerId" : "12345",
         "key":"blah"
      }
     ,{
         "provider":"Twitter",
         "providerId" : "67890",
         "key":"foo"
      }
     ]
}

Answer 1

I believe that this is possible: You can have an index that is sparse and unique. This way, non-existant values never make it to the index, hence they can't be duplicate.

Caveat: This is not possible with compound indexes. I'm not quite sure about your question. Your citing a part of the documentation that concerns compound indexes -- there, missing values will be inserted, but from your question I guess you're not looking for a solution w/ compound indexes?

Here's a sample:

> db.Test.insert({"myId" : "1234", "string": "foo"});
> show collections
Test
system.indexes
>
> db.Test.find();
{ "_id" : ObjectId("4e56e5260c191958ad9c7cb1"), "myId" : "1234", "string" : "foo" }
>

> db.Test.ensureIndex({"myId" : 1}, {sparse: true, unique: true});
>
> db.Test.insert({"myId" : "1234", "string": "Bla"});
E11000 duplicate key error index: test.Test.$myId_1  dup key: { : "1234" }
>
> db.Test.insert({"string": "Foo"});
> db.Test.insert({"string": "Bar"});
> db.Test.find();
{ "_id" : ObjectId("4e56e5260c191958ad9c7cb1"), "myId" : "1234", "string" : "foo" }
{ "_id" : ObjectId("4e56e5c30c191958ad9c7cb4"), "string" : "Foo" }
{ "_id" : ObjectId("4e56e5c70c191958ad9c7cb5"), "string" : "Bar" }

Also note that compound indexes can't be sparse