aggregate
Definition
aggregate
- Performs aggregation operation using the aggregation pipeline. The pipeline allows usersto process data from a collection or other source with a sequence ofstage-based manipulations.
Syntax
The command has following syntax:
Changed in version 3.6.
- {
- aggregate: "<collection>" || 1,
- pipeline: [ <stage>, <...> ],
- explain: <boolean>,
- allowDiskUse: <boolean>,
- cursor: <document>,
- maxTimeMS: <int>,
- bypassDocumentValidation: <boolean>,
- readConcern: <document>,
- collation: <document>,
- hint: <string or document>,
- comment: <string>,
- writeConcern: <document>
- }
Tip
Rather than run the aggregate
command directly, mostusers should use the db.collection.aggregate()
helperprovided in the mongo
shell or the equivalent helper intheir driver. In 2.6 and later, thedb.collection.aggregate()
helper always returns a cursor.
Command Fields
The aggregate
command takes the following fields asarguments:
Field | Type | Description |
---|---|---|
aggregate | string | The name of the collection or view that acts as the input for theaggregation pipeline. Use 1 for collection agnostic commands. |
pipeline | array | An array of aggregation pipeline stages that process andtransform the document stream as part of the aggregation pipeline. |
explain | boolean | Optional. Specifies to return the information on the processing of the pipeline.Not available in multi-document transactions. |
allowDiskUse | boolean | Optional. Enables writing to temporary files. When set to true , aggregationstages can write data to the _tmp subdirectory in thedbPath directory.Starting in MongoDB 4.2, the profiler log messages and diagnostic logmessages includes a usedDisk indicator if any aggregation stage wrote data to temporary files dueto memory restrictions. |
cursor | document | Specify a document that contains options that control the creationof the cursor object.Changed in version 3.6: MongoDB 3.6 removes the use of aggregate commandwithout the cursor option unless the command includes theexplain option. Unless you include the explain option, you mustspecify the cursor option.- To indicate a cursor with the default batch size, specify cursor:{} .- To indicate a cursor with a non-default batch size, use cursor: {batchSize: <num> } . |
maxTimeMS | non-negative integer | Optional. Specifies a time limit in milliseconds for processingoperations on a cursor. If you do not specify a value for maxTimeMS,operations will not time out. A value of 0 explicitlyspecifies the default unbounded behavior.MongoDB terminates operations that exceed their allotted time limitusing the same mechanism as db.killOp() . MongoDB onlyterminates an operation at one of its designated interruptpoints. |
bypassDocumentValidation | boolean | Optional. Applicable only if you specify the $out or $merge aggregationstages.Enables aggregate to bypass document validationduring the operation. This lets you insert documents that do notmeet the validation requirements.New in version 3.2. |
readConcern | document | Optional. Specifies the read concern.Starting in MongoDB 3.6, the readConcern option has the followingsyntax: readConcern: { level: <value> } Possible read concern levels are:- "local" . This is the default read concern level forread operations against primary and read operations againstsecondaries when associated with causally consistent sessions.- "available" . This is the default for reads againstsecondaries when when not associated with causally consistentsessions. The query returns the instance’s mostrecent data.- "majority" . Available for replica sets that useWiredTiger storage engine.- "linearizable" . Available for read operations on theprimary only.For more formation on the read concern levels, seeRead Concern Levels.Starting in MongoDB 4.2, the $out stage cannot be usedin conjunction with read concern "linearizable" . Thatis, if you specify "linearizable" read concern fordb.collection.aggregate() , you cannot include the$out stage in the pipeline.The $merge stage cannot be used in conjunction with readconcern "linearizable" . That is, if you specify"linearizable" read concern fordb.collection.aggregate() , you cannot include the$merge stage in the pipeline. |
collation | document | Optional.Specifies the collation to use for the operation.Collation allows users to specifylanguage-specific rules for string comparison, such as rules forlettercase and accent marks.The collation option has the following syntax:When specifying collation, the locale field is mandatory; allother collation fields are optional. For descriptions of the fields,see Collation Document.If the collation is unspecified but the collection has adefault collation (see db.createCollection() ), theoperation uses the collation specified for the collection.If no collation is specified for the collection or for theoperations, MongoDB uses the simple binary comparison used in priorversions for string comparisons.You cannot specify multiple collations for an operation. Forexample, you cannot specify different collations per field, or ifperforming a find with a sort, you cannot use one collation for thefind and another for the sort.New in version 3.4. |
hint | string or document | Optional. The index to use for the aggregation. The index is on the initialcollection/view against which the aggregation is run.Specify the index either by the index name or by the indexspecification document.NoteThe hint does not apply to $lookup and$graphLookup stages.New in version 3.6. |
comment | string | Optional. Users can specify an arbitrary string to help trace the operationthrough the database profiler, currentOp, and logs.New in version 3.6. |
writeConcern | document | Optional. A document that expresses the write concernto use with the $out or $merge stage.Omit to use the default write concern with the $out or$merge stage. |
MongoDB 3.6 removes the use of aggregate
commandwithout the cursor
option unless the command includes theexplain
option. Unless you include the explain
option, you mustspecify the cursor option.
- To indicate a cursor with the default batch size, specify
cursor:{}
. - To indicate a cursor with a non-default batch size, use
cursor: {batchSize: <num> }
.
For more information about the aggregation pipelineAggregation Pipeline, Aggregation Reference, andAggregation Pipeline Limits.
Sessions
New in version 4.0.
For cursors created inside a session, you cannot callgetMore
outside the session.
Similarly, for cursors created outside of a session, you cannot callgetMore
inside a session.
Transactions
aggregate
can be used inside multi-document transactions.
However, the following stages are not allowed within transactions:
You also cannot specify the explain
option.
- For cursors created outside of a transaction, you cannot call
getMore
inside the transaction. - For cursors created in a transaction, you cannot call
getMore
outside the transaction.
Important
In most cases, multi-document transaction incurs a greaterperformance cost over single document writes, and theavailability of multi-document transactions should not be areplacement for effective schema design. For many scenarios, thedenormalized data model (embedded documents and arrays) will continue to be optimal for yourdata and use cases. That is, for many scenarios, modeling your dataappropriately will minimize the need for multi-documenttransactions.
For additional transactions usage considerations(such as runtime limit and oplog size limit), see alsoProduction Considerations.
Client Disconnection
For aggregate
operation that do not include the$out
or $merge
stages:
Starting in MongoDB 4.2, if the client that issued the aggregate
disconnects before the operation completes, MongoDB marksthe aggregate
for termination (i.e. killOp
on theoperation).
Example
Changed in version 3.4: MongoDB 3.6 removes the use of aggregate
commandwithout the cursor
option unless the command includes theexplain
option. Unless you include the explain
option, you mustspecify the cursor option.
- To indicate a cursor with the default batch size, specify
cursor:{}
. - To indicate a cursor with a non-default batch size, use
cursor: {batchSize: <num> }
.
Rather than run the aggregate
command directly, mostusers should use the db.collection.aggregate()
helperprovided in the mongo
shell or the equivalent helper intheir driver. In 2.6 and later, thedb.collection.aggregate()
helper always returns a cursor.
Except for the first two examples which demonstrate the commandsyntax, the examples in this page use thedb.collection.aggregate()
helper.
Aggregate Data with Multi-Stage Pipeline
A collection articles
contains documents such as the following:
- {
- _id: ObjectId("52769ea0f3dc6ead47c9a1b2"),
- author: "abc123",
- title: "zzz",
- tags: [ "programming", "database", "mongodb" ]
- }
The following example performs an aggregate
operation onthe articles
collection to calculate the count of each distinctelement in the tags
array that appears in the collection.
- db.runCommand( {
- aggregate: "articles",
- pipeline: [
- { $project: { tags: 1 } },
- { $unwind: "$tags" },
- { $group: { _id: "$tags", count: { $sum : 1 } } }
- ],
- cursor: { }
- } )
In the mongo
shell, this operation can use thedb.collection.aggregate()
helper as in the following:
- db.articles.aggregate( [
- { $project: { tags: 1 } },
- { $unwind: "$tags" },
- { $group: { _id: "$tags", count: { $sum : 1 } } }
- ] )
Use $currentOp on an Admin Database
The following example runs a pipeline with two stages on the admindatabase. The first stage runs the $currentOp
operationand the second stage filters the results of that operation.
- db.adminCommand( {
- aggregate : 1,
- pipeline : [ {
- $currentOp : { allUsers : true, idleConnections : true } }, {
- $match : { shard : "shard01" }
- }
- ],
- cursor : { }
- } )
Note
The aggregate
command does not specify a collection andinstead takes the form {aggregate: 1}. This is because the initial$currentOp
stage does not draw input from a collection. Itproduces its own data that the rest of the pipeline uses.
The new db.aggregate()
helper has been added to assist inrunning collectionless aggregations such as this. The above aggregationcould also be run like this example.
Return Information on the Aggregation Operation
The following aggregation operation sets the optional field explain
to true
to return information about the aggregation operation.
- db.orders.aggregate([
- { $match: { status: "A" } },
- { $group: { _id: "$cust_id", total: { $sum: "$amount" } } },
- { $sort: { total: -1 } }
- ],
- { explain: true }
- )
Note
The explain
output is subject to change between releases.
See also
db.collection.aggregate()
method
Aggregate Data using External Sort
Aggregation pipeline stages have maximum memory use limit. To handle large datasets, setallowDiskUse
option to true
to enable writing data totemporary files, as in the following example:
- db.stocks.aggregate( [
- { $project : { cusip: 1, date: 1, price: 1, _id: 0 } },
- { $sort : { cusip : 1, date: 1 } }
- ],
- { allowDiskUse: true }
- )
Starting in MongoDB 4.2, the profiler log messages and diagnostic logmessages includes a usedDisk
indicator if any aggregation stage wrote data to temporary files dueto memory restrictions.
See also
Aggregate Data Specifying Batch Size
To specify an initial batch size, specify the batchSize
in thecursor
field, as in the following example:
- db.orders.aggregate( [
- { $match: { status: "A" } },
- { $group: { _id: "$cust_id", total: { $sum: "$amount" } } },
- { $sort: { total: -1 } },
- { $limit: 2 }
- ],
- { cursor: { batchSize: 0 } }
- )
The {batchSize: 0 }
document specifies the size of the _initial_batch size only. Specify subsequent batch sizes to OP_GET_MORE operations as with other MongoDB cursors. AbatchSize
of 0
means an empty first batch and is useful if youwant to quickly get back a cursor or failure message, without doingsignificant server-side work.
Specify a Collation
New in version 3.4.
Collation allows users to specifylanguage-specific rules for string comparison, such as rules forlettercase and accent marks.
A collection myColl
has the following documents:
- { _id: 1, category: "café", status: "A" }
- { _id: 2, category: "cafe", status: "a" }
- { _id: 3, category: "cafE", status: "a" }
The following aggregation operation includes the Collationoption:
- db.myColl.aggregate(
- [ { $match: { status: "A" } }, { $group: { _id: "$category", count: { $sum: 1 } } } ],
- { collation: { locale: "fr", strength: 1 } }
- );
For descriptions on the collation fields, seeCollation Document.
Hint an Index
New in version 3.6.
Create a collection foodColl
with the following documents:
- db.foodColl.insert([
- { _id: 1, category: "cake", type: "chocolate", qty: 10 },
- { _id: 2, category: "cake", type: "ice cream", qty: 25 },
- { _id: 3, category: "pie", type: "boston cream", qty: 20 },
- { _id: 4, category: "pie", type: "blueberry", qty: 15 }
- ])
Create the following indexes:
- db.foodColl.createIndex( { qty: 1, type: 1 } );
- db.foodColl.createIndex( { qty: 1, category: 1 } );
The following aggregation operation includes the hint
option toforce the usage of the specified index:
- db.foodColl.aggregate(
- [ { $sort: { qty: 1 }}, { $match: { category: "cake", qty: 10 } }, { $sort: { type: -1 } } ],
- { hint: { qty: 1, category: 1 } }
- )
Override Default Read Concern
To override the default read concern level, use the readConcern
option. The getMore
command uses the readConcern
levelspecified in the originating aggregate
command.
You cannot use the $out
or the $merge
stagein conjunction with read concern "linearizable"
. Thatis, if you specify "linearizable"
read concern fordb.collection.aggregate()
, you cannot include eitherstages in the pipeline.
The following operation on a replica set specifies a read concern of "majority"
to read themost recent copy of the data confirmed as having been written to amajority of the nodes.
Important
- To use read concern level of
"majority"
, replicasets must use WiredTiger storage engine.
You can disable read concern "majority"
for a deploymentwith a three-member primary-secondary-arbiter (PSA) architecture;however, this has implications for change streams (in MongoDB 4.0 andearlier only) and transactions on sharded clusters. For more information,see Disable Read Concern Majority.
- Starting in MongoDB 4.2, you can specify read concern level
"majority"
for anaggregation that includes an$out
stage.
In MongoDB 4.0 and earlier, you cannot include the $out
stage to use "majority"
read concern for the aggregation.
- Regardless of the read concern level, the most recent data on anode may not reflect the most recent version of the data in the system.
- db.restaurants.aggregate(
- [ { $match: { rating: { $lt: 5 } } } ],
- { readConcern: { level: "majority" } }
- )
To ensure that a single thread can read its own writes, use"majority"
read concern and "majority"
write concern against the primary of the replica set.
See also