I have a collection with the field called "contact_id". In my collection I have duplicate registers with this key.
How can I remove duplicates, resulting in just one register?
I already tried:
db.PersonDuplicate.ensureIndex({"contact_id": 1}, {unique: true, dropDups: true})
But did not work, because the function dropDups
is no longer available in MongoDB 3.x
I'm using 3.2
We can also use an $out
stage to remove duplicates from a collection by replacing the content of the collection with only one occurrence per duplicate.
For instance, to only keep one element per value of x
:
// > db.collection.find()
// { "x" : "a", "y" : 27 }
// { "x" : "a", "y" : 4 }
// { "x" : "b", "y" : 12 }
db.collection.aggregate(
{ $group: { _id: "$x", onlyOne: { $first: "$$ROOT" } } },
{ $replaceWith: "$onlyOne" }, // prior to 4.2: { $replaceRoot: { newRoot: "$onlyOne" } }
{ $out: "collection" }
)
// > db.collection.find()
// { "x" : "a", "y" : 27 }
// { "x" : "b", "y" : 12 }
This:
$group
s documents by the field defining what a duplicate is (here x
) and accumulates grouped documents by only keeping one (the $first
found) and giving it the value $$ROOT
, which is the document itself. At the end of this stage, we have something like:
{ "_id" : "a", "onlyOne" : { "x" : "a", "y" : 27 } }
{ "_id" : "b", "onlyOne" : { "x" : "b", "y" : 12 } }
$replaceWith
all existing fields in the input document with the content of the onlyOne
field we've created in the $group
stage, in order to find the original format back. At the end of this stage, we have something like:
{ "x" : "a", "y" : 27 }
{ "x" : "b", "y" : 12 }
$replaceWith
is only available starting in Mongo 4.2
. With prior versions, we can use $replaceRoot
instead:
{ $replaceRoot: { newRoot: "$onlyOne" } }
$out
inserts the result of the aggregation pipeline in the same collection. Note that $out
conveniently replaces the content of the specified collection, making this solution possible.
this is a good pattern for mongod 3+ that also ensures that you will not run our of memory which can happen with really big collections. You can save this to a dedup.js file, customize it, and run it against your desired database with: mongo localhost:27017/YOURDB dedup.js
var duplicates = [];
db.runCommand(
{aggregate: "YOURCOLLECTION",
pipeline: [
{ $group: { _id: { DUPEFIELD: "$DUPEFIELD"}, dups: { "$addToSet": "$_id" }, count: { "$sum": 1 } }},
{ $match: { count: { "$gt": 1 }}}
],
allowDiskUse: true }
)
.result
.forEach(function(doc) {
doc.dups.shift();
doc.dups.forEach(function(dupId){ duplicates.push(dupId); })
})
printjson(duplicates); //optional print the list of duplicates to be removed
db.YOURCOLLECTION.remove({_id:{$in:duplicates}});
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With