Index w/ unique constraint not preventing duplicate values when using updateOne $push?

[Brandon E]

I have a mongo document like so:

{ "_id" : "testing", "member_ids" : [ { "id" : 1, "name": "Joe" }, { "id" : 2, "name": "Joseph" } ] }

I've created a unique index on the collection:

db.mydb.createIndex( { 'member_ids.id': 1 }, { unique: true } )

When I insert a new document with a member_ids.id value that matches either 1 or 2 - the two present in the example document above - I get the error I expect, telling me that a document with that value already exists.

However, when I use $push with an update the constraint does NOT work:

db.audience_members.updateOne( { '_id': 'testing' }, { $push: { member_ids: { 'id': 1, 'name': 'Joe' } } } )

Now it looks like:

{ "_id" : "testing", "member_ids" : [ { "id" : 1, "name": "Joe" }, { "id" : 2, "name": "Joseph" }, { "id" : 1, "name": "Joe" } ] }

Does anyone know why the updateOne() call with $push not being constrained by the unique index?

Thanks!

[Robert Cochran]

Hi,

I suggest carefully reading the documentation on unique indexes.

It also helps if you post your exact MongoDB version if you still have questions.

Please note, I am not an employee of MongoDB, Inc. I am just another list user trying to help you.

Thanks so much

Bob

[Stennie]

On Friday, 29 November 2019 08:38:48 UTC+11, Brandon E wrote:

I have a mongo document like so:

{ "_id" : "testing", "member_ids" : [ { "id" : 1, "name": "Joe" }, { "id" : 2, "name": "Joseph" } ] }

I've created a unique index on the collection:

db.mydb.createIndex( { 'member_ids.id': 1 }, { unique: true } )

When I insert a new document with a member_ids.id value that matches either 1 or 2 - the two present in the example document above - I get the error I expect, telling me that a document with that value already exists. 

However, when I use $push with an update the constraint does NOT work:

Hi Brandon,

The unique index constraint currently applies per document, so your outcome is as expected.

db.audience_members.updateOne( { '_id': 'testing' }, { $push: { member_ids: { 'id': 1, 'name': 'Joe' } } } )

Now it looks like:

{ "_id" : "testing", "member_ids" : [ { "id" : 1, "name": "Joe" }, { "id" : 2, "name": "Joseph" }, { "id" : 1, "name": "Joe" } ] }

If you are pushing identical subdocuments (as per this example) you could use $addToSet to avoid adding exact duplicates:

> db.mydb.updateOne( { '_id': 'testing' }, { $addToSet: { member_ids: { 'id': 1, 'name': 'Joe' } } } )
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 0 }

However, this would not help if id is a duplicate but any other values of the embedded document differ:

> db.mydb.updateOne( { '_id': 'testing' }, { $addToSet: { member_ids: { 'id': 1, 'name': 'Joey' } } } )
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

If your array only included scalar values instead of subdocuments, you could use the JSON Schema uniqueItems property.

Since your current schema wants to enforce uniqueness on arrays of subdocuments with a partial unique constraint (only the id field) is unique, you will either have to handle this constraint in application logic or reconsider your data model. For example, those subdocuments could be replaced with ids which are unique references that can be used to lookup the full document in another collection.

Regards,

Stennie