A username lookup for ada can have different requirements from a binary comparison of the stored string Ada. MongoDB collation lets an operation specify locale-aware string comparison rules, and an index can be built under matching rules. This MongoDB 8.0 reference separates case sensitivity, accent sensitivity, uniqueness, and display ordering. The main fixture uses English collation at strength two: case differences are ignored for its demonstrated strings while accent distinctions remain relevant. The practice applies numeric ordering to labels containing digit sequences. Collation changes comparison behavior; it does not rewrite stored values, transliterate arbitrary scripts, or implement full-text search.
Choose comparison rules from the data contract
The locale identifies the language rules used for comparison. Strength chooses which levels of difference participate in comparison, with locale-dependent details. At strength two in the controlled English fixture, Ada and ada compare equally while the accented Áda remains distinct. At a lower strength, some differences that previously distinguished values can cease to matter. Do not describe every locale and strength combination with a single universal ignore-case slogan.
A collection can have a default collation, and an operation can supply an explicit one where supported. Without an applicable non-simple collation, the fixture's ordinary equality query uses its default simple comparison and does not equate Ada with ada. Be explicit when a field's business identity requires one policy and its presentation requires another. A username, a legal name, and an inventory code may have different comparison needs even when all are strings. The most convenient display ordering is not automatically the correct rule for account uniqueness.
Align index collation with operation collation
A non-simple index stores comparison keys under its configured collation. To support compatible string comparison using that index, the operation generally needs the matching collation. Creating a case-insensitive index alone does not cause every future query to ignore case. In the main fixture, the matching query finds stored Ada when it asks for lowercase ada. The query without collation does not inherit the index's comparison rule.
Name important indexes so their intended policy is visible during inspection. Different comparisons may require different indexes, with corresponding storage and write costs. A compound index can have documented partial usability for non-string prefix fields even when a string comparison's collation differs, so avoid an absolute statement that every collation mismatch makes every part of every index unusable. Explain the real query to verify support. Collation comparison keys can also be larger than the original string representation. Evaluate the full index and query workload rather than equating matching semantics with guaranteed performance.
Treat case-insensitive uniqueness as a write policy
A unique index with the chosen collation rejects values that compare equally under those rules. The main fixture stores Ada and Áda, then attempts ada. That new document conflicts with Ada and receives duplicate-key code 11000. The accented value remains allowed under this particular strength. This is a database-enforced identity rule, not merely a user-interface convention.
Before creating such an index on existing data, find and resolve records that would become equivalent. A binary-distinct dataset can still contain collisions under the new policy. Changing the comparison contract after users have registered requires a migration decision rather than a silent index replacement. A application-side lowercase check followed by an insert can also race with another writer; the database constraint remains the final enforcement boundary. Handle duplicate-key failures as expected business outcomes where appropriate. The catch in the fixture verifies the exact expected code and rethrows unrelated errors so failed connection or permission issues are not reported as successful uniqueness tests.
Keep regex, search, and numeric ordering separate
A regular expression has its own matching behavior. Case-insensitive indexes do not automatically accelerate case-insensitive regex queries because regex matching is not collation-aware under the documented behavior. A request for prefix matching therefore needs its own design and evidence. Collation equality is also not a replacement for tokenization, relevance ranking, stemming, or other search features.
numericOrdering asks comparison to treat applicable digit sequences numerically. The practice sorts item2, item3, and item10 in that intuitive sequence instead of the binary sequence item10, item2, item3. The option does not convert the stored labels into numbers or parse arbitrary decimal, signed, or scientific notation as an application numeric type. Store quantities as appropriate numeric BSON values when arithmetic or numeric range filtering is required. For sorted pagination, keep the same collation on every request and include a stable tie rule when distinct strings compare equally. Changing the comparison policy between pages can invalidate the cursor boundary.
MongoDB documentation and version notes
MongoDB Collation and Index Matching Examples
Each example shows the code, the output it produces and what each part does.
Enforcing case-insensitive uniqueness with collation
The fixture uses a disposable collection without a collection-level locale default. Its explicitly named unique index uses English strength-two collation. Two initial values differ by accent; a lowercase spelling of Ada is then rejected. The first equality lookup supplies the matching collation and finds ID one, while the second lookup uses the ordinary collection default and finds nothing. The stored mixed-case spelling remains Ada. The output therefore distinguishes write enforcement, query comparison, and stored representation instead of conflating them. All behavior is pinned to the stated engine and comparison options.
const users = db.getSiblingDB('codevra_ref_collation').getCollection('users');
users.drop();
const rules = { locale: 'en', strength: 2 };
users.createIndex({ name: 1 }, { name: 'name_en_strength2', unique: true, collation: rules });
users.insertMany([{ _id: 1, name: 'Ada' }, { _id: 2, name: 'Áda' }]);
try { users.insertOne({ _id: 3, name: 'ada' }); }
catch (error) { if (error.code !== 11000) throw error; print('duplicate code: ' + error.code); }
const matching = users.find({ name: 'ada' }).collation(rules).sort({ _id: 1 }).toArray();
const simple = users.find({ name: 'ada' }).toArray();
print('matching collation IDs: ' + matching.map(d => d._id).join(','));
print('simple matches: ' + simple.length);
print('stored spelling: ' + users.findOne({ _id: 1 }).name);duplicate code: 11000
matching collation IDs: 1
simple matches: 0
stored spelling: AdaMongoDB Collation and Index Matching in Real Projects
Sort labels numerically with a collation index
Compare binary label sorting with numeric ordering, then choose the compatible named index explicitly for the numeric sort. The separate fixture inserts three unique labels and creates an English numeric-ordering index. Its first query intentionally omits collation, giving the binary result. The second query specifies the index's collation and hints that named index, making the demonstration's index choice reviewable. Hinting is a fixture teaching device rather than a general instruction to force plans in production. The labels have no numeric ties, so the expected ordering is fully determined by this sample.
const labels = db.getSiblingDB('codevra_ref_collation').getCollection('practice');
labels.drop();
labels.insertMany([{ _id: 1, label: 'item2' }, { _id: 2, label: 'item10' }, { _id: 3, label: 'item3' }]);
const rules = { locale: 'en', numericOrdering: true };
labels.createIndex({ label: 1 }, { name: 'label_numeric', collation: rules });
const simple = labels.find({}).sort({ label: 1 }).toArray();
const numeric = labels.find({}).collation(rules).sort({ label: 1 }).hint('label_numeric').toArray();
print('simple: ' + simple.map(d => d.label).join(','));
print('numeric: ' + numeric.map(d => d.label).join(','));simple: item10,item2,item3
numeric: item2,item3,item10MongoDB Collation and Index Matching FAQ
Does the index make all queries case-insensitive?
No. A query still uses its applicable comparison rules. The main fixture's unqualified lookup has zero matches for lowercase ada, while the explicitly matching collation finds Ada. A collection-level default would change what unqualified operations inherit. Document that distinction so a developer does not infer query semantics simply from the existence of a case-insensitive index.
Does strength two always ignore accents?
No. In the demonstrated English configuration, strength two retains the accent distinction while ignoring the shown case difference. Locale and strength interact, so test the actual scripts and values important to your application. The Ada and Áda example is intentionally chosen to show that case insensitivity does not automatically mean every accent variation becomes identical.
Can I use an ignore-case regex to benefit from this index?
Do not assume it. Regex matching is not collation-aware in the documented behavior, and a case-insensitive index is not a general acceleration mechanism for case-insensitive regex searches. Equality under matching collation is a different operation. For prefix or text search, select a supported query design and verify its real execution plan rather than transferring equality-index expectations to regex.
Should numericOrdering replace numeric fields?
No. It provides a string comparison policy for supported digit sequences, useful for labels such as item2 and item10. It does not give those labels numeric BSON types or make arbitrary signed decimal text behave like a quantity. Store amounts and measurements as suitable numeric values. Reserve this option for the string-ordering requirement it actually addresses.
MongoDB Collation and Index Matching Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Which lookup in the main fixture finds the stored Ada for lowercase ada?
MongoDB Collation and Index Matching Quiz
2 questions to check what you learned.
Why is the attempted ada insertion rejected?
- It compares equally to Ada under the unique index collation
- Every accented name reserves all three-character strings
- Collation forbids storing lowercase letters
Which practice ordering uses numericOrdering?
- item2,item3,item10
- item10,item2,item3
- item3,item2,item10