db.collection.aggregate()

Error Handling

If an error occurs, the aggregate() helper throws an exception.

Cursor Behavior

In mongosh, if the cursor returned from the db.collection.aggregate() is not assigned to a variable using the var keyword, then mongosh automatically iterates the cursor up to 20 times. See Iterate a Cursor in mongosh for handling cursors in mongosh.

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

For more information, see:

• Aggregation Pipeline

• Aggregation Reference

• Aggregation Pipeline Limits

• aggregate

Sessions

For cursors created inside a session, you cannot call getMore outside the session.

Similarly, for cursors created outside of a session, you cannot call getMore inside a session.

Transactions

db.collection.aggregate() can be used inside distributed transactions.

However, the following stages are not allowed within transactions:

• $collStats

• $currentOp

• $indexStats

• $listLocalSessions

• $listSessions

• $out

• $merge

• $planCacheStats

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.

Client Disconnection

For db.collection.aggregate() operation that do not include the $out or $merge stages:

Starting in MongoDB 4.2, if the client that issued db.collection.aggregate() disconnects before the operation completes, MongoDB marks db.collection.aggregate() for termination using killOp.

Query Settings

You can use query settings to set index hints, set operation rejection filters, and other fields. The settings apply to the query shape on the entire cluster. The cluster retains the settings after shutdown.

The query optimizer uses the query settings as an additional input during query planning, which affects the plan selected to run the query. You can also use query settings to block a query shape.

To add query settings and explore examples, see setQuerySettings.

You can add query settings for find, distinct, and aggregate commands.

Query settings have more functionality and are preferred over deprecated index filters.

To remove query settings, use removeQuerySettings. To obtain the query settings, use a $querySettings stage in an aggregation pipeline.

Group by and Calculate a Sum

The following aggregation operation selects documents with status equal to "A", groups the matching documents by the cust_id field and calculates the total for each cust_id field from the sum of the amount field, and sorts the results by the total field in descending order:

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

The operation returns a cursor with the following documents:

[
   { _id: "xyz1", total: 100 },
   { _id: "abc1", total: 75 }
]

mongosh iterates the returned cursor automatically to print the results. See Iterate a Cursor in mongosh for handling cursors manually in mongosh.

Return Information on Aggregation Pipeline Operation

The following example uses db.collection.explain() to view detailed information regarding the execution plan of the aggregation pipeline.

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

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

You can view more verbose explain output by passing the executionStats or allPlansExecution explain modes to the db.collection.explain() method.

Interaction with allowDiskUseByDefault

Starting in MongoDB 6.0, pipeline stages that require more than 100 megabytes of memory to execute write temporary files to disk by default. These temporary files last for the duration of the pipeline execution and can influence storage space on your instance. In earlier versions of MongoDB, you must pass { allowDiskUse: true } to individual find and aggregate commands to enable this behavior.

Individual find and aggregate commands can override the allowDiskUseByDefault parameter by either:

• Using { allowDiskUse: true } to allow writing temporary files out to disk when allowDiskUseByDefault is set to false

• Using { allowDiskUse: false } to prohibit writing temporary files out to disk when allowDiskUseByDefault is set to true

The profiler log messages and diagnostic log messages includes a usedDisk indicator if any aggregation stage wrote data to temporary files due to memory restrictions.

For more information, see Aggregation Pipeline Limits.

Specify an Initial Batch Size

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

cursor: { batchSize: <int> }

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

db.orders.aggregate(
                     [
                       { $match: { status: "A" } },
                       { $group: { _id: "$cust_id", total: { $sum: "$amount" } } },
                       { $sort: { total: -1 } },
                       { $limit: 2 }
                     ],
                     {
                       cursor: { batchSize: 0 }
                     }
                   )

The { cursor: { batchSize: 0 } } document, which specifies the size of the initial batch size, indicates an empty first batch. This batch size is useful for quickly returning a cursor or failure message without doing significant server-side work.

To specify batch size for subsequent getMore operations (after the initial batch), use the batchSize field when running the getMore command.

mongosh iterates the returned cursor automatically to print the results. See Iterate a Cursor in mongosh for handling cursors manually in mongosh.

Specify a Collation

Collation allows users to specify language-specific rules for string comparison, such as rules for lettercase and accent marks.

A collection restaurants has the following documents:

db.restaurants.insertMany( [
   { _id: 1, category: "café", status: "A" },
   { _id: 2, category: "cafe", status: "a" },
   { _id: 3, category: "cafE", status: "a" }
] )

The following aggregation operation includes the collation option:

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

For descriptions on the collation fields, see Collation Document.

Hint an Index

Create a collection food with the following documents:

db.food.insertMany( [
   { _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.food.createIndex( { qty: 1, type: 1 } );
db.food.createIndex( { qty: 1, category: 1 } );

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

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

Override readConcern

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

You cannot use the $out or the $merge stage in conjunction with read concern "linearizable". That is, if you specify "linearizable" read concern for db.collection.aggregate(), you cannot include either stages in the pipeline.

The following operation on a replica set specifies a Read Concern of "majority" to read the most recent copy of the data confirmed as having been written to a majority of the nodes.

db.restaurants.aggregate(
   [ { $match: { rating: { $lt: 5 } } } ],
   { readConcern: { level: "majority" } }
)

Specify a Comment

A collection named movies contains documents formatted as such:

db.movies.insertOne(
   {
      _id: ObjectId("599b3b54b8ffff5d1cd323d8"),
      title: "Jaws",
      year: 1975,
      imdb: "tt0073195"
   }
)

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

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.profile collection to see all recent similar aggregations, as shown below:

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:

{
  "op" : "command",
  "ns" : "video.movies",
  "command" : {
    "aggregate" : "movies",
    "pipeline" : [
      {
        "$match" : {
          "year" : 1995
        }
      }
    ],
    "comment" : "match_all_movies_from_1995",
    "cursor" : {
    },
    "$db" : "video"
  },
  ...
}

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

Use Variables in let

To define variables that you can access elsewhere in the command, use the let option.

Create a collection cakeSales containing sales for cake flavors:

db.cakeSales.insertMany( [
   { _id: 1, flavor: "chocolate", salesTotal: 1580 },
   { _id: 2, flavor: "strawberry", salesTotal: 4350 },
   { _id: 3, flavor: "cherry", salesTotal: 2150 }
] )

The following example:

• retrieves the cake that has a salesTotal greater than 3000, which is the cake with an _id of 2

• defines a targetTotal variable in let, which is referenced in $gt as $$targetTotal

db.cakeSales.aggregate(
   [
      { $match: {
         $expr: { $gt: [ "$salesTotal", "$$targetTotal" ] }
      } }
   ],
   { let: { targetTotal: 3000 } }
)