Query to Fetch groupId based on Range Criteria in detailList Array Objects

Working with Data
#1

I have a set of the value in the array, I need to write a query in the arrays object

Below is my data

{
   "groupId":{
      "$numberLong":"12345"
   },
   "detailList":[
      {
         "Type":"P",
         "fromCode":"1000000",
         "toCode":"1100000"
      },
      {
         "Type":"P",
         "fromCode":"2000000",
         "toCode":"2200000"
      },
      {
         "Type":"M",
         "fromCode":"3000000",
         "toCode":"3300000"
      },
      {
         "Type":"M",
         "fromCode":"4000000",
         "toCode":"5500000"
      }
   ]
}

I need to fetch the groupId only when my searching criteria matched the range of the fromCode and toCode

For example:-
If I am searching 1000000-3300000 then there are two types P, M so, I have to return groupId only all the criteria has type P with the searched criteria

What will be the query?

#3

Hello :wave: @Prabhat_Gautam,

Welcome to the MongoDB Community forum :sparkles:

I have created a sample collection based on the shared data and written the following aggregation pipeline to get the desired output:

db.collection.aggregate([
  {
    $project: {
      groupId: 1,
      detailList: {
        $filter: {
          input: "$detailList",
          as: "detail",
          cond: {
            $and: [
              {
                $eq: ["$$detail.Type", "P"],
              },
              {
                $gte: [
                  "$$detail.fromCode",
                  "1000000",
                ],
              },
              {
                $lte: [
                  "$$detail.toCode",
                  "3300000",
                ],
              },
            ],
          },
        },
      },
    },
  },
]);

and it returned the following result:

{
  _id: ObjectId("645370489f19c564d617d4c6"),
  groupId: 12345,
  detailList: [
    {
      Type: 'P',
      fromCode: '1000000',
      toCode: '1100000'
    },
    {
      Type: 'P',
      fromCode: '2000000',
      toCode: '2200000'
    }
  ]
}

In the above query, I’m using the $filter operator to filter an array of subdocuments based on certain conditions. This operator takes an input array, an identifier for each element in the array, and a condition that determines which elements to include in the output array. Within the condition, I’ve used the comparison operators $eq, $gte and $lte to compare values and return the result.

Hope this helps. Feel free to reach out if you have any further questions.

Best,
Kushagra

1 Like