How to Create Case-Insensitive Indexes in MongoDB®

3 min read
How to Create Case-Insensitive Indexes in MongoDB®

SHARE THIS ARTICLE

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.

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

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

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

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

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:

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

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:

Case insensitive Index builds with collation

Read Also:
Cassandra Vs. MongoDB
Reducing Your Database Hosting Costs: DigitalOcean vs. AWS vs. Azure
How to enable logging for Mongoose and the MongoDB Node.JS driver

For more information, please visit www.scalegrid.io. Connect with ScaleGrid on LinkedIn, X, Facebook, and YouTube.
Table of Contents

Stay Ahead with ScaleGrid Insights

Dive into the world of database management with our monthly newsletter. Get expert tips, in-depth articles, and the latest news, directly to your inbox.

Related Posts

pitr mysql

Master MySQL Point in Time Recovery

Data loss or corruption can be daunting. With MySQL point-in-time recovery, you can restore your database to the moment before...

Setting Up MongoDB SSL Encryption

In a world where data security is essential, enabling MongoDB SSL is critical in fortifying your database. This guide walks...

distributed storage system

What is a Distributed Storage System

A distributed storage system is foundational in today’s data-driven landscape, ensuring data spread over multiple servers is reliable, accessible, and...

NEWS

Add Headline Here