Mongo Case Insensitive InList Query
As we know in Mongo the match queries are case sensitive. So whether we do a find() operation or use $match stage of aggregate pipeline, the condition would be true only if its a exact case sensitive match. Of cource we can use regex to do case insensitive queries but then again it will not work when we are trying to find a match with in a list of possible matches. Although we can use javascript for that but there is no easy way when you want to do this using java driver for mongo and aggregation pipeline.
To understand the requirement, lets see below json document list from a Sales collection:
[code lang=”groovy”]
{
"_id" : ObjectId("576105331d41b83a770253f2"),
"EndUser" : "XYZ Ltd",
"SoldTo" : "0001005191",
"SalesItemQuantity" : NumberInt(10),
"VendorName" : "ABC",
"SalesOrder" : "SO411187"
}
{
"_id" : ObjectId("576105331d41b83a770253f3"),
"EndUser" : "XYZ LTD",
"SoldTo" : "0001005191",
"SalesItemQuantity" : NumberInt(8),
"VendorName" : "abc",
"SalesOrder" : "SO417994"
}
{
"_id" : ObjectId("576105331d41b83a770253f4"),
"EndUser" : "AAA LTD",
"SoldTo" : "0001005191",
"SalesItemQuantity" : NumberInt(13),
"VendorName" : "XYZ",
"SalesOrder" : "SO417564"
}
[/code]
We have three documents here. We want to find all the documents where EndUser value is in list [XYZ LTD, ABC LTD]. We can say “XYZ Ltd” and “XYZ LTD” are same for us but when we query to mongo, they are different.
So how to do this:
We need to use an aggregate pipeline here instead of a direct find() operation. Stages of pipeline query:
- Projection – During this stage we will convert all EndUser values to their upper case.
- Match – Match upper case EndUser values with the given list of values.
Mongo Query:
[code lang=”groovy”]
db.Sales.aggregate(
// Pipeline
[
// Stage 1
{
$project: {
//Convert EndUser to UpperCase
"EndUser": {
"$let": {
//var declaration
"vars": {
"endUserVar": "$EndUser"
},
"in": {
//conversion to upper case
"$toUpper": "$$endUserVar"
}
}
},
//We need to include all other fields also in projection otherwise they will not be available in next stage ε(´סּ︵סּ`)з
"SoldTo": 1,
"SalesOrder": 1,
"VendorName": 1,
"SalesItemQuantity": 1
}
},
// Stage 2: match using in operator
{
$match: {
"EndUser" : {
"$in" : [
"XYZ LTD",
"ABC LTD"
]
}
}
}
]
);
[/code]
One issue that I can see here is during projection query at first stage, we have to include all parent keys which we want to be available in next stage. But again if it helps to resolve our problem then we can do that 😉
I must say, this article exceeded my expectations!
The depth of examination and the original point of view offered really sets it apart.
The writer’s command of the subject is evident, making complex concepts readily understandable.
The writing style is engaging and holds the reader’s attention from the very
beginning. I found myself engrossed in the content, enthusiastically absorbing every word.
This is a laudable piece of work that deserves recognition. Well done!