MongoDB Duplicate Documents even after adding unique key

Question

I have created a collection and added a unique key like this

db.user_services.createIndex({"uid":1 , "sid": 1},{unique:true,dropDups: true}) 

The collection looks something like this "user_services"

{  "_id" : ObjectId("55068b35f791c7f81000002d"),  "uid" : 15,  "sid" : 1,  "rate" : 5 }, {   "_id" : ObjectId("55068b35f791c7f81000002f"),  "uid" : 15,  "sid" : 1,  "rate" : 4 } 

Problem :

Am using php driver to insert documents with same uid and sid and it is getting inserted.

What I want

1. On Mongo Shell : Add unique key on uid and sid with no duplicate documents with the same uid and sid.
2. On PHP Side : having something like mysql "insert (value) on duplicate key update rate=rate+1". That is whenever I try to insert a document, it should be inserted if not there else it should update the rate field of the document

Answer 1

Congratulations, you appear to have found a bug. This only happens with MongoDB 3.0.0 in my testing, or at least is not present at MongoDB 2.6.6. Bug now recorded at SERVER-17599

NOTE: Not actually an "issue" but confirmed "by design". Dropped the option for version 3.0.0. Still listed in the documentation though.

The problem is that the index is not being created and errors when you attempt to create this on a collection with existing duplicates on the "compound key" fields. On the above, the index creation should yield this in the shell:

{     "createdCollectionAutomatically" : false,     "numIndexesBefore" : 1,     "errmsg" : "exception: E11000 duplicate key error dup key: { : 15.0, : 1.0 }",     "code" : 11000,     "ok" : 0 } 

When there are no duplicates present you can create the index as you are currently trying and it will be created.

So to work around this, first remove the duplicates with a procedure like this:

db.events.aggregate([     { "$group": {         "_id": { "uid": "$uid", "sid": "$sid" },         "dups": { "$push": "$_id" },         "count": { "$sum": 1 }     }},     { "$match": { "count": { "$gt": 1 } }} ]).forEach(function(doc) {     doc.dups.shift();     db.events.remove({ "_id": {"$in": doc.dups }}); });  db.events.createIndex({"uid":1 , "sid": 1},{unique:true}) 

Then further inserts containing duplicate data will not be inserted and the appropriate error will be recorded.

The final note here is that "dropDups" is/was not a very elegant solution for removing duplicate data. You really want something with more control as demonstrated above.

For the second part, rather than use .insert() use the .update() method. It has an "upsert" option

$collection->update(     array( "uid" => 1, "sid" => 1 ),     array( '$set' => $someData ),     array( 'upsert' => true ) ); 

So the "found" documents are "modified" and the documents not found are "inserted". Also see $setOnInsert for a way to only create certain data when the document is actually inserted and not when modified.

---

For your specific attempt, the correct syntax of .update() is three arguments. "query", "update" and "options":

$collection->update(     array( "uid" => 1, "sid" => 1 ),     array(         '$set' => array( "field" => "this" ),         '$inc' => array( "counter" => 1 ),         '$setOnInsert' => array( "newField" => "another" )    ),    array( "upsert" => true ) ); 

None of the update operations are allowed to "access the same path" as used in another update operation in that "update" document section.

Answer 2

I feel like the current most popular answer is a little too local and detailed for such an elementary MongoDB operation - removing duplicates from mongo by a key.

Removing duplicates by a key for mongo > 3.0 is simple. Just run this query, replacing yourDuplicateKey and assuming _id is your primary key (make sure you mongodump just in case):

db.yourCollection.aggregate([     { "$group": {         "_id": { "yourDuplicateKey": "$yourDuplicateKey" },         "dups": { "$push": "$_id" },         "count": { "$sum": 1 }     }},     { "$match": { "count": { "$gt": 1 } }} ]).forEach(function(doc) {     doc.dups.shift();     db.yourCollection.remove({ "_id": {"$in": doc.dups }}); });