db.collection.aggregate()

Definition

  • db.collection.aggregate(pipeline, options)

mongo Shell Method

This page documents the mongo shell method, and doesnot refer to the MongoDB Node.js driver (or any other driver)method. For corresponding MongoDB driver API, refer to your specificMongoDB driver documentation instead.

Calculates aggregate values for the data in a collection or a view.

ParameterTypeDescriptionpipelinearrayA sequence of data aggregation operations or stages. See theaggregation pipeline operators for details.

Changed in version 2.6: The method can still accept the pipeline stages as separatearguments instead of as elements in an array; however, if you donot specify the pipeline as an array, you cannot specify theoptions parameter.

optionsdocumentOptional. Additional options that aggregate() passesto the aggregate command.

New in version 2.6: Available only if you specify the pipeline as an array.

The options document can contain the following fields and values:

FieldTypeDescriptionexplainbooleanOptional. Specifies to return the information on the processing of the pipeline. SeeReturn Information on Aggregation Pipeline Operation for an example.

Not available in multi-document transactions.allowDiskUsebooleanOptional. Enables writing to temporary files. When set to true, aggregationoperations can write data to the _tmp subdirectory in thedbPath directory. SeePerform Large Sort Operation with External Sort for an example.

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.cursordocumentOptional. Specifies the initial batch size for the cursor. The value of the cursorfield is a document with the field batchSize. SeeSpecify an Initial Batch Size for syntax and example.

New in version 2.6.

maxTimeMSnon-negative integerOptional. 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.bypassDocumentValidationbooleanOptional. Applicable only if you specify the $out or $merge aggregationstages.

Enables db.collection.aggregate to bypass document validationduring the operation. This lets you insert documents that do notmeet the validation requirements.

New in version 3.2.

readConcerndocumentOptional. Specifies the read concern.

Starting in MongoDB 3.6, the readConcern option has the followingsyntax: readConcern: { level: <value> }

Possible read concern levels are:

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.collationdocumentOptional.

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:

  1. collation: {
  2.    locale: <string>,
  3.    caseLevel: <boolean>,
  4.    caseFirst: <string>,
  5.    strength: <int>,
  6.    numericOrdering: <boolean>,
  7.    alternate: <string>,
  8.    maxVariable: <string>,
  9.    backwards: <boolean>
  10. }

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.

hintstring or documentOptional. 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.

Note

The hint does not apply to $lookup and$graphLookup stages.

New in version 3.6.

commentstringOptional. Users can specify an arbitrary string to help trace the operationthrough the database profiler, currentOp, and logs.

New in version 3.6.

writeConcerndocumentOptional. 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.

Returns:A cursor to the documents produced by the final stage ofthe aggregation pipeline operation, or if you include theexplain option, the document that providesdetails on the processing of the aggregation operation.If the pipeline includes the $out operator,aggregate() returns an empty cursor. See$out for more information.

Changed in version 2.6: The db.collection.aggregate() method returns acursor and can return result sets of any size. Previousversions returned all results in a single document, and theresult set was subject to a size limit of 16 megabytes.

Behavior

Error Handling

If an error occurs, the aggregate() helperthrows an exception.

Cursor Behavior

In the mongo shell, if the cursor returned from thedb.collection.aggregate() is not assigned to a variable usingthe var keyword, then the mongo shell automaticallyiterates the cursor up to 20 times. SeeIterate a Cursor in the mongo Shell for handling cursors in themongo shell.

Cursors returned from aggregation only supports cursor methods thatoperate on evaluated cursors (i.e. cursors whose first batch has beenretrieved), such as the following methods:

- cursor.hasNext()- cursor.next()- cursor.toArray()- cursor.forEach()- cursor.map()- cursor.objsLeftInBatch()- cursor.itcount()- cursor.pretty()

See also

For more information, seeAggregation Pipeline, Aggregation Reference,Aggregation Pipeline Limits, and aggregate.

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

db.collection.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 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 db.collection.aggregate() operation that do not includethe $out or $merge stages:

Starting in MongoDB 4.2, if the client that issued the db.collection.aggregate()disconnects before the operation completes, MongoDB marksthe db.collection.aggregate() for termination (i.e. killOp on theoperation).

Examples

The following examples use the collection orders that contains thefollowing documents:

  1. { _id: 1, cust_id: "abc1", ord_date: ISODate("2012-11-02T17:04:11.102Z"), status: "A", amount: 50 }
  2. { _id: 2, cust_id: "xyz1", ord_date: ISODate("2013-10-01T17:04:11.102Z"), status: "A", amount: 100 }
  3. { _id: 3, cust_id: "xyz1", ord_date: ISODate("2013-10-12T17:04:11.102Z"), status: "D", amount: 25 }
  4. { _id: 4, cust_id: "xyz1", ord_date: ISODate("2013-10-11T17:04:11.102Z"), status: "D", amount: 125 }
  5. { _id: 5, cust_id: "abc1", ord_date: ISODate("2013-11-12T17:04:11.102Z"), status: "A", amount: 25 }

Group by and Calculate a Sum

The following aggregation operation selects documents with status equalto "A", groups the matching documents by the cust_id field andcalculates the total for each cust_id field from the sum of theamount field, and sorts the results by the total field indescending order:

  1. db.orders.aggregate([
  2.                      { $match: { status: "A" } },
  3.                      { $group: { _id: "$cust_id", total: { $sum: "$amount" } } },
  4.                      { $sort: { total: -1 } }
  5.                    ])

The operation returns a cursor with the following documents:

  1. { "_id" : "xyz1", "total" : 100 }
  2. { "_id" : "abc1", "total" : 75 }

The mongo shell iterates the returned cursor automaticallyto print the results. See Iterate a Cursor in the mongo Shell forhandling cursors manually in the mongo shell.

Return Information on Aggregation Pipeline Operation

The following example uses db.collection.explain() to viewdetailed information regarding the execution plan of the aggregationpipeline.

  1. db.orders.explain().aggregate([
  2.    { $match: { status: "A" } },
  3.    { $group: { _id: "$cust_id", total: { $sum: "$amount" } } },
  4.    { $sort: { total: -1 } }
  5. ])

The operation returns a document that details the processing of theaggregation pipeline. For example, the document may show, among otherdetails, which index, if any, the operation used. [1]If the orders collection is a sharded collection, the documentwould also show the division of labor between the shards and the mergeoperation, and for targeted queries, the targeted shards.

Note

The intended readers of the explain output document are humans, andnot machines, and the output format is subject to change betweenreleases.

You can view more verbose explain output by passing theexecutionStats or allPlansExecution explain modes to thedb.collection.explain() method.

[1]Index Filters can affect the choice of indexused. See Index Filters for details.

Perform Large Sort Operation with 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:

  1. var results = db.stocks.aggregate(
  2.                                    [
  3.                                      { $project : { cusip: 1, date: 1, price: 1, _id: 0 } },
  4.                                      { $sort : { cusip : 1, date: 1 } }
  5.                                    ],
  6.                                    {
  7.                                      allowDiskUse: true
  8.                                    }
  9.                                  )

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.

Specify an Initial Batch Size

To specify an initial batch size for the cursor, use the followingsyntax for the cursor option:

  1. cursor: { batchSize: <int> }

For example, the following aggregation operation specifies theinitial batch size of 0 for the cursor:

  1. db.orders.aggregate(
  2.                      [
  3.                        { $match: { status: "A" } },
  4.                        { $group: { _id: "$cust_id", total: { $sum: "$amount" } } },
  5.                        { $sort: { total: -1 } },
  6.                        { $limit: 2 }
  7.                      ],
  8.                      {
  9.                        cursor: { batchSize: 0 }
  10.                      }
  11.                    )

A batchSize of 0 means an emptyfirst batch and is useful for quickly returning a cursor or failuremessage without doing significant server-side work. Specify subsequentbatch sizes to OP_GET_MORE operations as withother MongoDB cursors.

The mongo shell iterates the returned cursor automaticallyto print the results. See Iterate a Cursor in the mongo Shell forhandling cursors manually in the mongo shell.

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:

  1. { _id: 1, category: "café", status: "A" }
  2. { _id: 2, category: "cafe", status: "a" }
  3. { _id: 3, category: "cafE", status: "a" }

The following aggregation operation includes the collation option:

  1. db.myColl.aggregate(
  2.    [ { $match: { status: "A" } }, { $group: { _id: "$category", count: { $sum: 1 } } } ],
  3.    { collation: { locale: "fr", strength: 1 } }
  4. );

Note

If performing an aggregation that involves multiple views, such aswith $lookup or $graphLookup, the views musthave the same collation.

For descriptions on the collation fields, seeCollation Document.

Hint an Index

New in version 3.6.

Create a collection foodColl with the following documents:

  1. db.foodColl.insert([
  2.    { _id: 1, category: "cake", type: "chocolate", qty: 10 },
  3.    { _id: 2, category: "cake", type: "ice cream", qty: 25 },
  4.    { _id: 3, category: "pie", type: "boston cream", qty: 20 },
  5.    { _id: 4, category: "pie", type: "blueberry", qty: 15 }
  6. ])

Create the following indexes:

  1. db.foodColl.createIndex( { qty: 1, type: 1 } );
  2. db.foodColl.createIndex( { qty: 1, category: 1 } );

The following aggregation operation includes the hint option toforce the usage of the specified index:

  1. db.foodColl.aggregate(
  2.    [ { $sort: { qty: 1 }}, { $match: { category: "cake", qty: 10  } }, { $sort: { type: -1 } } ],
  3.    { hint: { qty: 1, category: 1 } }
  4. )

Override readConcern

Use the readConcern option to specify the read concern forthe operation.

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 aRead Concern of "majority" to read themost recent copy of the data confirmed as having been written to amajority of the nodes.

Note

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.

  • 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.

  • 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.
  1. db.restaurants.aggregate(
  2.    [ { $match: { rating: { $lt: 5 } } } ],
  3.    { readConcern: { level: "majority" } }
  4. )

Specify a Comment

A collection named movies contains documents formatted as such:

  1. {
  2.   "_id" : ObjectId("599b3b54b8ffff5d1cd323d8"),
  3.   "title" : "Jaws",
  4.   "year" : 1975,
  5.   "imdb" : "tt0073195"
  6. }

The following aggregation operation finds movies created in 1995 and includesthe comment option to provide tracking information in the logs,the db.system.profile collection, and db.currentOp.

  1. db.movies.aggregate( [ { $match: { year : 1995 } } ], { comment : "match_all_movies_from_1995" } ).pretty()

On a system with profiling enabled, you can then query the system.profilecollection to see all recent similar aggregations, as shown below:

  1. db.system.profile.find( { "command.aggregate": "movies", "command.comment" : "match_all_movies_from_1995" } ).sort( { ts : -1 } ).pretty()

This will return a set of profiler results in the following format:

  1. {
  2.   "op" : "command",
  3.   "ns" : "video.movies",
  4.   "command" : {
  5.     "aggregate" : "movies",
  6.     "pipeline" : [
  7.       {
  8.         "$match" : {
  9.           "year" : 1995
  10.         }
  11.       }
  12.     ],
  13.     "comment" : "match_all_movies_from_1995",
  14.     "cursor" : {
  15.  
  16.     },
  17.     "$db" : "video"
  18.   },
  19.   ...
  20. }

An application can encode any arbitrary information in the comment in orderto more easily trace or identify specific operations through the system.For instance, an application might attach a string comment incorporating itsprocess ID, thread ID, client hostname, and the user who issued the command.