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:

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.

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 would also show 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.

Perform Large Sort Operation with External Sort

Each individual pipeline stage has a limit of 100 megabytes of RAM. By default, if a stage exceeds this limit, MongoDB produces an error. To allow pipeline processing to take up more space, set the allowDiskUse option to true to enable writing data to temporary files, as in the following example:

var results = db.stocks.aggregate(
  [
    { $sort : { cusip : 1, date: 1 } }
  ],
  {
    allowDiskUse: 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.

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 } }
)