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

Question

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 [email protected] ghost is created, [email protected] register, he will be known as '[email protected]', so [email protected] 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!

Answer 1

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.