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?

Ori Popowski · Answer 1 · 2016-09-16T23:18:56.953

1

It seems to be a problem during the $match phase. Your date field has two expressions, and this scenario you need to use the $and operator, as specified in the docs:

MongoDB provides an implicit AND operation when specifying a comma separated list of expressions. Using an explicit AND with the $and operator is necessary when the same field or operator has to be specified in multiple expressions.

So it becomes:

{$match: {
    userId: userId,
    tally: true,
    $and: [
        { date: { $gte : month.toDate() } },
        { date: { $lt: nextMonth.toDate() } }
    ]
}}

edited Sep 16 '16 at 23:18

answered Sep 16 '16 at 23:12

Ori Popowski

10,432
15
57
79

Thanks for you quick response! You are right it must have something to do with the match, I just brought the logic back to first principles and with only 1 item per month and 3 months total, the first month query returns all three items, the second month returns only 2 and the last month returns the correctly matched one item. However, altering the match logic the way you suggested did not seem to help. Neither did putting all my criteria within the $and. – Eric Sep 17 '16 at 02:44

score 0 · Accepted Answer · edited May 23 '17 at 12:08

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.

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

2 Answers2