MongoDB Aggregation Framework - How to Match by Date Range, Group By Days, and Return Average For Each Day?

Question

I'm just learning about MongoDB's Aggregation Framework, and I'm wondering if someone can help me improve this query to do the following:

• Find/Match Records with Dates in between a submitted range
• Group the results by Day
• Return Averages for each Day

Here is my model, there are some helpful properties to make writing this query this easier, like the day property...

// Model
var PriceHourlySchema = new Schema({
    created: {
        type: Date,
        default: Date.now
    },
    day: {
        type: String,
        required: true,
        trim: true
    },
    hour: {
        type: String,
        required: true,
        trim: true
    },
    price: {
        type: Number,
        required: true
    },
    date: {
        type: Date,
        required: true
    }
}, 
{ 
    autoIndex: true 
});

Here is my query so far, this only returns a Total Average for all dates included within the range, and does not group by days and return averages for each day, so you can $group by day...

var start       = moment.utc(req.query.start).startOf('year').toDate();
var end         = moment.utc(req.query.start).add('years',1).startOf('year').add('hours',1).toDate();

PriceHourly.aggregate([
    { $match: { date: { $gt: start, $lt: end } } },
    { $group: { _id: null, price: { $avg: '$price' } } }
], function(err, results){
    console.log(results); 
}); // PriceHourly();

Solution

The problem seems to be with the _id you are using for $group. You should group by $day rather than null, if you want to group the results by day. Try this:

PriceHourly.aggregate([
    { $match: { date: { $gt: start, $lt: end } } },
    { $group: { _id: "$day", price: { $avg: '$price' } } }
], function(err, results){
    console.log(results); 
});

That said, you do not need to store the day and hour as separate elements in the document. It's redundant data. You can extract the day and hour from date field in the aggregation query using the Date Aggregation operators.