CODEWITHSUNDEEP
javascriptnode.jsmongodbmongodb-queryaggregation-framework
Matthew
Matthew

Reputation: 2246

Mongoose — Create new property with $sum using aggregation

I'm trying to add a new field in all documents that contain the sum of an array of numbers.

Here is the Schema (removed irrelevant fields for brevity):

 var PollSchema = new Schema(
  {
    votes: [Number]
  }
 );

I establish the model:

PollModel = mongoose.model('Poll', PollSchema);

And I use aggregation to create a new field that contains the sum of the votes array.

PollModel.aggregate([
 {
  $project: {
    totalVotes: { $sum: "$votes"}
  }
 }
]);

When I startup my server, I get no errors; however, the totalVotes field hasn't been created. I used this documentation to help me. It similarly uses the $sum operator and I did it exactly like the documentation illustrates, but no results.

Upvotes: 1

Views: 1781

Answers (4)

Matthew
Matthew

Reputation: 2246

@Blakes Seven and @Volodymyr Synytskyi helped me arrive to my solution! I also found this documentation particularly helpful.

PollModel.aggregate(
[
    { '$unwind': '$votes' },
    { '$group': {
        '_id': '$_id',
        'totalVotes': { '$sum': '$votes' }
    }}
],
function(err,results) {
    // console.log(results);
    results.forEach(function(result){
        var conditions = { _id: result._id }, 
            update = { totalVotes: result.totalVotes }, 
            options = { multi: true };

        PollModel.update(conditions, update, options, callback);

        function callback (err, numAffected) {
            if(err) {
                console.error(err);
                return;
            } else {
                // console.log(numAffected);
            }
        }
    });
}
);

Upvotes: 0

Volodymyr Synytskyi
Volodymyr Synytskyi

Reputation: 4055

MongoDb aggregation doesn't save its result into database. You just get the result of aggregation inline within a callback. So after aggregation you would need to do multi update to your database:

PollModel.aggregate([
{
    $project: { totalVotes: { $sum: "$votes"} }
}]).exec( function(err, docs){
        // bulk is used for updating all records within a single query
        var bulk = PollModel.collection.initializeUnorderedBulkOp();

        // add all update operations to bulk
        docs.forEach(function(doc){
            bulk.find({_id: doc._id}).update({$set: {totalVotes: doc.totalVotes}});
        });

        // execute all bulk operations
        bulk.execute(function(err) {
        });
    })
});

Upvotes: 3

Vishnu
Vishnu

Reputation: 12323

You don't have totalVotes in your schema. Just try the below code.

var PollSchema = new Schema(
  {
    votes: [Number],
    totalVotes: Number
  }
 );

PollModel.aggregate([
 {
  $project: {
    totalVotes: { $sum: "$votes"}
  }
 }
]);

or

resultData.toJSON();

Upvotes: 0

Blakes Seven
Blakes Seven

Reputation: 50436

Unfortunately this does not work as you think it does because "votes" is actually an array of values to start with, and then secondly because $sum is an accumulator operator for usage in the $group pipeline stage only.

So in order for you to get the total of the array as another property, first you must $unwind the array and then $group together on the document key to $sum the total of the elements:

PostModel.aggregate(
    [
        { "$unwind": "$votes" },
        { "$group": {
            "_id": "$_id",
            "anotherField": { "$first": "$anotherField" },
            "totalVotes": { "$sum": "$votes" }
        }}
    ],
    function(err,results) {

    }
);

Also noting here another accumulator in $first would be necessary for each additional field you want in results as $group and $project only return the fields you ask for.

Generally though this is better to keep as a property within each document for performance reasons, as it's faster than using aggregate. So to do this just increment a total each time you $push to an array by also using $inc:

PostModel.update(
    { "_id": id },
    {
        "$push": { "votes": 5 },
        "$inc": { "totalVotes": 5 }
    },
    function(err,numAffected) {

    }
);

In that way the "totalVotes" field is always ready to use without the overhead of needing to deconstruct the array and sum the values for each document.

Upvotes: 2

Related Questions