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:
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:
The basic aggregation query by type goes like this:
db.collection.aggregate({
"$group" : {
"_id" : "$type",
"count" : {
"$sum" : 1
},
"sum" : {
"$sum" : "$PINGS"
}
}
})
Now, if we want to group things by time periods, we need to extract the times from our date:
{"$project" : {
"type" : 1,
"PINGS" : 1,
"date" : "$time",
"_id" : 0,
"h" : {
"$hour" : "$time"
},
"m" : {
"$minute" : "$time"
},
"s" : {
"$second" : "$time"
},
"ml" : {
"$millisecond" : "$time"
}
}
}
It's sad that there is no way to group the date parts back into a date type (there's a ticket for it in Jira but it hasn't been implemented as of 2.4) otherwise I could just extract the year, month and date and then group them back into a new ISODate, but it turns out there is a different way we can get all our dates truncated to the resolution we want (let's say day) without losing the type:
{"$project" : {
"type" : 1,
"PINGS" : 1,
"date" : {
"$subtract" : [
"$date",
{
"$add" : [
"$ml",
{
"$multiply" : [
"$s",
1000
]
},
{
"$multiply" : [
"$m",
60,
1000
]
},
{
"$multiply" : [
"$h",
60,
60,
1000
]
}
]
}
]
}
}
}
Now we can do our aggregation (grouping) step which is just:
{"$group" : {
"_id" : {
"type" : "$type",
"dt" : "$date"
},
"total" : {
"$sum" : "$PINGS"
},
"cnt" : {
"$sum" : 1
}
}
}
Now we get back records that look something like this:
[
{
"_id" : {
"type" : "annoying",
"dt" : ISODate("2013-01-22T00:00:00Z")
},
"total":146,
"cnt" : 28
},
{
"_id" : {
"type" : "annoying",
"dt" : ISODate("2013-01-23T00:00:00Z")
},
"total" : 345,
"cnt" : 49
}, ...
You will probably want to add a {$sort:{}} step to the end of the pipeline so that you get the dates in order, but that part is simple, so you can probably do it yourselves.
Next time we'll talk about the tricky things we need to do to aggregate by days of the week. It's actually quite simple to extract day of the week from an ISODate - what's not so simple is to turn it into something human readable - like "Wednesday" instead of 4! I'll show you how I do it.
Next time we'll talk about the tricky things we need to do to aggregate by days of the week. It's actually quite simple to extract day of the week from an ISODate - what's not so simple is to turn it into something human readable - like "Wednesday" instead of 4! I'll show you how I do it.
RSS Feed
