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:

collation: {   locale: <string>,   caseLevel: <boolean>,   caseFirst: <string>,   strength: <int>,   numericOrdering: <boolean>,   alternate: <string>,   maxVariable: <string>,   backwards: <boolean>}

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:

• $collStats
• $currentOp
• $indexStats
• $listLocalSessions
• $listSessions
• $merge
• $out
• $planCacheStats

You also cannot specify the explain option.

• For cursors created outside of a transaction, you cannot callgetMore inside the transaction.
• For cursors created in a transaction, you cannot callgetMore 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 aggregatedisconnects 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 explainto 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 usedDiskindicator if any aggregation stage wrote data to temporary files dueto memory restrictions.

See also

db.collection.aggregate()

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 readConcernoption. 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 $outstage 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

db.collection.aggregate()