Aggregation Pipeline Quick Reference

Aggregation Pipeline Quick Reference

Note

For details on specific operator, including syntax and examples,click on the specific operator to go to its reference page.

Stages

Stages (db.collection.aggregate)

In the db.collection.aggregate method, pipeline stages appearin an array. Documents pass through the stages in sequence. All exceptthe $out, $merge, and $geoNear stages can appearmultiple times in a pipeline.

db.collection.aggregate( [ { <stage> }, ... ] )

Stage	Description
`$addFields`	Adds new fields to documents. Similar to`$project`, `$addFields` reshapes eachdocument in the stream; specifically, by adding new fields tooutput documents that contain both the existing fieldsfrom the input documents and the newly added fields.`$set` is an alias for `$addFields`.
`$bucket`	Categorizes incoming documents into groups, called buckets,based on a specified expression and bucket boundaries.
`$bucketAuto`	Categorizes incoming documents into a specific number ofgroups, called buckets, based on a specified expression.Bucket boundaries are automatically determined in an attemptto evenly distribute the documents into the specified numberof buckets.
`$collStats`	Returns statistics regarding a collection or view.
`$count`	Returns a count of the number of documents at this stage ofthe aggregation pipeline.
`$facet`	Processes multiple aggregation pipelines within a single stage on the same setof input documents. Enables the creation of multi-facetedaggregations capable of characterizing data across multipledimensions, or facets, in a single stage.
`$geoNear`	Returns an ordered stream of documents based on the proximity to ageospatial point. Incorporates the functionality of`$match`, `$sort`, and `$limit` forgeospatial data. The output documents include an additional distancefield and can include a location identifier field.
`$graphLookup`	Performs a recursive search on a collection. To each outputdocument, adds a new array field that contains the traversalresults of the recursive search for that document.
`$group`	Groups input documents by a specified identifier expressionand applies the accumulator expression(s), if specified, toeach group. Consumes all input documents and outputs onedocument per each distinct group. The output documents onlycontain the identifier field and, if specified, accumulatedfields.
`$indexStats`	Returns statistics regarding the use of each index for thecollection.
`$limit`	Passes the first n documents unmodified to the pipelinewhere n is the specified limit. For each input document,outputs either one document (for the first n documents) orzero documents (after the first n documents).
`$listSessions`	Lists all sessions that have been active long enough topropagate to the `system.sessions` collection.
`$lookup`	Performs a left outer join to another collection in thesame database to filter in documents from the “joined”collection for processing.
`$match`	Filters the document stream to allow only matching documentsto pass unmodified into the next pipeline stage.`$match` uses standard MongoDB queries. For eachinput document, outputs either one document (a match) or zerodocuments (no match).
`$merge`	Writes the resulting documents of the aggregation pipeline toa collection. The stage can incorporate (insert newdocuments, merge documents, replace documents, keep existingdocuments, fail the operation, process documents with acustom update pipeline) the results into an outputcollection. To use the `$merge` stage, it must bethe last stage in the pipeline.New in version 4.2.
`$out`	Writes the resulting documents of the aggregation pipeline toa collection. To use the `$out` stage, it must bethe last stage in the pipeline.
`$planCacheStats`	Returns plan cache information for acollection.
`$project`	Reshapes each document in the stream, such as by adding newfields or removing existing fields. For each input document,outputs one document.See also `$unset` for removing existing fields.
`$redact`	Reshapes each document in the stream by restricting thecontent for each document based on information stored in thedocuments themselves. Incorporates the functionality of`$project` and `$match`. Can be used toimplement field level redaction. For each input document,outputs either one or zero documents.
`$replaceRoot`	Replaces a document with the specified embedded document. Theoperation replaces all existing fields in the input document,including the `id` field. Specify a document embedded inthe input document to promote the embedded document to thetop level.`$replaceWith` is an alias for`$replaceRoot` stage.
`$replaceWith`	Replaces a document with the specified embedded document. Theoperation replaces all existing fields in the input document,including the `_id` field. Specify a document embedded inthe input document to promote the embedded document to thetop level.`$replaceWith` is an alias for`$replaceRoot` stage.
`$sample`	Randomly selects the specified number of documents from itsinput.
`$set`	Adds new fields to documents. Similar to`$project`, `$set` reshapes eachdocument in the stream; specifically, by adding new fields tooutput documents that contain both the existing fieldsfrom the input documents and the newly added fields.`$set` is an alias for `$addFields` stage.
`$skip`	Skips the first _n documents where n is the specified skipnumber and passes the remaining documents unmodified to thepipeline. For each input document, outputs either zerodocuments (for the first n documents) or one document (ifafter the first n documents).
`$sort`	Reorders the document stream by a specified sort key. Onlythe order changes; the documents remain unmodified. For eachinput document, outputs one document.
`$sortByCount`	Groups incoming documents based on the value of a specifiedexpression, then computes the count of documents in eachdistinct group.
`$unset`	Removes/excludes fields from documents.`$unset` is an alias for `$project` stagethat removes fields.
`$unwind`	Deconstructs an array field from the input documents tooutput a document for each element. Each output documentreplaces the array with an element value. For each inputdocument, outputs n documents where n is the number ofarray elements and can be zero for an empty array.

Stages (db.aggregate)

Starting in version 3.6, MongoDB also provides the thedb.aggregate method:

db.aggregate( [ { <stage> }, ... ] )

The following stages use the db.aggregate() method and notthe db.collection.aggregate() method.

Stage	Description
`$currentOp`	Returns information on active and/or dormant operations forthe MongoDB deployment.
`$listLocalSessions`	Lists all active sessions recently in use on the currentlyconnected `mongos` or `mongod`instance. These sessions may have not yet propagated to the`system.sessions` collection.

Stages Available for Updates

Starting in MongoDB 4.2, you can use the aggregation pipeline forupdates in:

Command	`mongo` Shell Methods
`findAndModify`	`db.collection.findAndModify()db.collection.findOneAndUpdate()`
`update`	`db.collection.updateOne()db.collection.updateMany()db.collection.update()`

For the updates, the pipeline can consist of the following stages:

$addFields and its alias $set
$project and its alias $unset
$replaceRoot and its alias $replaceWith.

Expressions

Expressions can include field paths and system variables, literals,expression objects, andexpression operators.Expressions can be nested.

Field Path and System Variables

Aggregation expressions use field path to access fields in theinput documents. To specify a field path, use a string that prefixeswith a dollar sign $ the field name or the dotted field name, if the field is in the embedded document. Forexample, "$user" to specify the field path for the user fieldor "$user.name" to specify the field path to "user.name" field.

"$<field>" is equivalent to "$$CURRENT.<field>" where theCURRENT is a system variable that defaults to the root ofthe current object in the most stages, unless stated otherwise inspecific stages. CURRENT can be rebound.

Along with the CURRENT system variable, other systemvariables are also available for use inexpressions. To access variables in expressions, prefix the variablename with $$.

Literals

Literals can be of any type. However, MongoDB parses string literalsthat start with a dollar sign $ as a path to a field andnumeric/boolean literals in expression objects as projection flags. To avoidparsing literals, use the $literal expression.

Expression Objects

Expression objects have the following form:

{ <field1>: <expression1>, ... }

If the expressions are numeric or boolean literals, MongoDB treats theliterals as projection flags (e.g. 1 or true to include thefield), valid only in the $project stage. To avoid treatingnumeric or boolean literals as projection flags, use the$literal expression to wrap the numeric or booleanliterals.

Operator Expressions

Operator expressions are similar to functions that take arguments.In general, these expressions take an array of argumentsand have the following form:

{ <operator>: [ <argument1>, <argument2> ... ] }

If operator accepts a single argument, you can omit the outer arraydesignating the argument list:

{ <operator>: <argument> }

To avoid parsing ambiguity if the argument is a literal array, you mustwrap the literal array in a $literal expression or keepthe outer array that designates the argument list.

Arithmetic Expression Operators

Arithmetic expressions perform mathematic operations on numbers. Somearithmetic expressions can also support date arithmetic.

Name	Description
`$abs`	Returns the absolute value of a number.
`$add`	Adds numbers to return the sum, or adds numbers and a date toreturn a new date. If adding numbers and a date, treats thenumbers as milliseconds. Accepts any number of argumentexpressions, but at most, one expression can resolve to adate.
`$ceil`	Returns the smallest integer greater than or equal to the specified number.
`$divide`	Returns the result of dividing the first number by thesecond. Accepts two argument expressions.
`$exp`	Raises e to the specified exponent.
`$floor`	Returns the largest integer less than or equal to thespecified number.
`$ln`	Calculates the natural log of a number.
`$log`	Calculates the log of a number in the specified base.
`$log10`	Calculates the log base 10 of a number.
`$mod`	Returns the remainder of the first number divided by thesecond. Accepts two argument expressions.
`$multiply`	Multiplies numbers to return the product. Accepts any numberof argument expressions.
`$pow`	Raises a number to the specified exponent.
`$round`	Rounds a number to to a whole integer or to a specifieddecimal place.
`$sqrt`	Calculates the square root.
`$subtract`	Returns the result of subtracting the second value from thefirst. If the two values are numbers, return the difference.If the two values are dates, return the difference inmilliseconds. If the two values are a date and a number inmilliseconds, return the resulting date. Accepts two argumentexpressions. If the two values are a date and a number,specify the date argument first as it is not meaningful tosubtract a date from a number.
`$trunc`	Truncates a number to a whole integer or to a specifieddecimal place.

Array Expression Operators

`$arrayElemAt`	Returns the element at the specified array index.
`$arrayToObject`	Converts an array of key value pairs to a document.
`$concatArrays`	Concatenates arrays to return the concatenated array.
`$filter`	Selects a subset of the array to return an array with onlythe elements that match the filter condition.
`$in`	Returns a boolean indicating whether a specified value is inan array.
`$indexOfArray`	Searches an array for an occurence of a specified value andreturns the array index of the first occurence. If thesubstring is not found, returns `-1`.
`$isArray`	Determines if the operand is an array. Returns a boolean.
`$map`	Applies a subexpression to each element of an array andreturns the array of resulting values in order. Accepts namedparameters.
`$objectToArray`	Converts a document to an array of documents representingkey-value pairs.
`$range`	Outputs an array containing a sequence of integers accordingto user-defined inputs.
`$reduce`	Applies an expression to each element in an array andcombines them into a single value.
`$reverseArray`	Returns an array with the elements in reverse order.
`$size`	Returns the number of elements in the array. Accepts a singleexpression as argument.
`$slice`	Returns a subset of an array.
`$zip`	Merge two arrays together.

Boolean Expression Operators

Boolean expressions evaluate their argument expressions as booleans andreturn a boolean as the result.

In addition to the false boolean value, Boolean expression evaluatesas false the following: null, 0, and undefinedvalues. The Boolean expression evaluates all other values as true,including non-zero numeric values and arrays.

Name	Description
`$and`	Returns `true` only when all its expressions evaluate to`true`. Accepts any number of argument expressions.
`$not`	Returns the boolean value that is the opposite of itsargument expression. Accepts a single argument expression.
`$or`	Returns `true` when any of its expressions evaluates to`true`. Accepts any number of argument expressions.

Comparison Expression Operators

Comparison expressions return a boolean except for $cmpwhich returns a number.

The comparison expressions take two argument expressions and compareboth value and type, using the specified BSON comparison order for values of different types.

`$cmp`	Returns `0` if the two values are equivalent, `1` if thefirst value is greater than the second, and `-1` if thefirst value is less than the second.
`$eq`	Returns `true` if the values are equivalent.
`$gt`	Returns `true` if the first value is greater than thesecond.
`$gte`	Returns `true` if the first value is greater than or equalto the second.
`$lt`	Returns `true` if the first value is less than the second.
`$lte`	Returns `true` if the first value is less than or equal tothe second.
`$ne`	Returns `true` if the values are not equivalent.

Conditional Expression Operators

Name	Description
`$cond`	A ternary operator that evaluates one expression, anddepending on the result, returns the value of one of theother two expressions. Accepts either three expressions in anordered list or three named parameters.
`$ifNull`	Returns either the non-null result of the first expression orthe result of the second expression if the first expressionresults in a null result. Null result encompasses instancesof undefined values or missing fields. Accepts twoexpressions as arguments. The result of the second expressioncan be null.
`$switch`	Evaluates a series of case expressions. When it finds anexpression which evaluates to `true`, `$switch` executesa specified expression and breaks out of the control flow.

Date Expression Operators

The following operators returns date objects or components of a dateobject:

Name	Description
`$dateFromParts`	Constructs a BSON Date object given the date’s constituentparts.
`$dateFromString`	Converts a date/time string to a date object.
`$dateToParts`	Returns a document containing the constituent parts of a date.
`$dateToString`	Returns the date as a formatted string.
`$dayOfMonth`	Returns the day of the month for a date as a number between 1and 31.
`$dayOfWeek`	Returns the day of the week for a date as a number between 1(Sunday) and 7 (Saturday).
`$dayOfYear`	Returns the day of the year for a date as a number between 1and 366 (leap year).
`$hour`	Returns the hour for a date as a number between 0 and 23.
`$isoDayOfWeek`	Returns the weekday number in ISO 8601 format, ranging from`1` (for Monday) to `7` (for Sunday).
`$isoWeek`	Returns the week number in ISO 8601 format, ranging from`1` to `53`. Week numbers start at `1` with the week(Monday through Sunday) that contains the year’s firstThursday.
`$isoWeekYear`	Returns the year number in ISO 8601 format. The year startswith the Monday of week 1 (ISO 8601) and ends with the Sundayof the last week (ISO 8601).
`$millisecond`	Returns the milliseconds of a date as a number between 0 and999.
`$minute`	Returns the minute for a date as a number between 0 and 59.
`$month`	Returns the month for a date as a number between 1 (January)and 12 (December).
`$second`	Returns the seconds for a date as a number between 0 and 60(leap seconds).
`$toDate`	Converts value to a Date.New in version 4.0.
`$week`	Returns the week number for a date as a number between 0 (thepartial week that precedes the first Sunday of the year) and53 (leap year).
`$year`	Returns the year for a date as a number (e.g. 2014).

The following arithmetic operators can take date operands:

Name	Description
`$add`	Adds numbers and a date to return a new date. If addingnumbers and a date, treats the numbers as milliseconds.Accepts any number of argument expressions, but at most, oneexpression can resolve to a date.
`$subtract`	Returns the result of subtracting the second value from thefirst. If the two values are dates, return the difference inmilliseconds. If the two values are a date and a number inmilliseconds, return the resulting date. Accepts two argumentexpressions. If the two values are a date and a number,specify the date argument first as it is not meaningful tosubtract a date from a number.

Literal Expresion Operator

Name	Description
`$literal`	Return a value without parsing. Use for values that theaggregation pipeline may interpret as an expression. Forexample, use a `$literal` expression to a stringthat starts with a `$` to avoid parsing as a field path.

Object Expression Operators

Name	Description
`$mergeObjects`	Combines multiple documents into a single document.New in version 3.6.
`$objectToArray`	Converts a document to an array of documents representingkey-value pairs.New in version 3.6.

Set Expression Operators

Set expressions performs set operation on arrays, treating arrays assets. Set expressions ignores the duplicate entries in each input arrayand the order of the elements.

If the set operation returns a set, the operation filters outduplicates in the result to output an array that contains only uniqueentries. The order of the elements in the output array is unspecified.

If a set contains a nested array element, the set expression does not descendinto the nested array but evaluates the array at top-level.

Name	Description
`$allElementsTrue`	Returns `true` if no element of a set evaluates to`false`, otherwise, returns `false`. Accepts a singleargument expression.
`$anyElementTrue`	Returns `true` if any elements of a set evaluate to`true`; otherwise, returns `false`. Accepts a singleargument expression.
`$setDifference`	Returns a set with elements that appear in the first setbut not in the second set; i.e. performs a relativecomplement)of the second set relative to the first. Accepts exactlytwo argument expressions.
`$setEquals`	Returns `true` if the input sets have the same distinctelements. Accepts two or more argument expressions.
`$setIntersection`	Returns a set with elements that appear in all of theinput sets. Accepts any number of argument expressions.
`$setIsSubset`	Returns `true` if all elements of the first set appearin the second set, including when the first set equals thesecond set; i.e. not a strict subset. Accepts exactlytwo argument expressions.
`$setUnion`	Returns a set with elements that appear in any of theinput sets.

String Expression Operators

String expressions, with the exception of$concat, only have a well-defined behavior for strings of ASCII characters.

$concat behavior is well-defined regardless ofthe characters used.

Name	Description
`$concat`	Concatenates any number of strings.
`$dateFromString`	Converts a date/time string to a date object.
`$dateToString`	Returns the date as a formatted string.
`$indexOfBytes`	Searches a string for an occurence of a substring and returnsthe UTF-8 byte index of the first occurence. If the substringis not found, returns `-1`.
`$indexOfCP`	Searches a string for an occurence of a substring and returnsthe UTF-8 code point index of the first occurence. If thesubstring is not found, returns `-1`
`$ltrim`	Removes whitespace or the specified characters from thebeginning of a string.New in version 4.0.
`$regexFind`	Applies a regular expression (regex) to a string and returnsinformation on the first matched substring.New in version 4.2.
`$regexFindAll`	Applies a regular expression (regex) to a string and returnsinformation on the all matched substrings.New in version 4.2.
`$regexMatch`	Applies a regular expression (regex) to a string and returnsa boolean that indicates if a match is found or not.New in version 4.2.
`$rtrim`	Removes whitespace or the specified characters from theend of a string.New in version 4.0.
`$split`	Splits a string into substrings based on a delimiter. Returnsan array of substrings. If the delimiter is not found withinthe string, returns an array containing the original string.
`$strLenBytes`	Returns the number of UTF-8 encoded bytes in a string.
`$strLenCP`	Returns the number of UTF-8 code points in a string.
`$strcasecmp`	Performs case-insensitive string comparison and returns:`0` if two strings are equivalent, `1` if the firststring is greater than the second, and `-1` if the firststring is less than the second.
`$substr`	Deprecated. Use `$substrBytes` or`$substrCP`.
`$substrBytes`	Returns the substring of a string. Starts with the characterat the specified UTF-8 byte index (zero-based) in the stringand continues for the specified number of bytes.
`$substrCP`	Returns the substring of a string. Starts with the characterat the specified UTF-8 code point (CP) index(zero-based) in the string and continues for the number ofcode points specified.
`$toLower`	Converts a string to lowercase. Accepts a single argumentexpression.
`$toString`	Converts value to a string.New in version 4.0.
`$trim`	Removes whitespace or the specified characters from thebeginning and end of a string.New in version 4.0.
`$toUpper`	Converts a string to uppercase. Accepts a single argumentexpression.

Text Expression Operator

Name	Description
`$meta`	Access text search metadata.

Trigonometry Expression Operators

Trigonometry expressions perform trigonometric operations on numbers.Values that represent angles are always input or output in radians. Use$degreesToRadians and $radiansToDegrees toconvert between degree and radian measurements.

Name	Description
`$sin`	Returns the sine of a value that is measured in radians.
`$cos`	Returns the cosine of a value that is measured in radians.
`$tan`	Returns the tangent of a value that is measured in radians.
`$asin`	Returns the inverse sin (arc sine) of a value in radians.
`$acos`	Returns the inverse cosine (arc cosine) of a value in radians.
`$atan`	Returns the inverse tangent (arc tangent) of a value inradians.
`$atan2`	Returns the inverse tangent (arc tangent) of `y / x` inradians, where `y` and `x` are the first and secondvalues passed to the expression respectively.
`$asinh`	Returns the inverse hyperbolic sine (hyperbolic arc sine) of avalue in radians.
`$acosh`	Returns the inverse hyperbolic cosine (hyperbolic arc cosine)of a value in radians.
`$atanh`	Returns the inverse hyperbolic tangent (hyperbolic arctangent) of a value in radians.
`$degreesToRadians`	Converts a value from degrees to radians.
`$radiansToDegrees`	Converts a value from radians to degrees.

Type Expression Operators

Name	Description
`$convert`	Converts a value to a specified type.New in version 4.0.
`$toBool`	Converts value to a boolean.New in version 4.0.
`$toDate`	Converts value to a Date.New in version 4.0.
`$toDecimal`	Converts value to a Decimal128.New in version 4.0.
`$toDouble`	Converts value to a double.New in version 4.0.
`$toInt`	Converts value to an integer.New in version 4.0.
`$toLong`	Converts value to a long.New in version 4.0.
`$toObjectId`	Converts value to an ObjectId.New in version 4.0.
`$toString`	Converts value to a string.New in version 4.0.
`$type`	Return the BSON data type of the field.

Accumulators ($group)

Available for use in the $group stage, accumulators areoperators that maintain their state (e.g. totals, maximums, minimums,and related data) as documents progress through the pipeline.

When used as accumulators in the $group stage, theseoperators take as input a single expression, evaluating the expressiononce for each input document, and maintain their stage for the group ofdocuments that share the same group key.

Name	Description
`$addToSet`	Returns an array of unique expression values for eachgroup. Order of the array elements is undefined.
`$avg`	Returns an average of numerical values. Ignores non-numeric values.
`$first`	Returns a value from the first document for each group. Orderis only defined if the documents are in a defined order.
`$last`	Returns a value from the last document for each group. Orderis only defined if the documents are in a defined order.
`$max`	Returns the highest expression value for each group.
`$mergeObjects`	Returns a document created by combining the input documentsfor each group.
`$min`	Returns the lowest expression value for each group.
`$push`	Returns an array of expression values for each group.
`$stdDevPop`	Returns the population standard deviation of the input values.
`$stdDevSamp`	Returns the sample standard deviation of the input values.
`$sum`	Returns a sum of numerical values. Ignores non-numeric values.

Accumulators (in Other Stages)

Some operators that are available as accumulators for the$group stage are also available for use in other stages butnot as accumulators. When used in these other stages, these operatorsdo not maintain their state and can take as input either a singleargument or multiple arguments. For details, refer to the specificoperator page.

Changed in version 3.2.

The following accumulator operators are also available in the$project, $addFields, and $set stages.

Name	Description
`$avg`	Returns an average of the specified expression or list ofexpressions for each document. Ignores non-numeric values.
`$max`	Returns the maximum of the specified expression or list ofexpressions for each document
`$min`	Returns the minimum of the specified expression or list ofexpressions for each document
`$stdDevPop`	Returns the population standard deviation of the input values.
`$stdDevSamp`	Returns the sample standard deviation of the input values.
`$sum`	Returns a sum of numerical values. Ignores non-numeric values.

Variable Expression Operators

Name	Description
`$let`	Defines variables for use within the scope of a subexpressionand returns the result of the subexpression. Accepts namedparameters.Accepts any number of argument expressions.

Index of Expression Operators

$abs$add$addToSet$allElementsTrue$and$anyElementTrue$arrayElemAt$arrayToObject$avg$cmp$concat$concatArrays$cond$dateFromParts$dateToParts$dateFromString$dateToString$dayOfMonth$dayOfWeek$dayOfYear$divide

$eq$exp$filter$first$floor$gt$gte$hour$ifNull$in$indexOfArray$indexOfBytes$indexOfCP$isArray$isoDayOfWeek$isoWeek$isoWeekYear$last$let$literal

$ln$log$log10$lt$lte$ltrim$map$max$mergeObjects$meta$min$millisecond$minute$mod$month$multiply$ne$not

$objectToArray$or$pow$push$range$reduce$reverseArray$rtrim$second$setDifference$setEquals$setIntersection$setIsSubset$setUnion$size$slice$split

$sqrt$stdDevPop$stdDevSamp$strcasecmp$strLenBytes$strLenCP$substr$substrBytes$substrCP$subtract$sum$switch$toLower$toUpper$trim$trunc$type$week$year$zip