Aggregate $group for multiple date ranges

Question

In my aggregate, each document in the stream will have a date on it.

I need to sum some values over date ranges..

My documents look like:

{ value: 3,  date: [SoME TIME STAMP] },
{ value: 4,  date: [SoME TIME STAMP] },
{ value: 1,  date: [SoME TIME STAMP] },
{ value: -6, date: [SoME TIME STAMP] }

I want to be able to group these document base on the date range. i.e.: 1-7 days ago, 8-15 days ago. and 15-30 days ago.

I probably apply 3 different aggregate queries with 3 different $match on dates.

But is it possible to do all the $group and sum the "value" field in one run?

Answer 1

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.

Answer 2

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