CODEWITHSUNDEEP
mongodbmongoose
Patrick Estabrook
Patrick Estabrook

Reputation: 125

Add field that is unique index to collection in MongoDB

I'm trying to add a username field to documents in a 'users' collection, and I'd like it to be a unique index. (So far, we've been using email addresses for login but we'd like to add a username field as well.) However, running db.users.ensureIndex({username:1},{unique:true}) fails because mongo considers all the unset usernames to be duplicates and therefore not unique. Anybody know how to get around this?

Show the current users and username if they have one:

> db.users.find({},{_id:0,display_name:1,username:1})
    { "display_name" : "james" }
    { "display_name" : "sammy", "username" : "sammy" }
    { "display_name" : "patrick" }

Attempt to make the 'username' field a unique index:

> db.users.ensureIndex({username:1},{unique:true})
    {
        "err" : "E11000 duplicate key error index: blend-db1.users.$username_1  dup key: { : null }",
        "code" : 11000,
        "n" : 0,
        "connectionId" : 272,
        "ok" : 1
    }

It doesn't work because both james and sammy have username:null.

Let's set patrick's username to 'patrick' to eliminate the duplicate null value.

> db.users.update({display_name: 'patrick'}, { $set: {username: 'patrick'}});
> db.users.ensureIndex({username:1},{unique:true})
> db.users.getIndexes()
    [
        {
            "v" : 1,
            "key" : {
                "_id" : 1
            },
            "ns" : "blend-db1.users",
            "name" : "_id_"
        },
        {
            "v" : 1,
            "key" : {
                "username" : 1
            },
            "unique" : true,
            "ns" : "blend-db1.users",
            "name" : "username_1"
        }
    ]

Now it works!

To clarify the question, what I'd like is to be able to make username a unique index without having to worry about all the documents that have username still set to null.

Upvotes: 2

Views: 3252

Answers (1)

robertklep
robertklep

Reputation: 203286

Try creating a unique sparse index:

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

As per the docs:

You can combine the sparse index option with the unique indexes option so that mongod will reject documents that have duplicate values for a field, but that ignore documents that do not have the key.

Although this only works for documents which don't have the field, as opposed to documents that do have the field, but where the field has a null value.

Upvotes: 2

Related Questions