Node with mongoose aggregate and sum nested fields

Question

When I do this query against my collection...

models.Project.find(function(err, result) {
    //result = doc below
}).populate('media')

... I get this result:

{ 
  _id: 57f36baa6cf34d079c8474a0,
  code: 'ZMIA',
  __v: 0,
  media:[
    { 
      _id: 57f36bb26cf34d079c847766,
      project_code: 'ZMIA',
      amount: 228,
      __v: 0 
    },
    { 
      _id: 57f36bb26cf34d079c84775c,
      project_code: 'ZMIA',
      amount: 250,
      __v: 0 
    } 
  ]
},
{ 
  _id: 57f36baa6cf34d079c8474a1,
  code: 'ZMJU',
  __v: 0,
  media: [] 
}

media is a ref field. How can I aggregate the nested media objects (if present) to $sum the amount field and group the result by project_code ?

It is unclear what the expected result is. Can you please elaborate? — Sede
– Sede, Commented Oct 4, 2016 at 18:01

chridam · Accepted Answer · 2016-10-04 14:16:29Z

7

You can use the aggregation framework where you run an aggregation pipeline that consists of an initial $unwind pipeline that will denormalize the media field since it is an array and then a $lookup operator to do a left join to the collection that has the media refs. A further $unwind operator is needed to flatten the array field produced as a result of the join and then do a $group operator pipeline on the flattened documents to produce the desired result.

Running the following pipeline should work for you:

models.Project.aggregate([
    { "$unwind": "$media" },
    {
        "$lookup": {
            "from": "media", // <-- collection to join
            "localField": "media",
            "foreignField": "_id",
            "as": "media_joined"
        }
    },
    { "$unwind": "$media_joined" },
    {
        "$group": {
            "_id": "$media_joined.project_code",
            "total": { "$sum": "$media_joined.amount" }
        }
    }
], function(err, result){
    console.log(result);
})

answered Oct 4, 2016 at 14:16

chridam

104k26 gold badges246 silver badges243 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dave Over a year ago

Thank you! If I could upvote 100 times I would. Excellent explanation with code working first try.

nurgasemetey Over a year ago

I follow mongo questions and I see that you answer a lot on this topic. Do you work there? :)

chridam Over a year ago

@nurgasemetey No :) Just developed a very keen interest in the technology and an avid supporter of others regarding questions and technical help.

Collectives™ on Stack Overflow

Node with mongoose aggregate and sum nested fields

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related