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.