Collations and case-insensitive queries, explained simply

Collations and case-insensitive queries, explained simply

Users don't think about case sensitivity. They type "John" or "john" expecting the same person in your database. MongoDB gives you collations—a mechanism to handle text comparison and sorting the way users expect—but many teams implement them poorly or not at all, leading to frustrating search bugs in production. In this article, we'll walk through collations and case-insensitive queries so you can make text matching work naturally across your application.

What's the problem with case sensitivity?

By default, MongoDB treats text comparison as case-sensitive. If you store a user's email as "John@example.com" and search for "john@example.com", MongoDB returns nothing. This breaks the user experience.

You might reach for a regex workaround:

db.users.find({ email: { $regex: "^john@example\\.com$", $options: "i" } })

This works, but regex queries don't use indexes efficiently, forcing MongoDB to scan every document. For a table with millions of users, that's slow.

Collations solve this cleanly. A collation is a set of rules that defines how MongoDB compares and sorts strings. It's language-aware and lets you specify exactly how case should be treated.

Set collation per query or index

You can apply a collation two ways: at query time or when building an index. Query-time collation is flexible; index-time collation is faster.

Query-time collation

Apply collation directly to your find, updateOne, or aggregate call:

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

This finds the email regardless of case. The locale parameter sets the language rules (English, French, German, etc.), and we'll explain strength in the next section.

Query-time collation is useful for one-off searches or when you're unsure about collation requirements upfront. The downside: it's slower because MongoDB can't rely on a collated index.

Index-time collation

For queries you run constantly, build a collated index:

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

Now any query against this field uses the index automatically, provided your query specifies the same collation:

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

The explain plan will show IXSCAN instead of COLLSCAN, meaning the index is being used. This is the performance win we need in production.

Strength: 2 for case-insensitive matching

The strength parameter controls how strictly MongoDB compares characters. It has four levels:

For most applications, strength 2 is the right choice. It handles case-insensitivity while respecting accent marks. A French user searching for "café" won't accidentally match "cafe".

Here's a practical example. You're building an e-commerce site with a product search:

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

// User searches for "red shirt"
db.products.find(
  { name: "RED SHIRT" },
  { collation: { locale: "en", strength: 2 } }
)

The query finds products named "Red Shirt", "red shirt", "RED SHIRT"—all of them. But if your product is named "café mocha", a strength-2 collation won't match "cafe mocha".

If you need accent-insensitive matching too, use strength 1:

db.products.createIndex(
  { name: 1 },
  { collation: { locale: "en", strength: 1 } }
)

Now "café" matches "cafe", "CAFÉ", and "CAFE". Use strength 1 when your user base doesn't care about accents or when you're normalizing input (which we'll discuss next).

Indexes must use the same collation

This is critical and often overlooked: if you build a collated index, your query must specify the same collation, or MongoDB won't use the index.

Here's what goes wrong:

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

// Query without collation — index is NOT used
db.users.find({ email: "john@example.com" }).explain("executionStats")
// Result: COLLSCAN (full scan)

Your query is case-sensitive by default, so the index created for case-insensitive queries doesn't help. MongoDB falls back to scanning every document.

The fix is consistent collation everywhere:

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

// Query with matching collation — index IS used
db.users.find(
  { email: "john@example.com" },
  { collation: { locale: "en", strength: 2 } }
).explain("executionStats")
// Result: IXSCAN (index scan)

In production, we enforce this by wrapping queries in helper functions or using ODM tools like Mongoose, which can apply collations automatically.

Better than regex for equality matches

We mentioned earlier that regex is slow. Let's compare side by side.

Regex approach:

db.users.find({ email: { $regex: "^john@example\\.com$", $options: "i" } })
// Uses index only if regex is anchored at start; still less efficient

Collation approach:

db.users.find(
  { email: "john@example.com" },
  { collation: { locale: "en", strength: 2 } }
)
// Uses collated index directly; fast

With a 10-million-document collection, the collation query typically runs 5–10 times faster. The reason: MongoDB can use the index to narrow the result set immediately, whereas regex requires examining and comparing every value.

Regex is still useful for partial matching (e.g., "find emails that contain 'admin'"), but for equality searches, collations are the right tool.

Applying collations at the database level

For consistency, you can set a default collation on your entire collection:

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

Now every operation on that collection uses the collation automatically, unless overridden. This eliminates the risk of forgetting to add collation to a query.

However, if you're retrofitting an existing collection, you'd need to recreate it, which is costly for large tables. A better approach is to apply collation to indexes and handle queries carefully in your application layer.

Common mistakes

• Forgetting collation in queries when you have a collated index: Your index exists but doesn't get used because the query doesn't specify the same collation. Always match collation between index and query.
• Using strength 3 (default) and expecting case-insensitivity: Strength 3 is case-sensitive. You must explicitly set strength 2 (or 1 for accent-insensitivity) in every collation definition.
• Building a collated index without testing the query path: Create the index, then verify with explain that queries actually use it. A mismatch in locale or strength silently bypasses the index.
• Relying on data normalization instead of collation: Some teams convert all email to lowercase on write, then query lowercase. This works but doesn't handle sorting (what if you want "Smith" before "smith"?). Collations are more flexible.
• Mixing locales: If your users are global, pick one locale (usually "en") unless you have a specific reason to differ. Mixing locales across indexes causes confusion and index misses.

Key takeaways

• Collations define how MongoDB compares and sorts text. Use them to handle case-insensitivity and accent-insensitivity naturally.
• Set collation at index creation time for queries you run frequently; this ensures fast, index-backed lookups.
• Use strength: 2 for case-insensitive, accent-sensitive matching in most applications. Use strength: 1 if you need both case and accent insensitivity.
• Always specify the same collation in your query as in your index, or MongoDB won't use the index and will fall back to a full collection scan.
• Collations are faster and cleaner than regex for equality searches. Reserve regex for partial or pattern matching.
• Test your collation setup with explain() to confirm index usage before shipping to production.