How to Create Case-Insensitive Indexes in MongoDB

Case-insensitive indexes support queries that perform string comparisons without regard for letter case, and with MongoDB 3.4's support of Collation, 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.

Creating a MongoDB Case-Insensitive Index

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:

>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"
}
}
]

Here’s how you can explicitly specify a Collation when creating a collection:

>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"
}
}
}
]

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’:

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

Querying Using Collation

The Collation property needs to be specified at the query time to use the index built with Collation:

db.test.find({name:'blah'})

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:

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

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.

Collation When Upgrading from an Older Version (3.2.x)

If you upgrade from an older version of MongoDB (i.e. 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:

db.adminCommand( { setFeatureCompatibilityVersion: "3.4" } )

More information of the incompatibilities is provided in the MongoDB 3.x release notes. 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.

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 ScaleGrid rolling index build job to add your indexes:

And that's it!