mongodb - Find every document with the same field but different case

case-sensitive, duplicates, mongodb

Solution

Try this:

db.users.aggregate([
  { $match: {
    "username": { $exists: true }
  }},
  { $project: {
    "username": { "$toLower": [ "$username" ]}
  }},
  { $group: {
    _id: "$username",
    total: { $sum : 1 }
  }},
  { $match: {
    total: { $gte: 2 }
  }},
  { $sort: {
    total: -1
  }}
]);

This will find every user with a `username`, make the user names lower case, then group them by `username` and display the usernames that have a count greater than 1.

Problem

I'm having trouble with my database because I have documents representing my users with the field email with different cases (due to the ability to create ghost user, waiting for them to register). When the user registers, I use the lowered version of his email and overwrite the previous entry. The problem is that 'ghost' email has not been lowered. If Foo@bar.com ghost is created, Foo@bar.com register, he will be known as 'foo@bar.com', so Foo@bar.com will just pollute my database. I looking for a way in order to find the duplicates entries, remove the irrelevant one (by hand) before I push my fix about case. Ideas? Thank you!

Original source