MongoDB how to get count of records by 30 hours ago, 48 hour ago, and one month ago?

Question

I have bellow structure:

{
   "_id" : ObjectId("58d8bcf01caf4ebddb842855"),
   "publishDate" : ISODate("2017-03-14T00:00:00.000Z")
},
{
   "_id" : ObjectId("58e87ed516b51f33ded59eb3"),
   "publishDate" : ISODate("2017-04-14T00:00:00.000Z")
},
{
   "_id" : ObjectId("58eb5b01c21fbad780bc74b6"),
   "publishDate" : ISODate("2017-04-09T00:00:00.000Z")
},
{
   "_id" : ObjectId("58eb5b01c21fbad780bc74b9"),
   "publishDate" : ISODate("2017-04-12T00:00:00.000Z")
}

Now i want to take count of records that are published before one month ago, count of records that are published before 48 hour ago, and count of records that are published 30 hour ago.

My current try is:

db.JobPosting.aggregate([
   {$project:{
      "thirtyDaysAgo":{"publishDate":{$lt:["$publishDate",new Date(ISODate().getTime() - 1000*60*60*24*30)]}}, 
      "fourtyEightHourAgo":{"publishDate":{$lt:["$publishDate",new Date(ISODate().getTime() - 1000*60*60*48)]}}, 
      "thirtyHourAgo":{"publishDate":{$lt:["$publishDate",new Date(ISODate().getTime() - 1000*60*60*30)]}}
  }}, 
  {$group:{
      _id:{thirtyDaysAgo:"$thirtyDaysAgo", fourtyEightHourAgo:"$fourtyEightHourAgo", thirtyHourAgo:"$thirtyHourAgo"}, 
      "count":{$sum:1}
  }}
])

But the result is wrong:

{ "_id" : { "thirtyDaysAgo" : { "publishDate" : false }, "fourtyEightHourAgo" : { "publishDate" : true }, "thirtyHourAgo" : { "publishDate" : true } }, "count" : 1 }
{ "_id" : { "thirtyDaysAgo" : { "publishDate" : false }, "fourtyEightHourAgo" : { "publishDate" : false }, "thirtyHourAgo" : { "publishDate" : false } }, "count" : 1 }
{ "_id" : { "thirtyDaysAgo" : { "publishDate" : true }, "fourtyEightHourAgo" : { "publishDate" : true }, "thirtyHourAgo" : { "publishDate" : true } }, "count" : 1 }

What i want is: {moreThanThirtyDayAgo:{count:1}, moreThanFourtyEightHourAgo:{count:2}, moreThanThirtyHourAgo:{count:1}}

Answer 1

Use the $cond conditional expression to create a binary decision tree that will feed the evaluations to the $sum accumulator. Take for instance the following pipeline:

var dateThirtyHoursAgo = new Date();
dateThirtyHoursAgo.setHours(dateThirtyHoursAgo.getHours()-30);

var dateFourtyEightHoursAgo = new Date();
dateFourtyEightHoursAgo.setHours(dateFourtyEightHoursAgo.getHours()-48);

var dateMonthAgo = new Date();
dateMonthAgo.setMonth(dateMonthAgo.getMonth()-1);

var pipeline = [
    {
        "$group": {
            "_id": null,
            "thirtyHourAgo": {
                "$sum": {
                    "$cond": [ 
                        { "$gte": [ "$publishDate", dateThirtyHoursAgo ] }, 
                        1, 0 
                    ]
                }
            },
            "fourtyEightHourAgo": {
                "$sum": {
                    "$cond": [ 
                        { "$gte": [ "$publishDate", dateFourtyEightHoursAgo ] }, 
                        1, 0 
                    ]
                }
            },
            "thirtyDaysAgo": {
                "$sum": {
                    "$cond": [ 
                        { "$gte": [ "$publishDate", dateMonthAgo ] }, 
                        1, 0 
                    ]
                }
            }
        }
    }
];

db.JobPosting.aggregate(pipeline);