Aggregation

Mongo includes utility functions which provide server-side count, distinct, and group by operations. More advanced aggregate functions can be crafted using MapReduce.

Count
Distinct
Group

Examples
Using Group from Various Languages

Map/Reduce
See Also

Count

count() returns the number of objects in a collection or matching a query. If a document selector is provided, only the number of matching documents will be returned.

size() is like count() but takes into consideration any limit() or skip() specified for the query.

db.collection.count(selector);

For example:

print( "# of objects: " + db.mycollection.count() );
print( db.mycollection.count( {active:true} );

count is faster if an index exists for the condition in the selector. For example, to make the count on active fast, invoke

db.mycollection.ensureIndex( {active:1} );

Distinct

distinct(key) returns a list of distinct values for the given key across a collection.

db.addresses.save({"zip-code": 10010})
db.addresses.save({"zip-code": 10010})
db.addresses.save({"zip-code": 99701})

db.addresses.distinct("zip-code");
[ 10010, 99701 ]

distinct may also reference a nested key:

db.comments.save({"user": {"points": 25}})
db.comments.save({"user": {"points": 31}})
db.comments.save({"user": {"points": 25}})

db.comments.distinct("user.points");
[ 25, 31 ]

You can add an optional query parameter to distinct as well

db.address.distinct( "zip-code" , { age : 30 } )

Group

Note: currently one must use map/reduce instead of group() in sharded MongoDB configurations.

group returns an array of grouped items. The command is similar to SQL's group by. The SQL statement

select a,b,sum(c) csum from coll where active=1 group by a,b

corresponds to the following in MongoDB:

db.coll.group(
           {key: { a:true, b:true },
            cond: { active:1 },
            reduce: function(obj,prev) { prev.csum += obj.c; },
            initial: { csum: 0 }
            });

One caveat: the array resulting from the group command must fit in RAM; otherwise, you'll get an exception.

group takes a single object parameter containing the following fields:

key: Fields to group by.
reduce: The reduce function aggregates (reduces) the objects iterated. Typical operations of a reduce function include summing and counting. reduce takes two arguments: the current document being iterated over and the aggregation counter object. In the example above, these arguments are named obj and prev.
initial: initial value of the aggregation counter object.
keyf: An optional function returning a "key object" to be used as the grouping key. Use this instead of key to specify a key that is not an existing member of the object (or, to access embedded members). Set in lieu of key.
cond: An optional condition that must be true for a row to be considered. This is essentially a find() query expression object. If null, the reduce function will run against all rows in the collection.
finalize: An optional function to be run on each item in the result set just before the item is returned. Can either modify the item (e.g., add an average field given a count and a total) or return a replacement object (returning a new object with just _id and average fields). See jstests/group3.js for examples.

To order the grouped data, simply sort it client-side upon return. The following example is an implementation of count() using group().

function gcount(collection, condition) {
  var res =
    db[collection].group(
           { key: {},
             initial: {count: 0},
             reduce: function(obj,prev){ prev.count++;},
             cond: condition } );
  // group() returns an array of grouped items.  here, there will be a single
  // item, as key is {}.
  return res[0] ? res[0].count : 0;
}

Examples

The examples assume data like this:

{ domain: "www.mongodb.org"
, invoked_at: {d:"2009-11-03", t:"17:14:05"}
, response_time: 0.05
, http_action: "GET /display/DOCS/Aggregation"
}

Show me stats for each http_action in November 2009:

db.test.group(
   { cond: {"invoked_at.d": {$gt: "2009-11", $lt: "2009-12"}}
   , key: {http_action: true}
   , initial: {count: 0, total_time:0}
   , reduce: function(doc, out){ out.count++; out.total_time+=doc.response_time }
   , finalize: function(out){ out.avg_time = out.total_time / out.count }
   } );

[
  {
    "http_action" : "GET /display/DOCS/Aggregation",
    "count" : 1,
    "total_time" : 0.05,
    "avg_time" : 0.05
  }
]

Show me stats for each domain for each day in November 2009:

db.test.group(
   { cond: {"invoked_at.d": {$gt: "2009-11", $lt: "2009-12"}}
   , key: {domain: true, invoked_at.d: true}
   , initial: {count: 0, total_time:0}
   , reduce: function(doc, out){ out.count++; out.total_time+=doc.response_time }
   , finalize: function(out){ out.avg_time = out.total_time / out.count }
   } );

[
  {
    "http_action" : "GET /display/DOCS/Aggregation",
    "count" : 1,
    "total_time" : 0.05,
    "avg_time" : 0.05
  }
]

Using Group from Various Languages

Some language drivers provide a group helper function. For those that don't, one can manually issue the db command for group. Here's an example using the Mongo shell syntax:

 > db.foo.find()
{"_id" :  ObjectId( "4a92af2db3d09cb83d985f6f")  , "x" : 1}
{"_id" :  ObjectId( "4a92af2fb3d09cb83d985f70")  , "x" : 3}
{"_id" :  ObjectId( "4a92afdab3d09cb83d985f71")  , "x" : 3}

> db.$cmd.findOne({group:{ns:"foo",cond:{},key:{x:1},initial:{count:0},$reduce:function(obj,prev){prev.count++;}}})
{"retval" : [{"x" : 1 , "count" : 1},{"x" : 3 , "count" : 2}] , "count" : 3 , "keys" : 2 , "ok" : 1}

If you use the database command with keyf (instead of key) it must be prefixed with a $. For example:

db.$cmd.findOne({group : {
... ns : "foo", 
... $keyf : function(doc) { return {"x" : doc.x}; }, 
... initial : {count : 0}, 
... $reduce : function(obj,prev) { prev.count++; }}})

Map/Reduce

MongoDB provides a MapReduce facility for more advanced aggregation needs. CouchDB users: please note that basic queries in MongoDB do not use map/reduce.