CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-frameworkloopback
jones
jones

Reputation: 1453

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

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}}

Upvotes: 1

Views: 124

Answers (1)

chridam
chridam

Reputation: 103425

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);

Upvotes: 2

Related Questions