mongodb indexing embedded fields (dot notation)

Question

Let's assume this is a document representing a customer.

{
    company_name: 'corporate ltd.',
    pocs: [
       {name: 'Paul', email: '[email protected]'},
       {name: 'Jessica', email: '[email protected]'}
    ]
}

I wanted to define a unique index for pocs.email So I issued the following command:

db.things.ensureIndex({"pocs.email": 1}, {unique: true})

The strange thing is that when trying to add another company with a poc having an email already exists in another company, mongo rejects that, respecting the unique index constraint.

that is, the following cannot exists:

{
    company_name: 'corporate ltd.',
    pocs: [
       {name: 'Paul', email: '[email protected]'},
       {name: 'Jessica', email: '[email protected]'}
    ]
},
{
    company_name: 'contoso llc',
    pocs: [
       {name: 'Paul', email: '[email protected]'},
    ]
}

Which is fine. However, having duplicate poc within the same doc is possible, e.g.

{
    company_name: 'corporate ltd.',
    pocs: [
       {name: 'Paul', email: '[email protected]'},
       {name: 'Paul', email: '[email protected]'},
       {name: 'Jessica', email: '[email protected]'}
    ]
},

see my cli commands sequence below:

> version()
version: 2.0.2
>
> use test
switched to db test
> db.test.ensureIndex({"poc.email": 1}, {unique: true})
> 
> db.test.insert({company: "contoso", poc: [{email: '[email protected]'}]})
> db.test.insert({company: "contoso", poc: [{email: '[email protected]'}]})
E11000 duplicate key error index: test.test.$poc.email_1  dup key: { : "[email protected]" }
> ({company: "contoso", poc: [{email: '[email protected]'}, {email: '[email protected]'}]})
> 
> 
> db.test.find()
{ "_id" : ObjectId("4f44949685926af0ecf9295d"), "company" : "contoso", "poc" : [ { "email" : "[email protected]" } ] }
{ "_id" : ObjectId("4f4494b885926af0ecf9295f"), "company" : "contoso", "poc" : [ { "email" : "[email protected]" }, { "email" : "[email protected]" } ] }

Moreover, this happens either at insert or at update.

> db.test.update({"_id" : ObjectId("4f44949685926af0ecf9295d")}, {$push: { poc: {email: '[email protected]'}}})
> db.test.find()
{ "_id" : ObjectId("4f4494b885926af0ecf9295f"), "company" : "contoso", "poc" : [ { "email" : "[email protected]" }, { "email" : "[email protected]" } ] }
{ "_id" : ObjectId("4f44949685926af0ecf9295d"), "company" : "contoso", "poc" : [        {       "email" : "[email protected]" },   {       "email" : "[email protected]" },   {       "email" : "[email protected]" } ] }
> 

Is this a bug or a by-design-feature I missed spotting in the documentation?

Answer 1

There is an open issue regarding the same problem unique indexes not enforced within array of single document . You can vote for it.

Also there is a nice workaround suggested by Kyle Banker in this similar post Unique indexes on embedded documents

Update

This is not only related to the embedded fields, we can reproduce the same for array fields too.

>db.uniqqueTest.insert({a:[1],x:1})
>db.uniqqueTest.createIndex({a:1}, {unique: true})
> db.uniqqueTest.find()
{ "_id" : ObjectId("4f44c6252434860b44986b02"), "a" : [ 1 ],"x":1 }

and it throws an error if we try to create a new document with the same value (correct behavior )

> db.uniqqueTest.insert({a:[1],x:3})
E11000 duplicate key error index: stack.uniqqueTest.$a_1  dup key: { : 1.0 }

But this works fine if we put the same value inside the array (no errors, silently accepts the duplicate value inside the array)

> db.uniqqueTest.insert({a:[2],x:2})
> db.uniqqueTest.update({x:2},{$push:{a:2}})
{ "_id" : ObjectId("4f44c65f2434860b44986b05"), "a" : [ 2, 2 ], "x" : 2 }

But not for this

> db.uniqqueTest.update({x:2},{$push:{a:1}])
E11000 duplicate key error index: stack.uniqqueTest.$a_1  dup key: { : 1.0 }