Case-insensitive queries represent that the query should return results regardless of the case of the values from the MongoDB collection. In this article, we will look at various instances to perform case-insensitive queries in MongoDB.
To demonstrate this I will use the following student collection.
# Create Student collection
db.student.insertMany([
{
"_id": 1,
"name": "James",
"studentImage": "JamesImage.jpg",
"contentType": "image/jpeg"
},
{
"_id": 2,
"name": "Kelie",
"studentImage": "JamesImage.jpg",
"contentType": "image/jpeg"
},
{
"_id": 3,
"name": "Harry",
"studentImage": "JamesImage.jpg",
"contentType": "image/jpeg"
},
{
"_id": 4,
"name": "Peter",
"studentImage": "JamesImage.jpg",
"contentType": "image/jpeg"
},
{
"_id": 5,
"name": "Tom",
"studentImage": "JamesImage.jpg",
"contentType": "image/jpeg",
"assignment" : [{"assignment1" :"MongoDB.txt"}]
}
]
)
1. Using the $options modifier to query case insensitive in MongoDB
You can use the find() method with a regular expression to query the document case insensitive from the MongoDB collection. Make sure the regular expression using i option to perform case-insensitive searches.
For example, in the following find query, the name field is specified with the expression /alice/i. Thus, it will return all documents of the collection student where the field name contains the string alice in any case.
# Usage of regular expression with case insesnsitive
db.student.find({ name: /alice/i })
The document with the given string in the name field is displayed in the output. Note that the name field string contains the first character in uppercase, but the document is retrieved by the $option modifier.
2. Using the $regex operator to query case insensitive in MongoDB
Alternatively, you can also use the most common way for case insensitive query which is known as the $regex operator. The $regex operator allows us to perform regular expression searches in MongoDB. We can use it to perform case-insensitive queries by specifying the i flag in the regular expression pattern.
For example, we have the following find query where the course field is specified with the $regex operator, and the $regex operator is set with the regular expression /mongoDB/i. As a result, it returns all documents where the course field contains the string mongoDB regardless of the case.
# Usage of $regex operator
db.student.find({ course: { $regex: /mongoDB/i } })
The output displayed the document where the course field value is mongoDB by ignoring the case.
3. Using the $where operator to query case insensitive in MongoDB
We can also use the $where operator which allows us to specify a JavaScript expression to filter documents. We can use the JavaScript toLowercase() method to convert the value to lowercase and perform case-insensitive searches. You can also use the toUppercase().
For example, we have a find query here which is set with the $where operator. The $where operator is specified with the JavaScript expression where it takes the field name and applied the toLowerCase() method. The toLowerCase() method converts the name field to lowercase and the indexOf() method checks if the string value caroline is present in the lowercase name. In the end, the documents having the string caroline in any case will be returned.
# Usage of $where operator
db.student.find({ $where: "this.name.toLowerCase()
.indexOf('caroline') >= 0"
})
The output is yielded with the expected document below.
4. Using the collation option to query case insensitive in MongoDB
In addition to the prior examples, the collation option is also a way for a case-insensitive query that allows us to specify the collation rules for a query.
For example, we have the following command where the $regex operator is assigned with the string python of the field course to be matched and the $options operator is set with the option i. Then, we used the collation() method which takes the locale and strength parameters. The locale parameter is specified with the en, indicating that the search will be in English. Conversely, the strength parameter is assigned the value 1 for the query to perform primary-level collation.
# Usage of collation option
db.student.find({ course: { $regex: /python/, $options: 'i' } })
.collation({ locale: 'en', strength: 1 })
The output has the document where the course field has the string python despite the case.
5. Using the aggregation framework
In some other cases, we need to perform a case-insensitive query with more advanced functions. So, we can use the framework of aggregation in MongoDB.
For example, we have the following find query which finds for the documents where the email field have the string [email protected] in any casing and then with $group stage, groups the results by the email field. After that count the number of documents in each group using the count method.
# Usage of aggregation framework
db.student.aggregate([
{ $match: { email: { $regex: /[email protected]/i } } },
{ $group: { _id: "$email", count: { $sum: 1 } } }
])
The output is fetched from the searched document below.
6. Conclusion
In conclusion, MongoDB provides several ways to perform case-insensitive queries, based on your specific needs you can choose the option from the above examples. By using these techniques, we can query the MongoDB database while ignoring the case of the values in the database.
More details about this topic can be found here.