How to Create Case-Insensitive Indexes in MongoDB
Tags: database, collation, index, mongodb
Published: 2017-11-01 14:14:07

---
title: How to Create Case-Insensitive Indexes in MongoDB
published: true
description: Case-insensitive indexes support queries that perform string comparisons without regard for letter case, and with MongoDB 3.4's supports of Collation, these are
tags: database, Collation, index, MongoDB,
canonical_url: https://scalegrid.io/blog/how-to-create-case-insensitive-indexes-in-mongodb/
---
<link rel="canonical" href="https://scalegrid.io/blog/how-to-create-case-insensitive-indexes-in-mongodb/" />

<a href="https://scalegrid.io/blog/wp-content/uploads/2017/10/Create-Case-Insensitive-Indexes-in-MongoDB-ScaleGrid-Blog.jpg"><img class="alignleft size-full wp-image-2580" src="https://scalegrid.io/blog/wp-content/uploads/2017/10/Create-Case-Insensitive-Indexes-in-MongoDB-ScaleGrid-Blog.jpg" alt="How To Create Case Insensitive Indexes in MongoDB - ScaleGrid Blog" width="100%" height="auto" /></a>

<p style="text-align: justify;">Case-insensitive indexes support queries that perform string comparisons without regard for letterÂ case, and with <a href="https://www.mongodb.com/mongodb-3.4" target="_blank">MongoDB 3.4's</a> supports ofÂ <a href="https://docs.mongodb.com/manual/reference/collation/#collation-document-fields" target="_blank">Collation</a>, these are now possible to build. Collation gives you the ability to specify language-specific rules for string comparison. Since MongoDB's previous versions did not support Collation, you were limited to performing a case-sensitive index comparison.Â  In the scenarios that needed case-insensitive behavior, the only option was to convert/store all of your strings to either uppercase or lowercaseÂ and then do the comparison. As you can imagine, this causes a lot of hassle with queries and index operations.</p>

<h1>Creating a MongoDB Case-Insensitive Index</h1>
<p style="text-align: justify;">The Collation property can be set at the collection level or explicitly when creating an index. If set at the collection level, it doesn't need to be specified with every create-index command on as the index inherits the Collation of the collection. Unless specified explicitly during creation time, a collection has no Collation associated with it. You can determine the Collation details of your collection using commands provided below:</p>



>db.createCollection("test")
>db.getCollectionInfos({name: test'});
[
{
"name" : "test",
"type" : "collection",
"options" : {
},
"info" : {
"readOnly" : false
},
"idIndex" : {
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test.test"
}
}
]


<p style="text-align: justify;">Here's how you can explicitly specify a Collation when creating a collection:</p>



>db.createCollection("test2", { collation: { locale: 'en_US', strength: 2 } } );

>db.getCollectionInfos({name: 'test2'})
[
{
"name" : "test2",
"type" : "collection",
"options" : {
"collation" : {
"locale" : "en_US",
"caseLevel" : false,
"caseFirst" : "off",
"strength" : 2,
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
}
},
"info" : {
"readOnly" : false
},
"idIndex" : {
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test.test2",
"collation" : {
"locale" : "en_US",
"caseLevel" : false,
"caseFirst" : "off",
"strength" : 2,
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
}
}
}
]



<p style="text-align: justify;">You also have the option to explicitly set the Collation for an index while building it. For example, adding "index" on the name property of the test collection with locale 'en' and strength 2:</p>



<blockquote>>db.test.createIndex( { name: 1}, { collation: { locale: 'en', strength: 2 } })</blockquote>


<h2>Querying using Collation</h2>
<p style="text-align: justify;">The Collation property needs to be specified at the query time to use the index built with Collation:</p>



<blockquote>db.test.find({name:'blah'})</blockquote>


<p style="text-align: justify;">This query will not use the index specified above since Collation was not specified. In order to leverage the Collation, we need to specify it explicitly in the query:</p>



<blockquote>db.test.find({name:'blah'}).collation({ locale: 'en', strength: 2 })</blockquote>


<p style="text-align: justify;">Even if your collection has a default Collation, you still need to specifyÂ the Collation in your query. Otherwise, MongoDB will not use the specific index:</p>

<h2>Collation when upgrading from an older version (3.2.x)</h2>
<p style="text-align: justify;">If you upgrade from an older versionÂ of MongoDB (E.g. 3.2.x), the existing indexes will not support Collation. To get Collation up and running, your first step is to make sure all the new features of 3.4.x are turned on:</p>



<blockquote>db.adminCommand( { setFeatureCompatibilityVersion: "3.4" } )</blockquote>


<p style="text-align: justify;">More information of the incompatibilities is provided in<a href="https://docs.mongodb.com/v3.4/release-notes/3.4-compatibility/#compatibility-enabled" target="_blank">Â MongoDB 3.x release notes</a>. Please note that once you do these steps, it's harder to downgrade back to 3.2. Our next step is to check the version of your index.</p>
<p style="text-align: justify;">Once you've upgraded to 3.4 compatibilities, you can create new indexes by following the steps we outlined earlier. If you're building a large index, please use the <a href="https://scalegrid.io/blog/the-perils-of-building-indexes-on-mongodb/" target="_blank">ScaleGrid rolling index build job</a> to add your indexes:</p>
<a href="https://scalegrid.io/blog/wp-content/uploads/2017/09/indexwithcollation.jpg"><img class="alignleft size-full wp-image-2580" src="https://scalegrid.io/blog/wp-content/uploads/2017/09/indexwithcollation.jpg" alt="Case insensitive Index builds with collation" width="900" height="587" /></a>