mongodb - Aggregate $group for multiple date ranges

ID : 274360

viewed : 41

Tags : mongodbmongodb-queryaggregation-frameworkdate-rangemongodb





Top 4 Answer for mongodb - Aggregate $group for multiple date ranges

vote vote

94

You need to conditionally determine the grouping key based on where the current date falls in between the range. This is basically achieved via $cond with nested condtions and the logical variant of $lt:

// work out dates somehow var today = new Date(),     oneDay = ( 1000 * 60 * 60 * 24 ),     thirtyDays = new Date( today.valueOf() - ( 30 * oneDay ) ),     fifteenDays = new Date( today.valueOf() - ( 15 * oneDay ) ),     sevenDays = new Date( today.valueOf() - ( 7 * oneDay ) );  db.collection.aggregate([     { "$match": {         "date": { "$gte": thirtyDays }     }},     { "$group": {         "_id": {             "$cond": [                 { "$lt": [ "$date", fifteenDays ] },                 "16-30",                 { "$cond": [                     { "$lt": [ "$date", sevenDays ] },                     "08-15",                     "01-07"                 ]}             ]         },         "count": { "$sum": 1 },         "totalValue": { "$sum": "$value" }     }} ]) 

As $cond is a ternary operator, the first condition is evaluated to see if the condition is true, and when true the second argument is returned otherwise the third is returned when false. So by nesting another $cond in the false case you get the logical test on where the date falls, either "less that the 15 day date" which means its in the oldest range, or "less than 7 days" which means the middle range, or of course it's in the newest range.

I'm just prefixing the numbers here less than 10 with a 0 so it gives you something to sort on if you want, since the output of "keys" in $group is not in itself ordered.

But that is how you do this in a single query. You just work out what the grouping key should be based on where the date falls and accumulate for each key.

vote vote

85

The first step will be creating date objects that represent your range. Let's say you want to run your aggregation operation for the dange 8-15 days ago, this means you need two date objects, lets say start and end. start will hold the date a day ago and end will hold the date 8 days ago. Creating these date objects is easy as setting them to the numbers of days previous by subtracting n from the date where n is the number of days ago:

var start = new Date(); start.setDate(start.getDate() - 8);  var end = new Date(); end.setDate(end.getDate() - 15); 

or substracting from the timestamp milliseconds using .getTime() method returns a standard JavaScript timestamp (milliseconds since Jan 1/1970) on which you can use regular math operations, and fed back to the Date object directly:

var today = new Date(); var start = new Date(today.getTime() - 8*24*60*60*1000); var end = new Date(today.getTime() - 15*24*60*60*1000); 

Now that you have the date objects, you can then use them as the $match criteria, utilising the $lte and $gte comparison operators:

var pipeline = [     {         "$match": {             "date": { "$gte": start, "$lte": end }         }     } ] 

Running the aggregation at this stage will give you all the documents that have the date falling in the range 8-15 days ago,

db.aggregate(pipeline); 

which is equivalent to the find() query:

db.collection.find({     "date": { "$gte": start, "$lte": end } }); 

Now, to the next pipeline stage, you would need to create an aggregation operation that specifies a group _id of null, calculating the total value and the counts for all documents in the collection using the $sum accumulator operator:

var pipeline = [     {         "$match": {             "date": { "$gte": start, "$lte": end }         }     },     {         "$group": {             "_id": null,             "totalValues": { "$sum": "$value" },             "count": { "$sum": 1 }         }     } ]  db.collection.aggregate(pipeline); 

You may even go further to create a generic function that returns the actual total from the above aggregation operation that takes in two parameters, the start value of the date range and the end:

var getTotalValues = function(start, end){     var today = new Date();     var startDate = new Date(today.getTime() - start*24*60*60*1000);     var endDate = new Date(today.getTime() - end*24*60*60*1000);          var pipeline = [             {                 "$match": {                     "timestamp": { "$gte": startDate, "$lte": endDate }                 }             },             {                 "$group": {                     "_id": null,                     "totalValues": { "$sum": "$value" },                                 "count": { "$sum": 1 }                 }             }         ],         resultArray = db.collection.aggregate(pipeline).toArray();      return resultArray[0].totalValues; }  var total = getTotalValues(1, 8); printjson(total); // prints the total 
vote vote

76

This is a nice use case for the $bucket stage, combined with $dateDiff introduced in Mongo 5:

// { date: ISODate("2021-12-04"), value: 3  } <= last 7 days // { date: ISODate("2021-11-25"), value: 5  } <= last 15 days // { date: ISODate("2021-11-24"), value: 1  } <= last 15 days // { date: ISODate("2021-11-12"), value: 12 } <= last 30 days // { date: ISODate("2021-10-04"), value: 8  } <= too old db.collection.aggregate([    { $set: {     diff: { $dateDiff: { startDate: "$$NOW", endDate: "$date", unit: "day" } }   }},   // { value: 3,  diff: 0   }   // { value: 5,  diff: -9  }   // { value: 1,  diff: -10 }   // { value: 12, diff: -22 }   // { value: 8,  diff: -61 }    { $match: { diff: { $gte: -30 } } },   // { value: 3,  diff: 0   }   // { value: 5,  diff: -9  }   // { value: 1,  diff: -10 }   // { value: 12, diff: -22 }    { $bucket: {     groupBy: "$diff",     boundaries: [-30, -15, -7, 1],     output: { total: { $sum: "$value" } }   }} ]) // { _id: -30, total: 12 } <= 30 to 16 days ago // { _id: -15, total: 6  } <= 15 to 8  days ago // { _id: -7,  total: 3  } <= 7  to 0  days ago 

This:

  • first computes (with $dateDiff) the number of days of difference between today ("$$NOW") and the document's date
    • if the date is 3 days ago, diff will be set to -3
  • then filters out any document older than 30 days, based on diff
  • and finally buckets documents based on diff within buckets whose boundaries are defined by boundaries: [-30, -15, -7, 1]
    • and for each bucket, we sum bucketed values
vote vote

66

Top 3 video Explaining mongodb - Aggregate $group for multiple date ranges







Related QUESTION?