Group By in MongoDB

In response to demands for simpler data access, MongoDB is introducing the Aggregation Framework. This library uses a declarative pipe-line notation to support features such as SQL-like group by operations without having to write custom JavaScript routines.

After the “aggregate” clause, most pipeline operations begin with a “$match” clause. Together these act like SQL’s from and where clauses or MongoDB’s find function. Up next is the “$project” clause, which again looks a lot like its SQL or MongoDB counterpart (though unlike SQL it is properly located at the end of the expression).

The next operation is rather unique to MongoDB’s Aggregation Framework. Unlike most relational databases, MongoDB can natively store arrays within a row/document. While convenient for all-or-nothing access, this can make writing reports that need to use a combination of projections, grouping, and filtering quite complicated. The “$unwind” clause breaks down the array into single elements, which are then returned along with the rest of the document.

The “$group” operation serves the same purpose as SQL’s Group By clause, but works more like the LINQ grouping operators. Instead of getting back a flat row, the result set continues to be a nested structure. This allows it to return aggregate information such as counts and averages along with the actual documents, in whole or in part, that make up each grouping.