how to index a username in mongo with case-insensitively?

Question

I am coding a webservice where users can pick a username that has to be case-insensitive unique. However I want to allow them to use a case-sensitive version of their username.

What is the best way to check at insert that the username doesn't have a case-insensitive duplicate? I currently see 2 ways of doing this:

• storing both a lowercase version and another version with the case entered by the user, and indexing only the lowercase version
• store only the version with the case entered by the user and lowercase it for comparison, which defeats the purpose of the index I suppose

Is there a better way?

Answer 1

Storing the originally-entered username and a canonical version (lowercase for your app) is perfectly reasonable. Just ensure that the canonical field is updated in your model whenever the username is set, and check for constraint violations via the canonical field's unique index.

Another scenario where this solution (original and canonical field) makes sense is articles, where the same title might be re-used but the slug (for URL's) must be unique.

Answer 2

MongoDB 3.4 now includes the ability to make a true case-insensitive index. It is made by specifying a collation with a strength of 2.

Probably the easiest way to do it is to set a collation on the database itself. Then all queries inherit that collation and will use it:

db.createCollection("cities", { collation: { locale: 'en_US', strength: 2 } } )
db.cities.createIndex( { city: 1 } ) // inherits the default collation
db.cities.find({city:"new york"}) //inherits default collation

Or you can do it on an index by index basis:

db.myCollection.createIndex({city: 1}, {collation: {locale: "en", strength: 2}});

And use it like this:

db.myCollection.find({city: "new york"}).collation({locale: "en", strength: 2});

For more info: https://jira.mongodb.org/browse/SERVER-90