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:
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.
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.
{
"$group" : {
"_id" : "$dow",
"total" : {
"$sum" : "$totalPaid"
},
"count" : {
"$sum" : "$quantity"
}
}
}
You probably want to sort your results here, while your days are still represented by numbers (once you translate them to strings like "Monday", "Tuesday", etc. they won't sort quite so easily!
{ "$sort" : { "_id" : 1 } }
Now the tricky part. We need to create a project step that will translate the numbers into corresponding strings.
I broke it up into steps so that you can see how I came up with an incredibly long and complex expression - it's the kind of thing that I could never get all the parenthesis to match up correctly if I didn't do it programmatically, or in sequence of simple steps. I'm also saving the last step into a variable that I will use in my final pipeline.
I broke it up into steps so that you can see how I came up with an incredibly long and complex expression - it's the kind of thing that I could never get all the parenthesis to match up correctly if I didn't do it programmatically, or in sequence of simple steps. I'm also saving the last step into a variable that I will use in my final pipeline.
var ifFri = { $cond: [ {$eq:[6,"$_id"]}, "Friday","Saturday"]}
var ifThu = { $cond: [ {$eq:[5,"$_id"]}, "Thursday",ifFri]}
var ifWed = { $cond: [ {$eq:[4,"$_id"]}, "Wednesday",ifThu]}
var ifTue = { $cond: [ {$eq:[3,"$_id"]}, "Tuesday",ifWed]}
var ifMon = { $cond: [ {$eq:[2,"$_id"]}, "Monday",ifTue]}
var dayOfWeek = { $cond: [ {$eq:[1,"$_id"]}, "Sunday",ifMon]} Now that I have the tricky part saved in a variable, the actual $project looks much less intimidating:
{
"$project" : {
"_id" : 0,
"dow" : dayOfWeek,
"avgPrice" : {
"$divide" : [
{
"$divide" : [
"$totalPaid",
"$quantity"
]
},
100
]
}
}
}
All I'm doing is turning _id field into day of week string, and figuring out average in dollars by dividing total paid by quantity and then dividing the result by 100 to move the decimal point two to the left.
Meanwhile, let's look at what 'dayOfWeek' expression actually looks like:
Meanwhile, let's look at what 'dayOfWeek' expression actually looks like:
{
"$cond" : [
{
"$eq" : [
1,
"$_id"
]
},
"Sunday",
{
"$cond" : [
{
"$eq" : [
2,
"$_id"
]
},
"Monday",
{
"$cond" : [
{
"$eq" : [
3,
"$_id"
]
},
"Tuesday",
{
"$cond" : [
{
"$eq" : [
4,
"$_id"
]
},
"Wednesday",
{
"$cond" : [
{
"$eq" : [
5,
"$_id"
]
},
"Thursday",
{
"$cond" : [
{
"$eq" : [
6,
"$_id"
]
},
"Friday",
"Saturday"
]
}
]
}
]
}
]
}
]
}
]
}
Now we run our aggregation query and we have our result that looks something like this:
{
"result" : [
{
"dow" : "Sunday",
"avgPrice" : 18.15
},
{
"dow" : "Monday",
"avgPrice" : 18.06
},
{
"dow" : "Tuesday",
"avgPrice" : 33.50
},
{
"dow" : "Wednesday",
"avgPrice" : 50.66
},
{
"dow" : "Thursday",
"avgPrice" : 55.89
},
{
"dow" : "Friday",
"avgPrice" : 54.00
},
{
"dow" : "Saturday",
"avgPrice" : 52.53
}
],
"ok" : 1
}
RSS Feed
