CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-frameworkmongotemplate
Anshuman Tripathy
Anshuman Tripathy

Reputation: 113

mongodb $and Operation for $lte and $gte not working as expected for Integer value

I have created the following aggregation pipeline which fetches data from two collections based on certain filters. The main table being Allocation:

    {
   "aggregate":"Allocation",
   "pipeline":[
      {
         "$match":{
            "$and":[
               {
                  "allocPush.weekNum":{
                     "$gte":202109
                  }
               },
               {
                  "allocPush.weekNum":{
                     "$lte":202114
                  }
               },
               {
                  "statusCode":{
                     "$in":[
                        "C",
                        "L"
                     ]
                  }
               }
            ]
         }
      },
      {
         "$lookup":{
            "from":"Drug",
            "localField":"articleId",
            "foreignField":"_id",
            "as":"drugData"
         }
      },
      {
         "$unwind":"$drugData"
      },
      {
         "$sort":{
            "articleId":1,
            "sapStoreNum":1,
            "batchNum":1,
            "allocPush.weekNum":1
         }
      }
   ]
}

In my Allocation Collection I have an embedded Doc called allocPush which is a list of object.

I have an allocation document that has two allocPush documents like so:

enter image description here

One has a weekNum value of 202107 and the other 202117

In my aggregation pipeline I have an $and condition that verifies that it needs to fetch documents that have the allocPush.weekNum value between 202109 and 202114

but this aggregation still fetches the record that has allocPush.weekNum values 202107 and 202117 (Neither of which are between 202109 & 202114)

enter image description here

When I change the values of weekNum for both the allocPush docs to just 202107 or both to 202117 it doesn't fetch that record

Not sure why this is happening?

Upvotes: 0

Views: 955

Answers (1)

Joe
Joe

Reputation: 28326

This is a job for $elemMatch.

These query criteria

               {
                  "allocPush.weekNum":{
                     "$gte":202109
                  }
               },
               {
                  "allocPush.weekNum":{
                     "$lte":202114
                  }
               }]

are applied to the entire document. Since the document contains an allocPush.weekNum that is greater than 202109 and the the document also contains one that is less than 202114, the document as whole satisfies both conditions.

What you probably meant to check was that a single element of the array is between those two values. $elemMatch allows you to specify criteria that must be satisfied by a single element of the array in order for the document to match.

That might look something like:

{
    "allocPush": {
        "$elemMatch":{
            "weekNum":{
                "$gte":202109, 
                "$lte":202114
            }
         }
     }
}

Upvotes: 2

Related Questions