Mongodb - query today's totals, week's totals, and month's totals in one query

Question

I have objects in my db that look like this:

{
    "_id": ObjectId("563f8c320ef987c122aeeb4a"),
    "num": 1515,
    "createdAt": ISODate("2015-10-29T21:14:26.477Z"),
}

I'd like to write an aggregation that groups all by a specific id and sums up the total for today, this week, and this month, and do it in one query. I've written three separate queries for this task, but I'm wondering if I can make this more efficient and get it done in one query.

Edit

Somebody mentioned mapReduce as a solution. It seems like a promising solution, but I'm not able to get anything to return from a simple query. The following is what I have tried:

    var o = {};
    o.map = function () { emit( this.num, this.createdAt ) }
    o.reduce = function (k, vals) { return vals }
    o.query = {
        _id: req.user._id
    }
    Submission.mapReduce(o, function (err, results) {
        console.log(results)
    })

The console logs an empty array. I've also tried casting the _id to a mongoose object id, but it still returns an empty array.

@Jaco I gave map reduce a try, but I'm not seeing the right results. Are you familiar with using map reduce with mongoosejs? — jordan, Oct 29 '15 at 23:55
See Blakes answer, it is a more elegant way to achieve what you want without using a map reduce query. — Alex, Oct 30 '15 at 09:55

score 3 · Accepted Answer · edited May 23 '17 at 11:52

This is more really a question of what you expect the output to look like, as any aggregated result essentially needs to group at the lowest level and then progressively group at higher "grains" until the largest level ( "month" ) is reached. This kind of implies data grouped by "month" ultimately, unless you break it down otherwise.

In essence, progressively $group:

db.collection.aggregate([
    // First total per day. Rounding dates with math here
    { "$group": {
        "_id": {
            "$add": [
                { "$subtract": [
                    { "$subtract": [ "$createdAt", new Date(0) ] },
                    { "$mod": [
                        { "$subtract": [ "$createdAt", new Date(0) ] },
                        1000 * 60 * 60 * 24
                    ]}                        
                ]},
                new Date(0)
            ]
        },
        "week": { "$first": { "$week": "$createdAt" } },
        "month": { "$first": { "$month": "$createdAt" } },
        "total": { "$sum": "$num" }
    }},

    // Then group by week
    { "$group": {
        "_id": "$week",
        "month": { "$first": "$month" },
        "days": {
            "$push": {
                "day": "$_id",
                "total": "$total"
            }
        },
        "total": { "$sum": "$total" }
    }},

    // Then group by month
    { "$group": {
        "_id": "$month",
        "weeks": {
            "$push": {
                "week": "$_id",
                "total": "$total",
                "days": "$days"
            }
        },
        "total": { "$sum": "$total" }
    }}
])

So each level after the first which sums up per day is then progressively pushed into array content for it's "round up" value and the totals are then summed at that level as well.

If you want a flatter output with one record per day containing it's weekly and monthly totals as well as the day total, then just append two $unwind statements to the end of the pipeline:

{ "$unwind": "$weeks" },
{ "$unwind": "$weeks.days" }

And optionally $project the "dotted" fields out to something flatter and readable if you must.

If you are spanning "years" with this, then include such an operation in the grouping key at least from the "week" level so you are not possibly combining data from different years and they are separated.

It is also my own general preference to use the "date math" approach when rounding dates as it returns a Date object, but as is used at the other levels than "day", you can just alternately use the date aggregation operators instead.

No need for mapReduce as this is fairly intuitive and there is a finite amount of days in a month that means the BSON limit when nesting arrays in the content while aggregating will not be broken.

This is perfect! And really simple! Thank you – jordan Oct 30 '15 at 20:43 — jordan, Oct 30 '15 at 20:43

Mongodb - query today's totals, week's totals, and month's totals in one query

1 Answers1

Linked