CODEWITHSUNDEEP
mongodbindexingmongodb-query
Maya Levy
Maya Levy

Reputation: 403

How can I specify uniqueness on multiple field in mongo, NOT combined?

I have the following JSON schema in MongoDB:

{"email": "[email protected]", "second_email": "[email protected]"}

How can I enforce that both fields will be unique separately AND also be unique between them.

i.e the following document will not be valid:

{"email":"[email protected]", "second_email":"[email protected]"}

Because [email protected] is already exists in another document in the other field.

Upvotes: 6

Views: 2658

Answers (3)

het
het

Reputation: 1109

db.collection.createIndex( { "mails.email": 1, "mails.second_email": 1 }, { unique: true } )

db.collection.insert( { _id: 3, mails: [ { email: "[email protected]", second_email: "[email protected]" } ] } )

now you created a "email - second email" pair combination for enforce uniqueness this two field.

Also, if you use bulk option you can set ordered as false to continue with remaining inserts when one fails.. insertMany({},{ordered: false})

Upvotes: 0

Sede
Sede

Reputation: 61225

You need to create a a unique index on each field to enforce uniqueness for the fields. 

db.collection.createIndex( { "email": 1 }, { "unique": true } )
db.collection.createIndex( { "second_email": 1 }, { "unique": true } )

That being said, MongoDB doesn't not provides a way to enforce uniqueness for two fields in the same documents. This is something you will need to do in your application using an if/else statement.

Another option as shown in this answer here is to use an indexed array field if you do not want to call the createIndex() method multiple times. But you still need to use logical condition processing if you don't want duplicate value in the array.

Upvotes: 0

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230316

Off the top of my head, no database can do this (use another column/field as source data for uniqueness constraint). You will need to do some reshaping of data to achieve this. The easiest way is a unique constraint on an array field.

> db.foo.createIndex({ emails: 1 }, { unique: true } )

> db.foo.insert({ emails: ['[email protected]', '[email protected]'] })
WriteResult({ "nInserted" : 1 })
> db.foo.insert({ emails: ['[email protected]', '[email protected]'] })
WriteResult({
    "nInserted" : 0,
    "writeError" : {
        "code" : 11000,
        "errmsg" : "E11000 duplicate key error index: test.foo.$emails_1 dup key: { : \"[email protected]\" }"
    }
})

Now, depending on your app logic, this emails array can even replace your original two fields. Or not. Up to you. If not, you'll need insert both the original fields and duplicate them in this array for the uniqueness check.

Upvotes: 6

Related Questions