group¶

group¶

The group command groups documents in a collection by the specified key and performs simple aggregation functions such as computing counts and sums. The command is analogous to a SELECT ... GROUP BY statement in SQL. The command returns a document with the grouped records as well as the command meta-data.

The group command takes the following prototype form:

copy

{ group: { ns: <namespace>,
           key: <key>,
           $reduce: <reduce function>,
           $keyf: <key function>,
           cond: <query>,
           finalize: <finalize function> } }

The command fields are as follows:

Fields:

ns – Specifies the collection from which to perform the group by operation.
key – Specifies one or more document fields to group. Returns a “key object” for use as the grouping key.
$reduce – Specifies an aggregation function that operates on the documents during the grouping operation, such as compute a sum or a count. The aggregation function takes two arguments: the current document and an aggregation result document for that group.
initial – Initializes the aggregation result document.
$keyf – Optional. Alternative to the key field. Specifies a function that creates a “key object” for use as the grouping key. Use the keyf instead of key to group by calculated fields rather than existing document fields.
cond – Optional. Specifies the selection criteria to determine which documents in the collection to process. If the cond field is omitted, the db.collection.group() processes all the documents in the collection for the group operation.
finalize –
Optional. Specifies a function that runs each item in the result set before db.collection.group() returns the final value. This function can either modify the result document or replace the result document as a whole.

Note

Unlike the $keyf and the $reduce fields that specify a function, the field name is finalize and not $finalize.

Warning

The group command does not work with sharded clusters. Use the aggregation framework or map-reduce in sharded environments.
The group command takes a read lock and does not allow any other threads to execute JavaScript while it is running.

Note

The result set must fit within the maximum BSON document size.

Additionally, in version 2.2, the returned array can contain at most 20,000 elements; i.e. at most 20,000 unique groupings. For group by operations that results in more than 20,000 unique groupings, use mapReduce. Previous versions had a limit of 10,000 elements.

For the shell, MongoDB provides a wrapper method db.collection.group(); however, the db.collection.group() method takes the keyf field and the reduce field whereas the group command takes the $keyf field and the $reduce field.

Consider the following examples of the db.collection.group() method:

The examples assume an orders collection with documents of the following prototype:

copy

{
  _id: ObjectId("5085a95c8fada716c89d0021"),
  ord_dt: ISODate("2012-07-01T04:00:00Z"),
  ship_dt: ISODate("2012-07-02T04:00:00Z"),
  item: { sku: "abc123",
          price: 1.99,
          uom: "pcs",
          qty: 25 }
}

The following example groups by the ord_dt and item.sku fields those documents that have ord_dt greater than 01/01/2012:

copy

db.runCommand( { group:
                   {
                     ns: 'orders',
                     key: { ord_dt: 1, 'item.sku': 1 },
                     cond: { ord_dt: { $gt: new Date( '01/01/2012' ) } },
                     $reduce: function ( curr, result ) { },
                     initial: { }
                   }
                } )

The result is a documents that contain the retval field which contains the group by records, the count field which contains the total number of documents grouped, the keys field which contains the number of unique groupings (i.e. number of elements in the retval), and the ok field which contains the command status:

copy

{ "retval" :
      [ { "ord_dt" : ISODate("2012-07-01T04:00:00Z"), "item.sku" : "abc123"},
        { "ord_dt" : ISODate("2012-07-01T04:00:00Z"), "item.sku" : "abc456"},
        { "ord_dt" : ISODate("2012-07-01T04:00:00Z"), "item.sku" : "bcd123"},
        { "ord_dt" : ISODate("2012-07-01T04:00:00Z"), "item.sku" : "efg456"},
        { "ord_dt" : ISODate("2012-06-01T04:00:00Z"), "item.sku" : "abc123"},
        { "ord_dt" : ISODate("2012-06-01T04:00:00Z"), "item.sku" : "efg456"},
        { "ord_dt" : ISODate("2012-06-01T04:00:00Z"), "item.sku" : "ijk123"},
        { "ord_dt" : ISODate("2012-05-01T04:00:00Z"), "item.sku" : "abc123"},
        { "ord_dt" : ISODate("2012-05-01T04:00:00Z"), "item.sku" : "abc456"},
        { "ord_dt" : ISODate("2012-06-08T04:00:00Z"), "item.sku" : "abc123"},
        { "ord_dt" : ISODate("2012-06-08T04:00:00Z"), "item.sku" : "abc456"}
      ],
  "count" : 13,
  "keys" : 11,
  "ok" : 1 }

The method call is analogous to the SQL statement:

copy

SELECT ord_dt, item_sku
FROM orders
WHERE ord_dt > '01/01/2012'
GROUP BY ord_dt, item_sku

The following example groups by the ord_dt and item.sku fields, those documents that have ord_dt greater than 01/01/2012 and calculates the sum of the qty field for each grouping:

copy

db.runCommand( { group:
                   {
                     ns: 'orders',
                     key: { ord_dt: 1, 'item.sku': 1 },
                     cond: { ord_dt: { $gt: new Date( '01/01/2012' ) } },
                     $reduce: function ( curr, result ) {
                                  result.total += curr.item.qty;
                              },
                     initial: { total : 0 }
                   }
                } )

The retval field of the returned document is an array of documents that contain the group by fields and the calculated aggregation field:

copy

{ "retval" :
      [ { "ord_dt" : ISODate("2012-07-01T04:00:00Z"), "item.sku" : "abc123", "total" : 25 },
        { "ord_dt" : ISODate("2012-07-01T04:00:00Z"), "item.sku" : "abc456", "total" : 25 },
        { "ord_dt" : ISODate("2012-07-01T04:00:00Z"), "item.sku" : "bcd123", "total" : 10 },
        { "ord_dt" : ISODate("2012-07-01T04:00:00Z"), "item.sku" : "efg456", "total" : 10 },
        { "ord_dt" : ISODate("2012-06-01T04:00:00Z"), "item.sku" : "abc123", "total" : 25 },
        { "ord_dt" : ISODate("2012-06-01T04:00:00Z"), "item.sku" : "efg456", "total" : 15 },
        { "ord_dt" : ISODate("2012-06-01T04:00:00Z"), "item.sku" : "ijk123", "total" : 20 },
        { "ord_dt" : ISODate("2012-05-01T04:00:00Z"), "item.sku" : "abc123", "total" : 45 },
        { "ord_dt" : ISODate("2012-05-01T04:00:00Z"), "item.sku" : "abc456", "total" : 25 },
        { "ord_dt" : ISODate("2012-06-08T04:00:00Z"), "item.sku" : "abc123", "total" : 25 },
        { "ord_dt" : ISODate("2012-06-08T04:00:00Z"), "item.sku" : "abc456", "total" : 25 }
      ],
  "count" : 13,
  "keys" : 11,
  "ok" : 1 }

The method call is analogous to the SQL statement:

copy

SELECT ord_dt, item_sku, SUM(item_qty) as total
FROM orders
WHERE ord_dt > '01/01/2012'
GROUP BY ord_dt, item_sku

The following example groups by the calculated day_of_week field, those documents that have ord_dt greater than 01/01/2012 and calculates the sum, count, and average of the qty field for each grouping:

copy

db.runCommand( { group:
                   {
                     ns: 'orders',
                     $keyf: function(doc) {
                               return { day_of_week: doc.ord_dt.getDay() } ; },
                     cond: { ord_dt: { $gt: new Date( '01/01/2012' ) } },
                     $reduce: function ( curr, result ) {
                                  result.total += curr.item.qty;
                                  result.count++;
                              },
                     initial: { total : 0, count: 0 },
                     finalize: function(result) {
                                 var weekdays = [ "Sunday", "Monday", "Tuesday",
                                                  "Wednesday", "Thursday",
                                                  "Friday", "Saturday" ];

                                 result.day_of_week = weekdays[result.day_of_week];
                                 result.avg = Math.round(result.total / result.count);
                     }

                   }
                } )

The retval field of the returned document is an array of documents that contain the group by fields and the calculated aggregation field:

copy

{ "retval" :
      [ { "day_of_week" : "Sunday", "total" : 70, "count" : 4, "avg" : 18 },
        { "day_of_week" : "Friday", "total" : 110, "count" : 6, "avg" : 18 },
        { "day_of_week" : "Tuesday", "total" : 70, "count" : 3, "avg" : 23 }

      ],
  "count" : 13,
  "keys" : 3,
  "ok" : 1 }