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

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

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}

See Also
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.

Comments (5)

IF YOU HAVE A QUESTION, POST IT TO THE USER GROUP.

These pages are fine for comments, but for questions, your best bet will always be the MongoDB User Group.

 

  1. Nov 23

    Anonymous says:

    How do lt and gt work here: cond: {"invoked_at.d": {$gt: "2009-11", $lt: "2009-...

    How do lt and gt work here:

    cond: {"invoked_at.d": {$gt: "2009-11", $lt: "2009-12"}}

    It doesn't seem like 'd' is anything but a text field. Is this a realistic example?

    1. Nov 25

      Anonymous says:

      Strings are comparable alphabetically.  2009-11-01 is greater than 2009-11 ...

      Strings are comparable alphabetically.  2009-11-01 is greater than 2009-11 alphabetically sorted.  That's why YYYY-MM-DD HH:MM format is so convenient when storing dates; human-readable, and easily sorted.

    2. Nov 25

      Anonymous says:

      PS ask on the mailing list in the future you will get faster responses.

      PS ask on the mailing list in the future you will get faster responses.

  2. Nov 26

    Anonymous says:

    How to use "Distinct" if the driver does not support it (e.g. C#)? I've tried li...

    How to use "Distinct" if the driver does not support it (e.g. C#)? I've tried like:

    > db.$cmd.findOne({distinct:{ns:"foo",key:{movies:true}})

    says OK, returns nothing... What should be actually used (ns, key...)?

  3. Jan 29

    Anonymous says:

    The output of the second example ("each domain for each day in November 2009") s...

    The output of the second example ("each domain for each day in November 2009") seems wrong. Was it just copied from the earlier example accidentally?

Add Comment