How to sum several months of data separately in a MongoDB collection

Question

I'm trying to tally a field in a sub-array of a collection and I want to do it for each month. I had this working in Mongo 2.6 but recently upgrading to 3.0.12 has cause some erroneous results in the query. It almost seems like the sum is not getting reset for the several queries.

So currently I am doing twelve queries async and waiting for them all to complete. Again this was working in 2.6. My table structure is like this:

{
"_id" : ObjectId("<id>"),
"userId" : ObjectId("<id>"),
"accountId" : "1234567890",
"transactionId" : "22222222333",
"date" : ISODate("2016-09-08T04:00:00.000Z"),
"description" : "SUPERCOOL STORE",
"amount" : -123.45,
"allocations" : [ 
    {
        "jarId" : ObjectId("566faf1937af9ae11ef84bc4"),
        "amount" : -100
    }, 
    {
        "jarId" : ObjectId("566faf1937af9ae11ef84bc1"),
        "amount" : -23.45
    }
],
"reconciled" : true,
"tally" : true,
"split" : true
}

And this is my code:

var getTransactionAggregate = function (userId, month) {
  var deferred = q.defer();
  var nextMonth = moment(month).add(1, 'M');

  Transactions.aggregate([
    {$match: {
      userId: userId,
      tally: true,
      date: {
        $gte: month.toDate(),
        $lt: nextMonth.toDate()
      }
    }},
    {$project: { _id: 0, allocations: 1 } },
    {$unwind: '$allocations'},
    {$group: {_id: '$allocations.jarId', total: {$sum: '$allocations.amount'}}}
  ]).then(function(data) {
    deferred.resolve(data);
  })
  .catch(function (err) {
    logger.error(err);
    deferred.reject(err);
  });

  return deferred.promise;
};

summaryReport = function (req, res) {
  Jars.aggregate([
    { $match: {userId: new ObjectID(req.user._id)} },
    { $sort: {name: 1} }
  ])
  .then(function (jars) {
    var month = moment(moment().format('YYYY-MM') + '-01T00:00:00.000');
    var i, j;
    var promises = [];

    for (i = 0; i < 12; i++) {
      promises.push(getTransactionAggregate(new ObjectID(req.user._id), month));
      month.add(-1, 'M');
    }

    q.allSettled(promises).then(function (data) {
      for (i = 0; i < data.length; i++) {
        // data[i].value here is returned incorrectly from the queries

        ........
    });
  });
};

So essentially what is happening is the first month includes the correct data but it appears that the sum continues to include data from all the previous months. If I break down the query the correct transactions are returned in the date range, and the unwind is working as well. Just when the groupBy step seems to be the culprit. The same logic worked fine before I upgraded Mongo to 3.0.12.

Is there a better way to execute this query in one shot or is doing the twelve queries the best way?

Answer 1

It ended up being related to the match although not because of the $and case mentioned in the above answer. It had to do with the date matching, I'm guessing the moment object.toDate() does not return the same date object as when you use new Date(), although I thought they were the same.

Anyway the working logic looks like this:

Transactions.aggregate([
  {$match: {
    userId: userId,
    tally: true,
    $and: [
      { date: { $gt : new Date(month.toISOString()) } },
      { date: { $lt: new Date(nextMonth.toISOString()) } }
    ]
  }},
  {$unwind: '$allocations'},
  {$group: {_id: '$allocations.jarId', total: {$sum: '$allocations.amount'}}}
])

Credit to Date query with ISODate in mongodb doesn't seem to work and @Leif for pointing me in the right direction.