How to get aggregated sum of values in an array of mongoose subdocuments when query parent?

Question

I'm trying to build some advanced hello world app on top of express and mongoose. Assume I have next Schemas:

const pollOptionsSchema = new Schema({
  name: String,
  votes: {
    type: Number,
    default: 0
  }
});

const pollSchema = new Schema({
  name: String,
  dateCreated: { type: Date, default: Date.now },
  author: { type: Schema.Types.ObjectId },
  options: [pollOptionsSchema]
});

And when I simply call

Poll.findOne({_id: req.params.id}).exec((err, data) => {
  if (err) console.log(err);
  // I receive next data:
  // { _id: 58ef3d2c526ced15688bd1ea,
  //   name: 'Question',
  //   author: 58dcdadfaea29624982e2fc6,
  //   __v: 0,
  //   options:
  //    [ { name: 'stack', _id: 58ef3d2c526ced15688bd1ec, votes: 5 },
  //      { name: 'overflow', _id: 58ef3d2c526ced15688bd1eb, votes: 3 } ],
  //   dateCreated: 2017-04-13T08:56:12.044Z }
});

The question is how I could receive same data + aggregated number of votes (i.e 8 in case above) after calling some method on Model level, for example:

  // I want to receive:
  // { _id: 58ef3d2c526ced15688bd1ea,
  //   name: 'Question',
  //   author: 58dcdadfaea29624982e2fc6,
  //   __v: 0,
  //   totalNumberOfVotes: 8,
  //   options:
  //    [ { name: 'stack', _id: 58ef3d2c526ced15688bd1ec, votes: 5 },
  //      { name: 'overflow', _id: 58ef3d2c526ced15688bd1eb, votes: 3 } ],
  //   dateCreated: 2017-04-13T08:56:12.044Z }

Or maybe I need to implement some extra method on document level i.e (data.aggregate)?

I've already reviewed:

But can't utilize it for my case :(

Any advice will be much appreciated. Thanks!

for now I'm just looping through options and gather total in variable. let sumVotes = 0; data.options.forEach((el) => { sumVotes += el.votes }); — Hacking Node
– Hacking Node, Commented Apr 13, 2017 at 9:11

chridam · Accepted Answer · 2017-04-13 09:44:08Z

Use $reduce operator within an $addFields pipeline to create the totalNumberOfVotes field. In your aggregate pipeline, the first step is the $match which filters the document stream to allow only matching documents to pass unmodified into the next pipeline stage and uses standard MongoDB queries.

Consider running the following aggregate operation to get the desired result:

Poll.aggregate([
    { "$match": { "_id": mongoose.Types.ObjectId(req.params.id) } },
    {
        "$addFields": {
            "totalNumberOfVotes": {
                "$reduce": {
                    "input": "$options",
                    "initialValue": 0,
                    "in": { "$add" : ["$$value", "$$this.votes"] }
                }
            }
        }
    }
]).exec((err, data) => {  
    if (err) console.log(err);
    console.log(data);
});

NB: The above will work for MongoDB 3.4 and greater.

For other earlier versions you would need to $unwind the options array first before grouping the denormalised documents within a $group pipeline step and aggregating with the accumulators $sum, $push and $first.

The following example shows this approach:

Poll.aggregate([
    { "$match": { "_id": mongoose.Types.ObjectId(req.params.id) } },
    { "$unwind": { "path": "$options", "preserveNullAndEmptyArrays": true } },
    {
        "$group": {
            "_id": "$_id",
            "totalNumberOfVotes": { "$sum": "$options.votes" },
            "options": { "$push": "$options" },
            "name": { "$first": "$name" },
            "dateCreated": { "$first": "$dateCreated" },
            "author": { "$first": "$author" }
        }
    }
]).exec((err, data) => {  
    if (err) console.log(err);
    console.log(data);
});

Collectives™ on Stack Overflow

How to get aggregated sum of values in an array of mongoose subdocuments when query parent?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related