- Reference >
- Database Commands >
- group
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 aSELECT ... 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: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 thekeyf
instead ofkey
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, thedb.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 isfinalize
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, thedb.collection.group()
method takes thekeyf
field and thereduce
field whereas thegroup
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:The following example groups by the
ord_dt
anditem.sku
fields those documents that haveord_dt
greater than01/01/2012
:The result is a documents that contain the
retval
field which contains the group by records, thecount
field which contains the total number of documents grouped, thekeys
field which contains the number of unique groupings (i.e. number of elements in theretval
), and theok
field which contains the command status:The method call is analogous to the SQL statement:
The following example groups by the
ord_dt
anditem.sku
fields, those documents that haveord_dt
greater than01/01/2012
and calculates the sum of theqty
field for each grouping:The
retval
field of the returned document is an array of documents that contain the group by fields and the calculated aggregation field:The method call is analogous to the SQL statement:
The following example groups by the calculated
day_of_week
field, those documents that haveord_dt
greater than01/01/2012
and calculates the sum, count, and average of theqty
field for each grouping:The
retval
field of the returned document is an array of documents that contain the group by fields and the calculated aggregation field:See also