Mongoose: Populate field in collection from other collection

Question

I have 2 Mongoose collections: ExpenseCategory and Expense

var ExpenseCategorySchema = new Schema({
    name: String,
    totalSpentInThisMonth: Number
});
mongoose.model('ExpenseCategory', ExpenseCategorySchema);

var ExpenseSchema = new Schema({
    expenseCategoryId: {type: Schema.Types.ObjectId, ref: 'ExpenseCategory'},
    amount: Number,
    date: Date
});
mongoose.model('Expense', ExpenseSchema);

There is a GET api call written in Node.js to return all ExpenseCategory items.

appRouter.route('/expensecatgories')
  .get(function(req, res){
      ExpenseCategory.find({}, function (expenseCategories) {
        res.json(expenseCategories);
      });
  });

In the above GET method I want to populate field totalSpentInThisMonth in each expenseCategories item before returning. This field needs to be calculated as a sum of all expense.amount where expense.expenseCategoryId matched the expenseCategory.id and expense.date is in current month.

How can I populate the field totalSpentInThisMonth before returning expenseCategories?

Answer 1

Use the .aggregate() method from the aggregation framework for this. You would need to first construct dates to use as your date range query for documents whose date falls within the current month, thus you need to calculate the first and last days of the month date objects. These dates would be used in the $match pipeline to filter out the documents that are not in the current month.

The next pipeline stream would be the $group stage which groups the incoming documents by the expenseCategoryId key so that you may calculate the total spent in the current month using the accumulator operator $sum.

The following code implements the above:

appRouter.route('/expensecatgories').get(function(req, res){
    var today = new Date(), y = today.getFullYear(), m = today.getMonth();
    var firstDay = new Date(y, m, 1);
    var lastDay = new Date(y, m + 1, 0);
    var pipeline = [
        {
            "$match": {
                "date": { "$gte": firstDay, "$lt": lastDay }
            }
        },
        {
            "$group": {
                "_id": "$expenseCategoryId",
                "totalSpentInThisMonth": { "$sum": "$amount" }
            }
        }
    ];

    Expense.aggregate(pipeline, function (err, result){     
        if (err) throw err;
        var categories = result.map(function(doc) { return new ExpenseCategory(doc) });
        Expense.populate(categories, { "path": "expenseCategoryId" }, function(err, results) {
            if (err) throw err;
            console.log(JSON.stringify(results, undefined, 4 ));
            res.json(results);
        });
    });        
});