Find all duplicate documents in a MongoDB collection by a key field

by

The accepted answer is terribly slow on large collections, and doesn’t return the _ids of the duplicate records.

Aggregation is much faster and can return the _ids:

db.collection.aggregate([
  { $group: {
    _id: { name: "$name" },   // replace `name` here twice
    uniqueIds: { $addToSet: "$_id" },
    count: { $sum: 1 } 
  } }, 
  { $match: { 
    count: { $gte: 2 } 
  } },
  { $sort : { count : -1} },
  { $limit : 10 }
]);

In the first stage of the aggregation pipeline, the $group
operator aggregates documents by the name field and stores in uniqueIds each _id value of the grouped records.
The $sum operator adds up the values of the fields passed to it, in this case the constant 1 – thereby counting the number of grouped records into the count field.

In the second stage of the pipeline, we use $match
to filter documents with a count of at least 2, i.e. duplicates.

Then, we sort the most frequent duplicates first, and limit the results to the top 10.

This query will output up to $limit records with duplicate names, along with their _ids. For example:

{
  "_id" : {
    "name" : "Toothpick"
},
  "uniqueIds" : [
    "xzuzJd2qatfJCSvkN",
    "9bpewBsKbrGBQexv4",
    "fi3Gscg9M64BQdArv",
  ],
  "count" : 3
},
{
  "_id" : {
    "name" : "Broom"
  },
  "uniqueIds" : [
    "3vwny3YEj2qBsmmhA",
    "gJeWGcuX6Wk69oFYD"
  ],
  "count" : 2
}

More Related Contents:

  1. Does MongoDB’s $in clause guarantee order
  2. Check if every element in array matches condition
  3. MongoDB aggregation comparison: group(), $group and MapReduce
  4. Remove Duplicates from MongoDB
  5. Retrieve only the queried element in an object array in MongoDB collection
  6. Update MongoDB field using value of another field
  7. Mongodb sort inner array
  8. Return only matched sub-document elements within a nested array
  9. Converting string to date in mongodb
  10. Fastest way to remove duplicate documents in mongodb
  11. Using map/reduce for mapping the properties in a collection
  12. Is Mongodb Aggregation framework faster than map/reduce?
  13. Average Aggregation Queries in Meteor
  14. MongoDB Full and Partial Text Search
  15. Mongodb Join on _id field from String to ObjectId
  16. $unwind empty array
  17. Mongo sort on a calculated condition
  18. MongoDB: Calculate dwell time between every status value change
  19. MongoDB: how to parse date in 3.6 mongoDb version?
  20. Using stored JavaScript functions in the Aggregation pipeline, MapReduce or runCommand
  21. MongoDB – The argument to $size must be an Array, but was of type: EOO / missing
  22. How to flatten a subdocument into root level in MongoDB?
  23. How to access overall document count during arithmetic aggregation expression
  24. How to deal with the timezone issue when storing dates in utc using mongod?
  25. Sum in nested document MongoDB
  26. Export mongodb aggregation framework result to a new collection
  27. mongodb aggregate query isn’t returning proper sum on using $sum
  28. MongoDB aggregate by field exists
  29. MongoDB {aggregation $match} vs {find} speed
  30. MongoDB aggregate within daily grouping [duplicate]

Leave a Comment