aggregate

Command Fields

The aggregate command takes the following fields as arguments:

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

{
  <variable_name_1>: <expression_1>,
  ...,
  <variable_name_n>: <expression_n>
}

MongoDB removes the use of aggregate command without the cursor option unless the command includes the explain option. Unless you include the explain option, you must specify 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 pipeline Aggregation Pipeline, Aggregation Reference, and Aggregation Pipeline Limits.

Session Idle Timeout

MongoDB drivers and mongosh associate all operations with a server session, with the exception of unacknowledged write operations. For operations not explicitly associated with a session (i.e. using Mongo.startSession()), MongoDB drivers and mongosh create an implicit session and associate it with the operation.

If a session is idle for longer than 30 minutes, the MongoDB server marks that session as expired and may close it at any time. When the MongoDB server closes the session, it also kills any in-progress operations and open cursors associated with the session. This includes cursors configured with noCursorTimeout() or a maxTimeMS() greater than 30 minutes.

For operations that return a cursor, if the cursor may be idle for longer than 30 minutes, issue the operation within an explicit session using Mongo.startSession() and periodically refresh the session using the refreshSessions command. See Session Idle Timeout for more information.

Client Disconnection

For aggregate operation that do not include the $out or $merge stages:

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

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 on the articles collection to calculate the count of each distinct element 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 mongosh, this operation can use the db.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 admin database. The first stage runs the $currentOp operation and the second stage filters the results of that operation.

db.adminCommand( {
   aggregate : 1,
   pipeline : [ {
      $currentOp : { allUsers : true, idleConnections : true } }, {
      $match : { shard : "shard01" }
      }
   ],
   cursor : { }
} )

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

Aggregate Data using 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:

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.

Aggregate Data Specifying Batch Size

To specify an initial batch size, specify the batchSize in the cursor 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 { 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.

Specify a Collation

Collation allows users to specify language-specific rules for string comparison, such as rules for lettercase 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 Collation option:

db.myColl.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 foodColl with the following documents:

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

The following aggregation operation includes the hint option to force 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 level specified in the originating aggregate command.

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

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.

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.runCommand( {
   aggregate: db.cakeSales.getName(),
   pipeline: [
      { $match: {
           $expr: { $gt: [ "$salesTotal", "$$targetTotal" ] }
      } },
    ],
    cursor: {},
    let: { targetTotal: 3000 }
} )