CODEWITHSUNDEEP
mongodbmongooseaggregation-framework
Cog
Cog

Reputation: 1705

MongoDB query - aggregates and embedded documents

Need some help writing a MongoDB query.

Background: I'm building an app that keeps track of donations. I creating an API in ExpressJS, and I am using Mongoose to hook up to MongoDB.

I have a MongoDB collection called Donations that looks like this:

[
    {
        donor: 123,
        currency: 'CAD',
        donationAmount: 50
    },
    {
        donor: 123,
        currency: 'USD',
        donationAmount: 50
    },
    {
        donor: 789,
        currency: 'CAD',
        donationAmount: 50
    },
    {
        donor: 123,
        currency: 'CAD',
        donationAmount: 50
    }
]

For each donor, I need to sum up the total amount of donations per currency. Ideally I want a single MongoDB query that would produce the following dataset. (I'm flexible on the structure ... my only requirement are that in the results, 1) each donor has one and only one document, and 2) this document contains the summed total of each currency type)

[
    {
        donor: 123,
        donations: [
            {
                CAD : 100,
            },
            {
                USD : 50
            }
        ]
    },
    {
        donor: 789,
        donations: [
            {
                CAD: 50
            }
        ]
    },
]

Any ideas on the best way to do this? My solution right now is pretty ugly - I haven't been able to achieve it without doing multiple queries.

Upvotes: 1

Views: 41

Answers (1)

mickl
mickl

Reputation: 49945

You can run $group twice and use $arrayToObject to build your keys dynamically:

Model.aggregate([
    { $group: { _id: { donor: "$donor", currency: "$currency" }, sum: { $sum: "$donationAmount" } } },
    { $group: { _id: "$_id.donor", donations: { $push: { $arrayToObject: [[{ k: "$_id.currency", v: "$sum" }]] } } } },
    { $project: { _id: 0, donor: "$_id", donations: 1 } }
])

Mongo Playground

Upvotes: 2

Related Questions