Cara menggunakan nested mongodb filter

I have a collection with a nested array of subdocuments. I’d like to filter out the subdocuments in this nested array that have the field status: REMOVED, and then return the original document unchanged aside from the filtered subdocument array. I have this working in the following aggregate pipeline:

   db.getCollection('inventory').aggregate([
    {
        $match: {
            updatedat: {
                $gte: ISODate("2021-05-10T12:00:00Z"),
                $lte: ISODate("2021-05-11T12:00:00Z")
            }
        }   
    },
    { $unwind: "$sizes" },
    {
        $match: {
            "sizes.status": { $ne: "REMOVED" }
        }
    },
    {
        $group: {
           _id: {
               item: "$item",
               price: "$price",
               updatedat: "$updatedat",
               fees: "$fees"
           },
           sizes: { $push: "$sizes" } 
        }
    },
    {
        $project: { _id: 0, sizes: 1, item: "$_id.item", price: "$_id.price", updatedat: "$_id.updatedat", fees: "$_id.fees" }
    }

Here is an example document in my collection:

    {
        "_id" : ObjectId("60996db251b4a0b97ee405ba"),
        "item" : "A",
        "price" : NumberDecimal("80"),
        "updatedat" : ISODate("2021-05-10T12:00:00.000Z"),
        "fees" : {
            "texttext" : "sold in!",
            "taxes" : [ 
                {
                    "type" : 1.0,
                    "description" : "QC/CA#1234"
                }, 
                {
                    "type" : 2.0,
                    "description" : "QC/CA#2231"
                }
            ]
        },
        "sizes" : [ 
            {
                "size" : "S",
                "status" : "AVAILABLE"
            }, 
            {
                "size" : "M",
                "status" : "REMOVED"
            }, 
            {
                "size" : "L",
                "status" : "AVAILABLE"
            }
        ]
    }

This returns what I need, but managing each root level field by grouping them inside _id, and then projecting them in the final stage is tedious. This is also a test dataset, in reality the documents I’ll be manipulating are much more complex.

When I am filtering { "people.age": { $in: [24] } } I am getting full this document. But I want to see only the matching object. Is it possible to show only the matching object? If you kindly explain me it will be helpful for me.

I appreciate any help in such case. Collection in MongoDB (now only 2 documents for demonstration purpose):

{ 
    "_id" : ObjectId("62684847e9594c65cbaa5d85"), 
    "agentId" : NumberInt(1), 
    "agentName" : "Yardi Gaondi", 
    "policyList" : [
        {
            "receivedDate" : ISODate("2022-03-23T04:46:15.000+0000"), 
            "policyStatusDetail" : [
                {
                    "policyStsCode" : NumberInt(7), 
                    "policiesArray" : [
                        {
                            "policyDetailedCode" : NumberInt(1), 
                            "policyStatusDate" : ISODate("2022-02-20T04:46:15.000+0000")
                        }, 
                        {
                            "policyDetailedCode" : NumberInt(2), 
                            "policyStatusDate" : ISODate("2022-01-19T05:46:15.000+0000")
                        }
                    ]
                }
            ]
        }, 
        {
            "receivedDate" : ISODate("2022-03-23T04:46:15.000+0000"), 
            "policyStatusDetail" : [
                {
                    "policyStsCode" : NumberInt(7), 
                    "policiesArray" : [
                        {
                            "policyDetailedCode" : NumberInt(3), 
                            "policyStatusDate" : ISODate("2022-01-16T04:46:15.000+0000")
                        }
                    ]
                }
            ]
        }, 
        {
            "receivedDate" : ISODate("2022-02-23T04:46:15.000+0000"), 
            "policyStatusDetail" : [
                {
                    "policyStsCode" : NumberInt(7), 
                    "policiesArray" : [
                        {
                            "policyDetailedCode" : NumberInt(1), 
                            "policyStatusDate" : ISODate("2022-01-20T04:46:15.000+0000")
                        }, 
                        {
                            "policyDetailedCode" : NumberInt(2), 
                            "policyStatusDate" : ISODate("2022-01-19T05:46:15.000+0000")
                        }
                    ]
                }
            ]
        }
    ]
}
{ 
    "_id" : ObjectId("62684847e9594c65cbaa5d86"), 
    "agentId" : NumberInt(2), 
    "agentName" : "Michelle Hazandi", 
    "policyList" : [
        {
            "receivedDate" : ISODate("2022-04-10T04:46:15.000+0000"), 
            "policyStatusDetail" : [
                {
                    "policyStsCode" : NumberInt(7), 
                    "policiesArray" : [
                        {
                            "policyDetailedCode" : NumberInt(2), 
                            "policyStatusDate" : ISODate("2022-04-09T05:46:15.000+0000")
                        }
                    ]
                }
            ]
        }, 
        {
            "receivedDate" : ISODate("2022-03-10T04:46:15.000+0000"), 
            "policyStatusDetail" : [
                {
                    "policyStsCode" : NumberInt(7), 
                    "policiesArray" : [
                        {
                            "policyDetailedCode" : NumberInt(2), 
                            "policyStatusDate" : ISODate("2022-03-09T05:46:15.000+0000")
                        }
                    ]
                }
            ]
        }
    ]
}

I appreciate any help in such case. Collection in MongoDB (now only 2 documents for demonstration purpose):

{ 
    "_id" : ObjectId("62684847e9594c65cbaa5d85"), 
    "agentId" : NumberInt(1), 
    "agentName" : "Yardi Gaondi", 
    "policyList" : [
        {
            "receivedDate" : ISODate("2022-03-23T04:46:15.000+0000"), 
            "policyStatusDetail" : [
                {
                    "policyStsCode" : NumberInt(7), 
                    "policiesArray" : [
                        {
                            "policyDetailedCode" : NumberInt(1), 
                            "policyStatusDate" : ISODate("2022-02-20T04:46:15.000+0000")
                        }, 
                        {
                            "policyDetailedCode" : NumberInt(2), 
                            "policyStatusDate" : ISODate("2022-01-19T05:46:15.000+0000")
                        }
                    ]
                }
            ]
        }, 
        {
            "receivedDate" : ISODate("2022-03-23T04:46:15.000+0000"), 
            "policyStatusDetail" : [
                {
                    "policyStsCode" : NumberInt(7), 
                    "policiesArray" : [
                        {
                            "policyDetailedCode" : NumberInt(3), 
                            "policyStatusDate" : ISODate("2022-01-16T04:46:15.000+0000")
                        }
                    ]
                }
            ]
        }, 
        {
            "receivedDate" : ISODate("2022-02-23T04:46:15.000+0000"), 
            "policyStatusDetail" : [
                {
                    "policyStsCode" : NumberInt(7), 
                    "policiesArray" : [
                        {
                            "policyDetailedCode" : NumberInt(1), 
                            "policyStatusDate" : ISODate("2022-01-20T04:46:15.000+0000")
                        }, 
                        {
                            "policyDetailedCode" : NumberInt(2), 
                            "policyStatusDate" : ISODate("2022-01-19T05:46:15.000+0000")
                        }
                    ]
                }
            ]
        }
    ]
}
{ 
    "_id" : ObjectId("62684847e9594c65cbaa5d86"), 
    "agentId" : NumberInt(2), 
    "agentName" : "Michelle Hazandi", 
    "policyList" : [
        {
            "receivedDate" : ISODate("2022-04-10T04:46:15.000+0000"), 
            "policyStatusDetail" : [
                {
                    "policyStsCode" : NumberInt(7), 
                    "policiesArray" : [
                        {
                            "policyDetailedCode" : NumberInt(2), 
                            "policyStatusDate" : ISODate("2022-04-09T05:46:15.000+0000")
                        }
                    ]
                }
            ]
        }, 
        {
            "receivedDate" : ISODate("2022-03-10T04:46:15.000+0000"), 
            "policyStatusDetail" : [
                {
                    "policyStsCode" : NumberInt(7), 
                    "policiesArray" : [
                        {
                            "policyDetailedCode" : NumberInt(2), 
                            "policyStatusDate" : ISODate("2022-03-09T05:46:15.000+0000")
                        }
                    ]
                }
            ]
        }
    ]
}

So collection consists of 2 documents, in each document there is a field “policyList” which is an array of objects. In first document policyList contains 3 objects, in second document only two. So I have to filter documents in this collection in such way: 1) I need to keep in the “policyList” array only those objects that match to such condition: one of the fields are in a certain time interval, which means that must be a match in at least one of the fields(first field is “receivedDate” - which is located in the array at the first level of nesting - “policyList” or second field “policyStatusDate” - which is located in the array at the third level of nesting - “policiesArray”, and if there is a match in one of abovementioned fields we return from the “policyList” full object, that means we cannot throw away from “policiesArray” any object). One match is enough, for example if I want to see documents from 01/02/2022 to 01/03/2022 I expect to see in the first document in the “policyList” array only first and third object, because first object matches by “policyStatusDate” - 20/02/2022 (match in one of the object in “policiesArray” is enough) and third object matches by “receivedDate” - 23/02/2022) and second object in first document I don’t expect to see because both dates in this document are not in the period from 01/02/2022 to 01/03/2022; 2) if there are no any matches among objects in “policyList”, that means “policyList” must be empty after filtering, and in such case we don’t need to return this document. For example if I request for a documents from 01/02/2022 to 01/03/2022 I’m not expecting to see second document, because no “policyStatusDate” and no “receivedDate” are in requested time interval.

My aggregation request:

db.getCollection("offers2").aggregate([
{
  $project: {
  "agentId": "$agentId",
  "agentName": "$agentName",
  "policyList": {
      $filter: {
         input: "$policyList",
         as: "item",
         cond: {
         "$or": [
            {
              "$and": [
                { "$gte": [ "$item.receivedDate", ISODate("2022-02-01") ] },
                { "$lte": [ "$item.receivedDate", ISODate("2022-03-01") ] }
              ]
            }, 
            {
               $and": [
                { "$gte": [ "$item.policyStatusDetail.policiesArray.policyStatusDate", ISODate("2022-02-01") ] },
                { "$lte": [ "$item.policyStatusDetail.policiesArray.policyStatusDate", ISODate("2022-03-01") ] }
              ]
            }
         ]
         }
      }
  }
  }
},
{
  $project: {
  "agentId": "$agentId",
  "agentName": "$agentName",
  "policyList": "$policyList",
  "numPoliciesPerDate": {
    $cond: {  
      if: {$isArray: "$policyList"}, then: {$size: "$policyList"}, else: "0"
     }
    }
  }
  },
  {
  $match: {
    "numPoliciesPerDate": {$gte: 1}
     }
  }
]) 

After running this query I expect to receive first document with first object and third object in “policyList” array, but I received only third object (there is matching by “receivedDate” in this object ). Result:

{ 
    "_id" : ObjectId("62684847e9594c65cbaa5d85"), 
    "agentId" : NumberInt(1), 
    "agentName" : "Yardi Gaondi", 
    "policyList" : [
        {
            "receivedDate" : ISODate("2022-02-23T04:46:15.000+0000"), 
            "policyStatusDetail" : [
                {
                    "policyStsCode" : NumberInt(7), 
                    "policiesArray" : [
                        {
                            "policyDetailedCode" : NumberInt(1), 
                            "policyStatusDate" : ISODate("2022-01-20T04:46:15.000+0000")
                        }, 
                        {
                            "policyDetailedCode" : NumberInt(2), 
                            "policyStatusDate" : ISODate("2022-01-19T05:46:15.000+0000")
                        }
                    ]
                }
            ]
        }
    ], 
    "numPoliciesPerDate" : NumberInt(1)
}

So it seems that condition

 $and": [
                { "$gte": [ "$item.policyStatusDetail.policiesArray.policyStatusDate", ISODate("2022-02-01") ] },
                { "$lte": [ "$item.policyStatusDetail.policiesArray.policyStatusDate", ISODate("2022-03-01") ] }
              ]

doesn’t work correct. I think it’s because it may be impossible to use dot notation when we work with nested arrays. So maybe somebody could help me to fix this aggregation query so that the requirements I described at the beginning will be fullfiled and in our case first object from “policyList” in first document will also be returned?