How to Create a MongoDB Case-Insensitive Index

In this post, we’ll show you how to build case-insensitive indexes in MongoDB using Collations, a new feature released by MongoDB in version 3.4.

What are Case-Insensitive Indexes?

Case-insensitive indexes support queries that perform string comparisons without regard to the 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.

The Collation property can be set at the collection level or explicitly when creating an index.

1. Set the Collation Property at the collection level

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

2. Set the Collation Property when creating an index

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

3. Set the Collation Property while building an index

   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 } })
   

4. How to Query 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'})
   

   How to query case-insensitive indexes with Collation in MongoDBClick To Tweet

5. Specify Collation in your Query

   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:

6. Collation when upgrading from an older MongoDB version (3.2.x)

   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:

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

   Upgrade #MongoDB version to create case-insensitive indexes using CollationClick To Tweet

   More information of the incompatibilities is provided in 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: