Aggregating child objects within an array by root level data

Hi all,
I’m having trouble with an easy to explain but thus far difficult to achieve (for me) aggregation task.
I have a Bills collection that looks like this:

    {
        "BilledTo": {
            "Address": {...},
            "Name": "Steve's Rad Brokerage"
        },
       ... inconsequential fields ...
        "Payments": [
            {
                "AmountBillable": 16630.0,
                "AmountReceivable": 13719.75,
                "AmountReceived": 0,
                "BillDate": "2022-02-01",
                "DueDate": "2022-02-16",
                "Id": "an id",
                "Notes": null
            }
        ]
    }

What I’d like to do is group all the payments with BillDate = some date by the BilledTo.Name.
For example for 2022-02-01 I might get something like:

{
    "Steve's Rad Brokerage": [
         A payment object,
         Another payment object
    ],
    "Some Other BilledTo.Name": [
        Yet another payment object
    ]
}

I have gotten a good amount of the way there with unwind and a simple match, but getting the resulting payments to group by BilledTo.Name is turning my brain inside out.

Any help would be much appreciated. Thanks!

My approach would be:

A match stage for

match = { "$match" : {
    "Payments.BillDate" : "2022-01-01"
} }

The I would filter Payments with

filter = { "$set" : {
    "Payments" : { "$filter" : {
        "input" : "$Payments" ,
        "as" : "payment" ,
        "cond" : { "$eq" : [ "$$payment.BillDate" , "2022-02-01" ] }
    } }
} }

The I would unwind the filtered Payments array with

{ "$unwind" : "$Payments" }

The data is now ready for grouping using:

{ "$group" : {
    "_id" : "$BilledTo.Name" ,
    "Payments" : { "$push" : "$Payments" } 
} }

You might need a final $project to produce the exact format you wish.

I recommend you store your dates using the Date data type. It takes less spaces, it is faster and there is rich date specific API.

Thanks @steevej! This works great and has provided a nice framework to do a few fancier things with the data. FYI I am using the native date type, just easier to show as a string here :slight_smile:

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.