This is the second in a series of articles on MongoDB’s aggregation functions.
In the first installment,
we looked at count(),
distinct(), and some of the basics of
group(). But group() is rather a beast, so
here we take an extended example, and look at two deep features: finalizers and
key functions.

Counting and Sums

Let’s assume we’re hosting a social news application with a collection of comments. A comment document might look like this:

# Comment document
{ :id => 'fcfa23342'
:user_id => 'a0fb00004',
:text => 'mongodb be so funky',
:upvotes => 12
:downvotes => 4
:upvoters => ['a0fb00004', 'a0fb00005', ...]
:downvoters => ['a0fb00000', 'a0fb00001', ...]
}

Now, we want to know which users have garnered the greatest number of upvotes
and downvotes. With group(), this is straightforward. Our
results will include totals for upvotes and downvotes, with a running total
counting each comment. Hence, our initial document has three keys:

// Initial, aggregator document (in JavaScript)
var setupDoc = {
upvote_total: 0,
downvote_total: 0,
count: 0
}

Next, we need a reduce function that adds to these totals:

// Reduce function (in JavaScript)
var voteAdder = function(doc, prev) {
prev.upvote_total += doc.upvotes;
prev.downvote_total += doc.downvotes;
prev.count += 1;
}

Finally, we pass these to group(), specifying
user_id as the key to group by:

// In the MongoDB JS shell:
db.comments.group({
key: {user_id: true},
initial: setupDoc,
reduce: voteAdder
});

As expected, this returns an array of documents with the totals we sought:

// Result of group()
[
{
"user_id" : "a0fc46004",
"upvote_total" : 24,
"downvote_total" : 30,
"count" : 2
},
{
"user_id" : "a0fc46005",
"upvote_total" : 68,
"downvote_total" : 3,
"count" : 3
}
]


Limiting

If we don’t need totals for the entire collection of comments, we might be
tempted to limit our results by modifying the reduce function:

// Reduce function, totaling comments from 2009
var voteAdderWithFilter = function(doc, prev) {
var startDate = new Date(2009, 0).getTime();
if(doc.created_at.getTime() > startDate) {
prev.upvote_total += doc.upvotes;
prev.downvote_tota += doc.downvotes;
}
}

This gets us our totals, albeit inefficiently. The better route is to specify a query
filter. That way, we can take advantage of MongoDB’s query engine and any indexes
we may have declared. So we add a cond: key to
group(), passing in a document selector like we might pass to
find():

// group(), with a query condition
db.comments.group({
key: {user_id: true},
initial: setupDoc,
reduce: voteAdder,
cond: {created_at: {"$gte": new Date(2009, 0)}}
});

This will assure that our groupings only include comments from 2009.

The Finalizer

But what if counting and summing aren’t enough? Maybe we need the average
number of votes per user, or perhaps we need to extract a weighted score. Enter
the finalizer.

Sounds foreboding.

But it’s just an arbitrary function. It receives each of our result documents and performs whatever
operations we wish on them:

// A finalizer for averaging votes and calculating a score
var finalizer = function(doc) {
doc.average_upvotes = doc.upvotes / doc.count;
doc.score = (0.8 * doc.upvotes) - (0.2 * doc.downvotes);
}

Run group() again, this time specifying the finalizer:

// group(), with a query condition and finalizer
db.comments.group({
key: {user_id: true},
initial: setupDoc,
reduce: voteAdder,
cond: {created_at: {"$gte": new Date(2009, 0)}},
finalize: finalizer
});

And the richer our results become:

// group() results, enriched with a finalizer
[
{
"user_id" : "a0fc46004",
"uptotal" : 24,
"downtotal" : 30,
"count" : 2
"average_upvotes": 12,
"score" : 13.2
},
{
"user_id" : "a0fc46005",
"uptotal" : 68,
"downtotal" : 3,
"count" : 3
"average_upvotes": 22.667,
"score" : 53.8
}
]


But wait!

MongoDB’s group() function has yet another trick up its
sleeves (right?!). Because you might not want to group by any of the available
keys in your documents, there’s the keyf option. You pass it a
function that returns a document to group by.

This actually makes loads of sense. You may want to group users by last name:
A-F, G-L, etc. Or what if you want to arrange pageviews by month, or week, or hour.
If you haven’t cached these values, this isn’t so easy to do. But with a
keyfunction, you’ve got it made.

So, to take an arbitrary but potentially interesting example, what if we
decided to group our comments by length? We could define our
comments as short (< 10 words), medium (between 10 and 50 words),
and long (> 50 words), and see if any correlation between comment length
and votes exists.

Can you see how our keyf function would look?

// A key function for grouping comments as short, medium, and long
var commentLength = function(comment) {
var length = comment.text.split(' ').length;
if(length < 10) {
return {short: true};
else if(length >= 10 && length <= 50) {
return {medium: true};
else
return {large: true};
}

Then, just replace key with keyf:

// group(), with a query condition and finalizer
db.comments.group({
keyf: commentLength,
initial: setupDoc,
reduce: voteAdder,
cond: {created_at: {"$gte": new Date(2009, 0)}},
finalize: finalizer
});

keyf allows us to group our data in many a sane and zany
way. Exploring that range of possibility is left as an exercise to the reader.

Et, violà

So, that is indeed the gamut of group() as of MongoDB 1.1.4.
Please feel free to drop a note on how you’re using it in your apps.

In the next article, I present the basics of the not-so-widely-understood
map-reduce.
Curious? Read on…