Mongodb inline fields indexing (dot notation)

Question

Suppose this is a document representing a customer.

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

I would like 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 you try to add another company with a poc that has an email already exists in another company, mongo rejects this, observing the unique index constraint.

that is, the following cannot exist:

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

This is normal. However, duplication of poc within one document is possible, for example,

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

see the sequence of my cli commands 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: ' [e-mail] '}]}) > db.test.insert({company: "contoso", poc: [{email: ' [e-mail] '}]}) E11000 duplicate key error index: test.test.$poc.email_1 dup key: { : " [e-mail] " } > ({company: "contoso", poc: [{email: ' [e-mail] '}, {email: ' [e-mail] '}]}) > > > db.test.find() { "_id" : ObjectId("4f44949685926af0ecf9295d"), "company" : "contoso", "poc" : [ { "email" : " [e-mail] " } ] } { "_id" : ObjectId("4f4494b885926af0ecf9295f"), "company" : "contoso", "poc" : [ { "email" : " [e-mail] " }, { "email" : " [e-mail] " } ] } 

In addition, this happens either during insert or in update .

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

Is this a mistake or by design I skipped the definition in the documentation?

Answer 1

There is an open problem regarding the same problem, unique indexes not used in an array of a single document . You can vote for him.

There is also a good workaround suggested by Kyle Banker in this similar post. Unique indexes on embedded documents

Update

This is not only related to nested fields, we can also reproduce them for array fields.

 >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 gives 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 (without errors, silently accept a 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 }