Nested Group By Function with MongoDB

Question

I'm pretty new with MongoDB, long time MySQL guy and am running into a little roadblock.

Given the below sample data:
TeamID Day OrderAmount
100      4/1   50
100      4/1   40
200      4/2   50
100      4/2   20

I'm trying to find the average team order amounts per day. I'm able to do a simple query using mapReduce function to do a group by with the TeamId & Day. So now I have:

TeamID      Day      AvgAmount
100           4/1      45
200           4/2      50
100           4/2      20

Now I'm trying to roll that data up to get the average order amount per team per day which would be:

Day      AvgAmount
4/1      47.5
4/2      35

I can do this easily with MySQL but am having trouble figuring out how to do this with MongoDB without doing it manually in the app side rather than doing it with MongoDB.

Answer 1

You can calculate these aggregates with either map-reduce, or the group() function. I'm using group() because it's a bit simpler and faster, however you should use map-reduce if you need to distribute the query over a sharded cluster.

First load the data:

db.orders.insert( { teamid: 100, date: "4/1", amount: 50 })
db.orders.insert( { teamid: 100, date: "4/1", amount: 40 })
db.orders.insert( { teamid: 200, date: "4/2", amount: 50 })
db.orders.insert( { teamid: 100, date: "4/2", amount: 20 })

Per team, per day:

db.orders.group({
    key: { teamid: true, date: true },
    reduce: function(doc, out) { out.sum += doc.amount; out.count++; },
    initial: { sum: 0, count: 0 },
    finalize: function(out) { out.average = out.sum / out.count }
});

To roll up daily aggregates, just change the key:

db.orders.group({
    key: { date: true },
    reduce: function(doc, out) { out.sum += doc.amount; out.count++; },
    initial: { sum: 0, count: 0 },
    finalize: function(out) { out.average = out.sum / out.count }
});