CODEWITHSUNDEEP
node.jsmongodbexpressmongooseaggregation-framework
Hacking Node
Hacking Node

Reputation: 442

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

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:

  1. http://mongoosejs.com/docs/api.html#model_Model.mapReduce
  2. http://mongoosejs.com/docs/api.html#aggregate_Aggregate
  3. https://docs.mongodb.com/manual/core/map-reduce/
  4. https://docs.mongodb.com/manual/tutorial/map-reduce-examples/

But can't utilize it for my case :(

Any advice will be much appreciated. Thanks!

Upvotes: 3

Views: 3391

Answers (1)

chridam
chridam

Reputation: 103455

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);
});

Upvotes: 4

Related Questions