I mentioned a few days ago that sometimes we might want to aggregate data on day of the week.  It's relatively simple to do that with MongoDB Aggregation Framework, but that does not mean that it's easy.

Imagine that you have this document:

 {
    "time" : ISODate("2013-03-03T14:43:51.468Z"),
    "quantity" : 5,
    "totalPaid" : 5423
} 

There may be other fields, but we mainly want to see how many items we sold by day of the week and for what average price.  Imagine that totalPaid is expressed in cents (so the above document represents $54.23 paid for five items).

First we want to project to a document that has an additional field we need: day of the week.

 {
        "$project" : {
                "quantity" : 1,
                "totalPaid" : 1,
                "_id" : 0,
                "dow" : {
                        "$dayOfWeek" : "$time"
                }
        }
} 

This will turn our ISODate into an integer that represents day of the week - from 1 to 7.  Next we want to do the aggregation grouping by this new field and computing the totals paid and total quantity.

Ever since I discovered Aggregation Framework in MongoDB version 2.1 I thought it was the "coolest thing evah"(tm) and I've been playing with it ever since.   Going through my notes recently I realized that I had a lot of examples some of which did some pretty clever things (if I do say so myself).   

So I thought I should share them with the world!   I said: "World - wouldn't you like to know how to do all kinds of cool stuff with MongoDB aggregation framework?" and world said ...  well, pretty much nothing, so I took that as a "yes".

I'm not going to explain what MongoDB is or what Aggregation Framework is (since you're here, maybe you already know, if not just go and read the linked pages) and instead I'll dive right into some examples.

Imagine you have a lot of records like these:

 {
    "time" : ISODate("2013-03-08T22:25:24.973Z"), 
    "type": "annoying",
    "PINGS" : 143 
 }

You might want to group by type the number of records you have.  You might want to add up how many ping records you got each month, week, each day.   Maybe you even want to find out how many you get by day of the week (Do you get pinged more often on Sundays? Or does it just seem that way?).   You want to count the number of records during each time period and you also want to sum up the total pings in each record.

The basic aggregation query by type goes like this: