$filter nested level with $or, $gte, $lte operators

Question

I want to filter multi nested documents in MONGO db

Sample JSON:

{ 
"_id" : ObjectId("5b5c3afbcc43cb5ed64b7a04"), 
"id" : NumberLong(15015060), 
"name" : "1801_Conf", 
"type" : NumberInt(2), 
"members" : [
    {
        "id" : NumberLong(15015061), 
        "name" : "1801_Conf-W--", 
        "sku" : "1801_new", 
        "type" : NumberInt(1), 
        "parent_id" : NumberLong(15015060), 
        "available_qty" : NumberInt(10), 
        "on_hand_qty" : NumberInt(10), 
        "outgoing_qty" : NumberInt(0), 
        "incoming_qty" : NumberInt(0), 
        "shortage_qty" : NumberInt(0), 
        "product_warehouses" : [
            {
                "warehouse_id" : NumberLong(3), 
                "available_qty" : NumberInt(10), 
                "outgoing_qty" : NumberInt(0), 
                "incoming_qty" : NumberInt(0)
            },
            {
                "warehouse_id" : NumberLong(4), 
                "available_qty" : NumberInt(600), 
                "outgoing_qty" : NumberInt(0), 
                "incoming_qty" : NumberInt(0)
            }
        ], 
        ]
    } 
] 

}

Expected Output: Want to filter only members (not all) that have available_qty < 50 and > 10 and members.product_warehouses.available_qty < 50 and > 20

Query:

db.products.aggregate([{
   "$match": {
       "tenant_id": XXX,
       "type" : 2
   }
}, {
   "$project": {
       "name": 1,
       "sku": 1,
       "members": {
           "$filter": {
               "input": "$members",
               "as": "member",
               "cond": {

                       "$and": 
                        [
                            {
                               "$gte": ["$$member.product_warehouses.available_qty", 10]
                            }, 
                            {
                               "$lte": ["$$member.available_qty", 50]
                            },
                            {
                               "product_warehouses": {
                               "$elemMatch" : {

                                   }
                               }
                            }
                        ]

               }
           }
       }
   }
}])

Error: : { "ok" : 0, "errmsg" : "invalid operator '$elemMatch'", "code" : 15999 } : aggregate failed

Answer 1

You need to first $filter the members array and at the same time you have to apply $filter to the product_warehouses array with looping over the filtered members array using $map aggregation.

And last you need to put the cond with $and, $gt and $eq aggregation operator which is same as your Expected Output condition.

db.collection.aggregate([
  { "$project": {
    "members": {
      "$filter": {
        "input": {
          "$map": {
            "input": "$members",
            "as": "member",
            "in": {
              "$mergeObjects": [
                "$$member",
                { "product_warehouses": {
                  "$filter": {
                    "input": "$$member.product_warehouses",
                    "as": "product",
                    "cond": {
                      "$or": [
                        { "$lt": ["$$product.available_qty", 50] },
                        { "$gt": ["$$product.available_qty", 20] }
                      ]
                    }
                  }
                }}
              ]
            }
          }
        },
        "as": "member",
        "cond": {
          "$or": [
            { "$lte": [ "$$member.available_qty", 50 ] },
            { "$gte": [ "$$member.available_qty", 10 ] }
          ]
        }
      }
    }
  }},
  { "$match": { "members": { "$ne": [] } } }
])

Give it a try