CODEWITHSUNDEEP
mongodbaggregation-frameworkdatabase
Chris
Chris

Reputation: 3174

Find duplicate records in MongoDB

How would I find duplicate fields in a mongo collection.

I'd like to check if any of the "name" fields are duplicates.

{
    "name" : "ksqn291",
    "__v" : 0,
    "_id" : ObjectId("540f346c3e7fc1054ffa7086"),
    "channel" : "Sales"
}

Many thanks!

Upvotes: 244

Views: 289127

Answers (10)

Andreas Chatzivasileiadis
Andreas Chatzivasileiadis

Reputation: 659

Sometimes you want to find duplicates regardless the case, when you want to create a case insensitive index for instance. In this case you can use this aggregation pipeline

db.collection.aggregate([
  {'$group': {'_id': {'$toLower': '$name'}, 'count': { '$sum': 1 }, 'duplicates': { '$push': '$$ROOT' } } },
  {'$match': { 'count': { '$gt': 1 } } }
]);

Explanation:

  • group by name but first change the case to lower case and push the docs to the duplicates array.
  • match those groups having records greater than 1 (the duplicates).

Upvotes: 2

Andoctorey
Andoctorey

Reputation: 778

In case you need to see all duplicated rows:

db.collection.aggregate([
     {"$group" : { "_id": "$name", "count": { "$sum": 1 },"data": { "$push": "$$ROOT" }}},
     {"$unwind": "$data"},
     {"$match": {"_id" :{ "$ne" : null } , "count" : {"$gt": 1} } }, 
]);

Upvotes: 3

Denis Kanash
Denis Kanash

Reputation: 1

Search for duplicates in Compass Mongo db using $sortByCount
[screenshot]: https://i.sstatic.net/L85QV.png

Upvotes: 0

Tan Dat
Tan Dat

Reputation: 3117

Another option is to use $sortByCount stage.

db.collection.aggregate([
  { $sortByCount: '$name' }
]

This is the combination of $group & $sort.

The $sortByCount stage is equivalent to the following $group + $sort sequence:

    { $group: { _id: <expression>, count: { $sum: 1 } } },
    { $sort: { count: -1 } }

Upvotes: 23

Devflovv
Devflovv

Reputation: 475

enter image description here

this is how we can achieve this in mongoDB compass

Upvotes: 5

Julesezaar
Julesezaar

Reputation: 3396

If somebody is looking for a query for duplicates with an extra "$and" where clause, like "and where someOtherField is true"

The trick is to start with that other $match, because after the grouping you don't have all the data available anymore

// Do a first match before the grouping
{ $match: { "someOtherField": true }},
{ $group: {
    _id: { name: "$name" },
    count: { $sum: 1 }
}},
{ $match: { count: { $gte: 2 } }},

I searched for a very long time to find this notation, hope I can help somebody with the same problem

Upvotes: 1

anhlc
anhlc

Reputation: 14449

Use aggregation on name and get name with count > 1:

db.collection.aggregate([
    {"$group" : { "_id": "$name", "count": { "$sum": 1 } } },
    {"$match": {"_id" :{ "$ne" : null } , "count" : {"$gt": 1} } }, 
    {"$project": {"name" : "$_id", "_id" : 0} }
]);

To sort the results by most to least duplicates:

db.collection.aggregate([
    {"$group" : { "_id": "$name", "count": { "$sum": 1 } } },
    {"$match": {"_id" :{ "$ne" : null } , "count" : {"$gt": 1} } }, 
    {"$sort": {"count" : -1} },
    {"$project": {"name" : "$_id", "_id" : 0} }     
]);

To use with another column name than "name", change "$name" to "$column_name"

Upvotes: 400

Aman shrivastava
Aman shrivastava

Reputation: 164

db.getCollection('orders').aggregate([  
    {$group: { 
            _id: {name: "$name"},
            uniqueIds: {$addToSet: "$_id"},
            count: {$sum: 1}
        } 
    },
    {$match: { 
        count: {"$gt": 1}
        }
    }
])

First Group Query the group according to the fields.

Then we check the unique Id and count it, If count is greater then 1 then the field is duplicate in the entire collection so that thing is to be handle by $match query.

Upvotes: 14

Juan&#237;n
Juan&#237;n

Reputation: 851

The answer anhic gave can be very inefficient if you have a large database and the attribute name is present only in some of the documents.

To improve efficiency you can add a $match to the aggregation.

db.collection.aggregate(
    {"$match": {"name" :{ "$ne" : null } } }, 
    {"$group" : {"_id": "$name", "count": { "$sum": 1 } } },
    {"$match": {"count" : {"$gt": 1} } }, 
    {"$project": {"name" : "$_id", "_id" : 0} }
)

Upvotes: 16

BatScream
BatScream

Reputation: 19700

You can find the list of duplicate names using the following aggregate pipeline:

  • Group all the records having similar name.
  • Match those groups having records greater than 1.
  • Then group again to project all the duplicate names as an array.

The Code:

db.collection.aggregate([
{$group:{"_id":"$name","name":{$first:"$name"},"count":{$sum:1}}},
{$match:{"count":{$gt:1}}},
{$project:{"name":1,"_id":0}},
{$group:{"_id":null,"duplicateNames":{$push:"$name"}}},
{$project:{"_id":0,"duplicateNames":1}}
])

o/p:

{ "duplicateNames" : [ "ksqn291", "ksqn29123213Test" ] }

Upvotes: 49

Related Questions