MongoDB Collation and Case-Insensitive Queries

Sort a list of customer names in MongoDB without thinking about it, and the result looks broken to anyone outside engineering: every capitalised name comes before every lowercase one, and “Émile” ends up after “zoë”. Search for ann@example.com and you miss the account registered as Ann@Example.com. Both are the same problem. By default MongoDB compares strings byte by byte, and human expectations about text are not byte order.

Collation is how you tell MongoDB which language rules to compare strings with. It affects matching, sorting, range queries and unique indexes, and it has to agree with your indexes or performance falls off a cliff.

Binary order versus language order

MongoDB uses ICU, the same library behind JavaScript’s Intl.Collator, for its collations. That means Node can show you the orderings you’ll get. Here’s one list sorted three ways:

binary : Banana, Olsen, Zoe, apple, emile, item10, item2, zoë, Émile, Ölund
en     : apple, Banana, emile, Émile, item10, item2, Olsen, Ölund, Zoe, zoë
en+num : item1, item2, item10

The first line is what you get with no collation (MongoDB calls it the simple locale): all uppercase ASCII letters precede lowercase ones, and anything outside ASCII sorts after z. The second is English rules: case and accents only break ties between otherwise equal letters. The third uses numericOrdering: true, which compares digit runs as numbers, so item10 follows item2.

Locale matters beyond English. Swedish treats Ö as a separate letter after Z, while German sorts it with O:

sv : Olsen, Zander, Ölund
de : Olsen, Ölund, Zander

If a product serves Swedish users an alphabetical list, { locale: "sv" } is the correct order and { locale: "en" } is a bug report waiting to happen.

Strength: what counts as equal

The collation option that matters most for queries is strength:

So “case-insensitive” in MongoDB usually means { locale: "en", strength: 2 }, and “case- and accent-insensitive” means strength: 1. A query with a collation looks like this:

db.users.find({ lastName: "muller" }).collation({ locale: "de", strength: 1 })
// matches "Muller", "MULLER" and "Müller"

Collation also works on aggregate, updateMany, deleteMany, distinct, countDocuments and the rest, passed as an option.

The index has to share the query’s collation

This is the part interviewers care about. An index stores keys in the order defined by its collation. A strength-2 index on email stores Ann@x.com and ann@x.com as equal keys, side by side. A query with a different collation, or with none, compares strings differently, so the planner can’t use that index’s ordering to answer string comparisons.

Here’s the trap in practice. Suppose you create a case-insensitive index:

db.users.createIndex({ email: 1 }, { collation: { locale: "en", strength: 2 } })

This query uses it:

db.users.find({ email: "ann@example.com" }).collation({ locale: "en", strength: 2 })

This one, with no collation, can’t use it for the string match:

db.users.find({ email: "ann@example.com" })

Without a matching index, the second query falls back to a collection scan. The illustrative explain() output below shows the difference you’d look for (abbreviated, in the shape MongoDB 7.0 and 8.0 report):

// with the matching collation
winningPlan: { stage: 'FETCH', inputStage: { stage: 'IXSCAN', indexName: 'email_1', collation: { locale: 'en', strength: 2, ... } } }

// without it
winningPlan: { stage: 'COLLSCAN', filter: { email: { '$eq': 'ann@example.com' } } }

It’s easy to miss in development, where the collection has 200 documents and both queries return instantly. In production with 20 million users, the second one takes seconds.

One nuance: an index with a collation can still be used for parts of a query that don’t compare strings, such as a numeric prefix of a compound index. String comparisons are the part that needs the collations to match.

Making it the default for a collection

Asking every developer to remember .collation(...) on every query is fragile. You can set a default collation when creating the collection:

db.createCollection("users", { collation: { locale: "en", strength: 2 } })

Then queries without a collation use the default, and indexes created without one inherit it. A query can still override it, and an index can be created with { collation: { locale: "simple" } } if one field really needs binary comparison (an API key, say).

The limitation is that you can’t change a collection’s default collation after creation. Adding it to an existing collection means creating a new collection with the collation and copying the data, which is worth planning before launch.

A worked example: unique emails, ignoring case

Your signup flow stores emails as typed. Support finds two accounts, Ann@Example.com and ann@example.com, both belonging to the same person, and password resets go to whichever one the lookup happens to hit first. You need lookups to ignore case and duplicates to be rejected.

The usual first attempt is a regex:

db.users.findOne({ email: /^ann@example\.com$/i })

It works, but a case-insensitive regex can’t use index bounds efficiently. MongoDB has to examine every index key (or every document) and test the regex. It also doesn’t stop duplicates. And unescaped user input in a regex is an injection risk.

The second common attempt is to store a lowercased copy, emailLower, with a unique index. That’s a legitimate design, and it’s what you’d do in many databases. Its weaknesses are that every write path must remember to set the field, and lowercasing isn’t a full case-folding for every language.

The collation approach puts the rule in the index:

db.users.createIndex(
  { email: 1 },
  { unique: true, collation: { locale: "en", strength: 2 } }
)

Now inserting ann@example.com when Ann@Example.com exists fails:

MongoServerError: E11000 duplicate key error collection: app.users index: email_1 collation: { locale: "en", ... strength: 2, ... } dup key: { email: "ann@example.com" }

(Illustrative; the real message lists every collation field.) Lookups use the same collation and hit the index:

db.users.findOne({ email: "ann@example.com" }, { collation: { locale: "en", strength: 2 } })

Before creating the index on an existing collection, you have to find and merge the duplicates, or the build fails with the same E11000 error. An aggregation grouping on the email with the same collation lists them:

db.users.aggregate(
  [ { $group: { _id: "$email", ids: { $push: "$_id" }, n: { $sum: 1 } } }, { $match: { n: { $gt: 1 } } } ],
  { collation: { locale: "en", strength: 2 } }
)

Strength 2 rather than 1 is a deliberate choice here: email local parts can technically contain non-ASCII characters, and treating é and e as identical in an address would be wrong.

A few places collation doesn’t reach: $text indexes ignore it and use their own rules, $regex matching doesn’t honour it, and it changes nothing about how strings are stored. The document keeps the original capitalisation, which is usually what you want for display.

Practice question: the slow search box

An admin page lets staff search customers by last name with a “starts with” box. The query is db.customers.find({ lastName: { $regex: "^" + input, $options: "i" } }).sort({ lastName: 1 }). There’s an index on { lastName: 1 }. It takes four seconds on 8 million customers. How would you fix it?

Answer and reasoning

The i option is the problem. An anchored, case-sensitive regex like /^Mul/ can turn into a tight index range, but a case-insensitive one can’t, because the matching keys (Mul…, mul…, MUL…) aren’t contiguous in a binary-ordered index. So the planner scans all index keys or the whole collection, and the binary-ordered sort may still look wrong to users.

I’d create an index with a case-insensitive collation, createIndex({ lastName: 1 }, { collation: { locale: "en", strength: 2 } }), and replace the regex with a range query using the same collation: find({ lastName: { $gte: input, $lt: input + "￿" } }).collation({ locale: "en", strength: 2 }).sort({ lastName: 1 }). Because the index treats case as equal, mul to mul￿ covers Muller, MULLER and muller in one contiguous range, and the sort comes from the index in language order. I’d confirm with explain("executionStats") that the plan is an IXSCAN with tight bounds and no in-memory SORT, and I’d test the upper-bound trick against the chosen locale, since that’s the step most worth verifying on real data. If accent-insensitive matching is wanted (muller finding Müller), use strength 1 instead. The input no longer needs regex escaping, but I’d still check it’s a plain string, since a client that sends an object could inject query operators.

Keep reading

• MongoDB interview questions
• MongoDB quiz questions
• Unique indexes in MongoDB
• Compound index design
• Collation in the MongoDB manual