{"id":35711,"date":"2016-07-23T18:14:38","date_gmt":"2016-07-23T12:44:38","guid":{"rendered":"https:\/\/tothenewco.pro\/blog\/?p=35711"},"modified":"2016-07-25T12:07:11","modified_gmt":"2016-07-25T06:37:11","slug":"mongo-case-insensitive-inlist-query","status":"publish","type":"post","link":"https:\/\/tothenewco.pro\/blog\/mongo-case-insensitive-inlist-query\/","title":{"rendered":"Mongo Case Insensitive InList Query"},"content":{"rendered":"

As we know in Mongo<\/a> the match queries are case sensitive. So whether we do a find()<\/code> operation or use $match<\/code> stage of aggregate<\/code> 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.<\/p>\n

To understand the requirement, lets see below json document list from a Sales<\/strong> collection:<\/p>\n

[code lang=”groovy”]
\n{
\n "_id" : ObjectId("576105331d41b83a770253f2"),
\n "EndUser" : "XYZ Ltd",
\n "SoldTo" : "0001005191",
\n "SalesItemQuantity" : NumberInt(10),
\n "VendorName" : "ABC",
\n "SalesOrder" : "SO411187"
\n}
\n{
\n "_id" : ObjectId("576105331d41b83a770253f3"),
\n "EndUser" : "XYZ LTD",
\n "SoldTo" : "0001005191",
\n "SalesItemQuantity" : NumberInt(8),
\n "VendorName" : "abc",
\n "SalesOrder" : "SO417994"
\n}
\n{
\n "_id" : ObjectId("576105331d41b83a770253f4"),
\n "EndUser" : "AAA LTD",
\n "SoldTo" : "0001005191",
\n "SalesItemQuantity" : NumberInt(13),
\n "VendorName" : "XYZ",
\n "SalesOrder" : "SO417564"
\n}
\n[\/code]<\/p>\n

We have three documents here. We want to find all the documents where EndUser<\/code> value is in list [XYZ LTD, ABC LTD]<\/code>. We can say “XYZ Ltd” and “XYZ LTD” are same for us but when we query to mongo, they are different.<\/p>\n

So how to do this:<\/strong>
\nWe need to use an aggregate pipeline here instead of a direct find() operation. Stages of pipeline query:<\/p>\n

    \n
  1. Projection – During this stage we will convert all EndUser values to their upper case.<\/li>\n
  2. Match – Match upper case EndUser values with the given list of values.<\/li>\n<\/ol>\n

    Mongo Query:<\/p>\n

    [code lang=”groovy”]
    \ndb.Sales.aggregate(<\/p>\n

    \/\/ Pipeline
    \n [
    \n \/\/ Stage 1
    \n {
    \n $project: {
    \n \/\/Convert EndUser to UpperCase
    \n "EndUser": {
    \n "$let": {
    \n \/\/var declaration
    \n "vars": {
    \n "endUserVar": "$EndUser"
    \n },
    \n "in": {
    \n \/\/conversion to upper case
    \n "$toUpper": "$$endUserVar"
    \n }
    \n }
    \n },
    \n \/\/We need to include all other fields also in projection otherwise they will not be available in next stage \u03b5(\u00b4\ufb41\ufe35\ufb41`)\u0437
    \n "SoldTo": 1,
    \n "SalesOrder": 1,
    \n "VendorName": 1,
    \n "SalesItemQuantity": 1
    \n }
    \n },<\/p>\n

    \/\/ Stage 2: match using in operator
    \n {
    \n $match: {
    \n "EndUser" : {
    \n "$in" : [
    \n \t"XYZ LTD",
    \n \t"ABC LTD"
    \n ]
    \n }
    \n }
    \n }<\/p>\n

    ]<\/p>\n

    );
    \n[\/code]<\/p>\n

    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 \ud83d\ude09<\/p>\n","protected":false},"excerpt":{"rendered":"

    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 […]<\/p>\n","protected":false},"author":182,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":12,"footnotes":""},"categories":[1],"tags":[3483,1900],"class_list":["post-35711","post","type-post","status-publish","format-standard","hentry","category-technology","tag-case-insensitive-query","tag-mongo-db"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t