Mongodb unique sparse index

Question

I have created a sparse and unique index on my mongodb collection.

var Account = new Schema({
                email: { type: String, index: {unique: true, sparse: true} },
                        ....

It has been created correctly:

{ "ns" : "MyDB.accounts", "key" : { "email" : 1 }, "name" : "email_1", "unique" : true, "sparse" : true, "background" : true, "safe" : null }

But if I insert a second document with a key not set I receive this error:

{ [MongoError: E11000 duplicate key error index: MyDB.accounts.$email_1  dup key: { : null }]
  name: 'MongoError',
  err: 'E11000 duplicate key error index: MyDB.accounts.$email_1  dup key: { : null }',
  code: 11000,
  n: 0,
  ok: 1 }

Any hints?

Solution

I just had this issue too. I wanted a value to either be null or be unique. So, I set both the unique and the sparse flags:

var UserSchema = new Schema({
  // ...
  email: {type: String, default: null, trim: true, unique: true, sparse: true},
  // ...
});

And, I made sure that the database had actually created the index correctly with db.users.getIndexes();

{
  "v" : 1,
  "key" : {
    "email" : 1
  },
  "unique" : true,
  "ns" : "test.users",
  "name" : "email_1",
  "sparse" : true,
  "background" : true,
  "safe" : null
},

(So, this is not the same as the issue here: mongo _id field duplicate key error)

My mistake was setting the default value to null. In some sense, Mongoose counts an explicit null as a value that must be unique. If the field is never defined (or undefined) then it is not enforced to be unique.

email: {type: String, trim: true, unique: true, sparse: true},

So, if you are having this issue too, make sure you're not setting default values, and make sure you're not setting the values to null anywhere else in your code either. Instead, if you need to set it explicitly, set it to undefined (or a unique value).

Comments

[elhefe]

$unseting the field works as well if you're transitioning from a state where the field needs to be unique to a state where you don't care about the field.

[Arthur Costa]

Important to note that you need to re-start the collection to make it work