Finding duplicates in mongo-CodePudding

In the MongoDB I want to search for all duplicates on a certain field ownersEmailAddress with or without upper letters (any combination of letter).

In the mongodb compass when I known the value i can use

{ownersEmailAddress: /[email protected]/i}

but how can I do it without knowing specific value (search every ownersEmailAddress proeprty) with any case for duplicates?

I don't want to compare with value [email protected], just all duplicates on this field

CodePudding user response：

Query

group by lowercase email

I dont know what you need as expected output so i added

count of duplicates
all the duplicate docs
all the firstNames of duplicates
all the firstNames and emails of duplicates

Test code here

aggregate(
[{"$group":
  {"_id":{"$toLower":"$ownersEmailAddress"},
   "dupl-count":{"$sum":1},
   "dupl-docs":{"$push":"$$ROOT"},
   "dupl-firstNames":{"$push":"$firstName"},
   "dupl-firstNamesEmails":
   {"$push":
    {"firstName":"$firstName", "email":"$ownersEmailAddress"}}}}])

CodePudding user response：

Using aggregate you can do that

db.user.aggregate([  
    {$group: {
        _id: {ownersEmailAddress: "$ownersEmailAddress"},

        count: {$sum: 1}
        }
    }
]);