Case-Insensitive Queries and Collation in MongoDB
A user signs up as Alice@Example.com. A week later they try to log in with alice@example.com, and your query returns nothing. Worse, they hit "Sign up" again and now you have two accounts for the same person. MongoDB compares strings byte by byte by default, so to the database those two emails are completely different values.
The quick fix most people reach for is a case-insensitive regular expression. It works, and it quietly turns an indexed lookup into a scan of every key in the index. The better tool is collation: a set of language-aware rules that tell MongoDB how to compare and sort strings. With the right collation, "Alice" and "alice" match, "résumé" can match "resume" if you want it to, and sorting puts "apple" before "Zebra" the way humans expect.
This guide covers why default comparisons are case-sensitive, the three practical ways to handle case-insensitive queries, how collation strength works, how to build indexes that actually get used, and the gotchas that trip people up.
Why MongoDB Is Case-Sensitive by Default
Without a collation, MongoDB uses simple binary comparison for strings. It compares the UTF-8 bytes directly. That's fast and predictable, but it has consequences that surprise people:
db.users.insertMany([
{ name: "alice" },
{ name: "Bob" },
{ name: "Zoe" },
{ name: "émile" },
]);
db.users.find({}, { _id: 0, name: 1 }).sort({ name: 1 });
[{ name: "Bob" }, { name: "Zoe" }, { name: "alice" }, { name: "émile" }];
Every uppercase ASCII letter has a smaller byte value than every lowercase one, so "Zoe" sorts before "alice". Accented characters are multi-byte sequences with high values, so "émile" lands at the very end. And an equality query for { name: "Alice" } matches nothing, because the bytes differ.
Binary comparison is the right default for identifiers, codes, and hashes. For anything a human typed, you usually want something smarter.
Three Ways to Match Without Case
There are three common strategies. They have very different performance characteristics.
Option 1: Case-Insensitive Regex
db.users.find({ email: /^alice@example\.com$/i });
This matches regardless of case, and it's tempting because it requires no schema changes. The problem is indexes. A case-insensitive regex can't use tight index bounds, so even with an index on email, MongoDB must examine every key in the index and test each one against the pattern. On a collection of a few thousand users, you won't notice. On ten million, you will.
It also has a correctness trap: if you build the pattern from user input without escaping it, a . or + in the email changes the meaning of the query. The post on regular expression queries and their pitfalls covers escaping in detail. For exact-match lookups, regex is the wrong tool.
Option 2: Store a Normalized Copy
The classic relational trick works in MongoDB too. Store the original value for display and a lowercased copy for lookups:
db.users.insertOne({
email: "Alice@Example.com",
emailLower: "alice@example.com",
});
db.users.createIndex({ emailLower: 1 }, { unique: true });
db.users.findOne({ emailLower: "alice@example.com" });
This is fast, uses a normal index, and is easy to reason about. The downsides are that your application must keep the two fields in sync on every write, and toLowerCase() only handles case. It won't make "café" match "cafe", and lowercasing rules differ by language (Turkish dotted and dotless "i" is the famous example).
Option 3: Collation
Collation moves the comparison rules into the database. You specify a locale and a strength, and MongoDB compares strings according to Unicode collation rules for that locale:
db.users
.find({ email: "alice@example.com" })
.collation({ locale: "en", strength: 2 });
That query matches Alice@Example.com, ALICE@EXAMPLE.COM, and every other case variant. With a matching index (covered below), it's an ordinary index seek. No duplicate fields, no regex, no application-side normalization.
How Collation Works
A collation document has one required field and several optional ones:
{
locale: "en", // required: language rules to apply
strength: 2, // comparison level, 1-5 (default 3)
caseLevel: false, // add a case comparison at strength 1 or 2
caseFirst: "off", // "upper" or "lower" to control tie order
numericOrdering: false, // compare digit sequences as numbers
alternate: "non-ignorable", // "shifted" ignores spaces and punctuation
maxVariable: "punct", // what "shifted" ignores
backwards: false, // French accent ordering
normalization: false // Unicode normalization check
}
In practice you'll almost always set locale and strength, occasionally numericOrdering, and rarely the rest.
Strength Levels
Strength decides which differences count. Each level includes the ones before it:
| Strength | Compares | "cafe" vs "Café" | "cafe" vs "CAFE" | "cafe" vs "café" |
|---|---|---|---|---|
| 1 (primary) | Base letters only | Equal | Equal | Equal |
| 2 (secondary) | Base letters + accents | Not equal | Equal | Not equal |
| 3 (tertiary) | Letters + accents + case | Not equal | Not equal | Not equal |
The default strength is 3, which is case-sensitive. That's the part people miss: specifying { locale: "en" } on its own gives you better sorting, but it doesn't make matching case-insensitive. You need strength 1 or 2 for that.
- Use strength 2 for case-insensitive but accent-sensitive matching. This is the right choice for emails, usernames, and most identifiers.
- Use strength 1 when accents should also be ignored, such as a name search where users type "Muller" and expect to find "Müller".
Strengths 4 and 5 exist for very fine-grained distinctions (punctuation variants, identical code points) and are rarely needed.
Locale Matters for Sorting
The locale changes both matching and ordering. With locale: "en", the earlier sort example becomes human-friendly:
db.users
.find({}, { _id: 0, name: 1 })
.sort({ name: 1 })
.collation({ locale: "en" });
[{ name: "alice" }, { name: "Bob" }, { name: "émile" }, { name: "Zoe" }];
Other languages have their own rules. In Swedish, "å", "ä", and "ö" sort after "z". In German phonebook ordering, "ä" is treated like "ae". Pick the locale your users read in. If you serve many languages, you can pass a different collation per query, though each one needs its own index to be efficient. Use locale: "simple" to explicitly request binary comparison.
Numeric Ordering
Binary and default collation both sort "item10" before "item2", because they compare character by character. numericOrdering: true treats digit runs as numbers:
db.files
.find({}, { _id: 0, name: 1 })
.sort({ name: 1 })
.collation({ locale: "en", numericOrdering: true });
[{ name: "item1" }, { name: "item2" }, { name: "item10" }, { name: "item20" }];
This is handy for file names, version-like labels, and invoice numbers stored as strings. It only handles non-negative integers; it doesn't understand decimals or negative signs.
Indexes and Collation
This is where collation goes from convenient to fast, and also where most mistakes happen.
An index has a collation, just like a query. A query can only use an index for string comparisons if the query's collation matches the index's collation. If they differ, the planner can't use that index for the string bounds and falls back to another plan or a collection scan.
Create an index with a collation like this:
db.users.createIndex(
{ email: 1 },
{ collation: { locale: "en", strength: 2 }, name: "email_ci" },
);
Now compare two queries:
// Uses email_ci: collation matches
db.users
.find({ email: "ALICE@example.com" })
.collation({ locale: "en", strength: 2 });
// Cannot use email_ci for the string match: no collation specified
db.users.find({ email: "ALICE@example.com" });
Always confirm with explain(). If the winning plan shows IXSCAN on your collated index, you're good. If you see COLLSCAN, check that the collation on the query is identical, down to the strength.
Case-Insensitive Unique Constraints
A unique index with a collation enforces uniqueness under that collation's rules. This solves the duplicate-account problem from the introduction in one line:
db.users.createIndex(
{ email: 1 },
{
unique: true,
collation: { locale: "en", strength: 2 },
name: "email_unique_ci",
},
);
db.users.insertOne({ email: "Alice@Example.com" });
db.users.insertOne({ email: "alice@example.com" });
MongoServerError: E11000 duplicate key error collection: app.users index: email_unique_ci dup key: { email: "alice@example.com" }
The second insert fails because, at strength 2, the two emails are equal. The document still stores the email exactly as the user typed it, so you keep the original casing for display. For handling that error gracefully in application code, see handling duplicate key errors and unique constraints.
Setting a Default Collation on a Collection
If every string comparison in a collection should be case-insensitive, set a default collation when you create it:
db.createCollection("users", {
collation: { locale: "en", strength: 2 },
});
Queries, sorts, and indexes on that collection inherit the default unless they specify their own. Plain db.users.find({ email: "ALICE@EXAMPLE.COM" }) now matches, and createIndex({ email: 1 }) builds a collated index automatically.
Two important constraints:
- You can't change a collection's default collation after creation. To change it, create a new collection with the new collation and copy the data across (for example with
$outor$merge), then rebuild indexes and swap names. - The default applies to every string field, including ones where you might want exact matching, like API keys or SKUs. If you need binary comparison on a specific query, pass
collation({ locale: "simple" }), and remember that query then needs a simple-collation index.
A per-collection default works best when the collection is dominated by human-entered text. For mixed collections, explicit per-index and per-query collations are easier to reason about.
Using Collation from Drivers
Every official driver supports collation as a query option.
Node.js Driver
const collation = { locale: "en", strength: 2 };
const user = await db
.collection("users")
.findOne({ email: req.body.email }, { collation });
const sorted = await db
.collection("users")
.find({ country: "SE" }, { collation: { locale: "sv" } })
.sort({ lastName: 1 })
.toArray();
await db
.collection("users")
.updateOne(
{ email: req.body.email },
{ $set: { lastLoginAt: new Date() } },
{ collation },
);
Updates and deletes accept collation too, which matters: an updateOne without it won't match the differently cased document.
Mongoose
Mongoose exposes collation on queries and lets you declare collated indexes on the schema:
const userSchema = new mongoose.Schema({
email: { type: String, required: true },
});
userSchema.index(
{ email: 1 },
{ unique: true, collation: { locale: "en", strength: 2 } },
);
const User = mongoose.model("User", userSchema);
const user = await User.findOne({ email }).collation({
locale: "en",
strength: 2,
});
You can also set a schema-level default with new mongoose.Schema({ ... }, { collation: { locale: "en", strength: 2 } }), which Mongoose applies when it creates the collection.
PyMongo
from pymongo.collation import Collation, CollationStrength
ci = Collation(locale="en", strength=CollationStrength.SECONDARY)
db.users.create_index("email", unique=True, collation=ci, name="email_unique_ci")
user = db.users.find_one({"email": "ALICE@example.com"}, collation=ci)
names = db.users.find({}, {"name": 1}).sort("name", 1).collation(Collation(locale="en"))
Collation in Aggregation
Aggregation pipelines accept a collation as an option. It applies to string comparisons throughout the pipeline, including $match, $sort, $group keys, and comparison expressions:
db.orders.aggregate(
[
{ $match: { status: "shipped" } },
{ $group: { _id: "$customerEmail", orders: { $sum: 1 } } },
{ $sort: { orders: -1 } },
],
{ collation: { locale: "en", strength: 2 } },
);
With the collation, Alice@Example.com and alice@example.com land in the same group. Without it, they'd be counted as two customers. The group's _id will be one of the original spellings; if you need a canonical form in the output, add a $toLower in a $project after the group.
Limitations to Know
Regex ignores collation. $regex always uses its own matching rules. A collation on a query doesn't make /^ali/ case-insensitive; you still need the i flag, with its index cost. For case-insensitive prefix search, a normalized lowercase field with a case-sensitive anchored regex is usually the best option.
Text indexes and 2d indexes only support simple binary comparison. You can't create them with a non-simple collation. If your collection has a default collation, create those indexes with collation: { locale: "simple" }.
Collation has a small cost. Collated index keys are stored as collation sort keys, which can be larger than raw strings, and comparisons do more work than a byte comparison. For most workloads this is negligible, but it's one reason not to apply a collation everywhere by habit.
Collated indexes can't cover some queries. Because the index stores collation keys rather than original strings, MongoDB may need to fetch documents to return the actual string values, so a query that projects only the collated field might not be a covered query.
Common Pitfalls
Forgetting strength. { locale: "en" } alone is still case-sensitive. If matching doesn't behave as expected, check that you set strength: 1 or 2.
Mismatched query and index collations. The index uses strength 2, the query uses strength 1 (or no collation), and every lookup becomes a collection scan. Define the collation once as a shared constant in your code and use it everywhere.
Updating without the collation. A findOne with collation finds the user, and then an updateOne without it matches nothing. Pass the same collation to reads and writes.
Choosing strength 1 for identifiers. Ignoring accents is great for name search and wrong for usernames, where "josé" and "jose" may legitimately be different people. Default to strength 2 for anything that needs to be unique.
Expecting to change the collection default later. Plan the default up front, or skip it and use explicit index collations, which are easy to add and drop.
Conclusion
Case-sensitivity bugs look minor until they produce duplicate accounts or failed logins. Regex with the i flag is fine for ad hoc exploration but scales poorly, and normalized shadow fields work but add bookkeeping. Collation gives you language-aware matching and sorting directly in the database, and a collated unique index turns "no two users with the same email, ignoring case" into a guarantee instead of a hope.
Open your users collection, create a unique index on email with { locale: "en", strength: 2 }, and update your login and signup queries to pass the same collation. Then run explain() on the login query to confirm it's an index scan.
