Aggregation Pipeline Quick Reference¶
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 appear
in an array. Documents pass through the stages in sequence. All except
the $out
, $merge
, and $geoNear
stages can appear
multiple times in a pipeline.
db.collection.aggregate( [ { <stage> }, ... ] )
Stage | Description |
---|---|
Adds new fields to documents. Similar to
| |
Categorizes incoming documents into groups, called buckets,
based on a specified expression and bucket boundaries. | |
Categorizes incoming documents into a specific number of
groups, called buckets, based on a specified expression.
Bucket boundaries are automatically determined in an attempt
to evenly distribute the documents into the specified number
of buckets. | |
Returns statistics regarding a collection or view. | |
Returns a count of the number of documents at this stage of the aggregation pipeline. Distinct from the | |
Processes multiple aggregation pipelines within a single stage on the same set
of input documents. Enables the creation of multi-faceted
aggregations capable of characterizing data across multiple
dimensions, or facets, in a single stage. | |
Performs a recursive search on a collection. To each output
document, adds a new array field that contains the traversal
results of the recursive search for that document. | |
Groups input documents by a specified identifier expression
and applies the accumulator expression(s), if specified, to
each group. Consumes all input documents and outputs one
document per each distinct group. The output documents only
contain the identifier field and, if specified, accumulated
fields. | |
Returns statistics regarding the use of each index for the
collection. | |
Passes the first n documents unmodified to the pipeline
where n is the specified limit. For each input document,
outputs either one document (for the first n documents) or
zero documents (after the first n documents). | |
Lists all sessions that have been active long enough to
propagate to the system.sessions collection. | |
Performs a left outer join to another collection in the
same database to filter in documents from the "joined"
collection for processing. | |
Filters the document stream to allow only matching documents
to pass unmodified into the next pipeline stage.
$match uses standard MongoDB queries. For each
input document, outputs either one document (a match) or zero
documents (no match). | |
Writes the resulting documents of the aggregation pipeline to
a collection. The stage can incorporate (insert new
documents, merge documents, replace documents, keep existing
documents, fail the operation, process documents with a
custom update pipeline) the results into an output
collection. To use the New in version 4.2. | |
Writes the resulting documents of the aggregation pipeline to
a collection. To use the $out stage, it must be
the last stage in the pipeline. | |
Returns plan cache information for a
collection. | |
Reshapes each document in the stream, such as by adding new fields or removing existing fields. For each input document, outputs one document. See also | |
Reshapes each document in the stream by restricting the
content for each document based on information stored in the
documents themselves. Incorporates the functionality of
$project and $match . Can be used to
implement field level redaction. For each input document,
outputs either one or zero documents. | |
Replaces a document with the specified embedded document. The
operation replaces all existing fields in the input document,
including the
| |
Replaces a document with the specified embedded document. The
operation replaces all existing fields in the input document,
including the
| |
Randomly selects the specified number of documents from its
input. | |
Performs a full-text search of the field or fields in an Atlas collection. Note
| |
Adds new fields to documents. Similar to
| |
Groups documents into windows and applies one or more operators to the documents in each window. New in version 5.0. | |
Skips the first n documents where n is the specified skip
number and passes the remaining documents unmodified to the
pipeline. For each input document, outputs either zero
documents (for the first n documents) or one document (if
after the first n documents). | |
Reorders the document stream by a specified sort key. Only
the order changes; the documents remain unmodified. For each
input document, outputs one document. | |
Groups incoming documents based on the value of a specified
expression, then computes the count of documents in each
distinct group. | |
Performs a union of two collections; i.e. combines pipeline results from two collections into a single result set. New in version 4.4. | |
Deconstructs an array field from the input documents to
output a document for each element. Each output document
replaces the array with an element value. For each input
document, outputs n documents where n is the number of
array elements and can be zero for an empty array. |
Stages (db.aggregate
)¶
Starting in version 3.6, MongoDB also provides the
db.aggregate()
method:
db.aggregate( [ { <stage> }, ... ] )
The following stages use the db.aggregate()
method and not
the db.collection.aggregate()
method.
Stage | Description |
---|---|
Returns information on active and/or dormant operations for
the MongoDB deployment. | |
Stages Available for Updates¶
Starting in MongoDB 4.2, you can use the aggregation pipeline for updates in:
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, literals, system variables, expression objects, and expression operators. Expressions can be nested.
Field Paths¶
Aggregation expressions use field path to access fields in the
input documents. To specify a field path, prefix the field name or the
dotted field name (if the field is in
the embedded document) with a dollar sign $
. For example,
"$user"
to specify the field path for the user
field or
"$user.name"
to specify the field path to "user.name"
field.
"$<field>"
is equivalent to "$$CURRENT.<field>"
where the
CURRENT
is a system variable that defaults to the root of
the current object, unless stated otherwise in specific stages.
Aggregation Variables¶
MongoDB provides various aggregation system variables for use in expressions. To access variables,
prefix the variable name with $$
. For example:
Variable | Access via $$ | Brief Description |
---|---|---|
$$NOW | Returns the current datetime value, which is same across all
members of the deployment and remains constant throughout the
aggregation pipeline. (Available in 4.2+) | |
$$CLUSTER_TIME | Returns the current timestamp value, which is same across all
members of the deployment and remains constant throughout the
aggregation pipeline. For replica sets and sharded clusters
only. (Available in 4.2+) | |
$$ROOT | References the root document, i.e. the top-level document. | |
$$CURRENT | References the start of the field path, which by default is
ROOT but can be changed. | |
$$REMOVE | Allows for the conditional exclusion of fields. (Available in 3.6+) | |
$$DESCEND | One of the allowed results of a $redact expression. | |
$$PRUNE | One of the allowed results of a $redact expression. | |
$$KEEP | One of the allowed results of a $redact expression. |
For a more detailed description of these variables, see system variables.
Literals¶
Literals can be of any type. However, MongoDB parses string literals
that start with a dollar sign $
as a path to a field and
numeric/boolean literals in expression objects as projection flags. To avoid
parsing 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 the
literals as projection flags (e.g. 1
or true
to include the
field), valid only in the $project
stage. To avoid treating
numeric or boolean literals as projection flags, use the
$literal
expression to wrap the numeric or boolean
literals.
Operator Expressions¶
Operator expressions are similar to functions that take arguments. In general, these expressions take an array of arguments and have the following form:
{ <operator>: [ <argument1>, <argument2> ... ] }
If operator accepts a single argument, you can omit the outer array designating the argument list:
{ <operator>: <argument> }
To avoid parsing ambiguity if the argument is a literal array, you must
wrap the literal array in a $literal
expression or keep
the outer array that designates the argument list.
Arithmetic Expression Operators¶
Arithmetic expressions perform mathematic operations on numbers. Some arithmetic expressions can also support date arithmetic.
Name | Description |
---|---|
Returns the absolute value of a number. | |
Adds numbers to return the sum, or adds numbers and a date to
return a new date. If adding numbers and a date, treats the
numbers as milliseconds. Accepts any number of argument
expressions, but at most, one expression can resolve to a
date. | |
Returns the smallest integer greater than or equal to the specified number. | |
Returns the result of dividing the first number by the
second. Accepts two argument expressions. | |
Raises e to the specified exponent. | |
Returns the largest integer less than or equal to the
specified number. | |
Calculates the natural log of a number. | |
Calculates the log of a number in the specified base. | |
Calculates the log base 10 of a number. | |
Returns the remainder of the first number divided by the
second. Accepts two argument expressions. | |
Multiplies numbers to return the product. Accepts any number
of argument expressions. | |
Raises a number to the specified exponent. | |
Rounds a number to to a whole integer or to a specified
decimal place. | |
Calculates the square root. | |
Returns the result of subtracting the second value from the
first. If the two values are numbers, return the difference.
If the two values are dates, return the difference in
milliseconds. If the two values are a date and a number in
milliseconds, return the resulting date. Accepts two argument
expressions. If the two values are a date and a number,
specify the date argument first as it is not meaningful to
subtract a date from a number. | |
Truncates a number to a whole integer or to a specified
decimal place. |
Array Expression Operators¶
Name | Description |
---|---|
Returns the element at the specified array index. | |
Converts an array of key value pairs to a document. | |
Concatenates arrays to return the concatenated array. | |
Selects a subset of the array to return an array with only
the elements that match the filter condition. | |
Returns the first array element. Distinct from $first accumulator. | |
Returns a boolean indicating whether a specified value is in
an array. | |
Searches an array for an occurrence of a specified value and
returns the array index of the first occurrence. If the
substring is not found, returns -1 . | |
Determines if the operand is an array. Returns a boolean. | |
Returns the last array element. Distinct from $last accumulator. | |
Applies a subexpression to each element of an array and
returns the array of resulting values in order. Accepts named
parameters. | |
Converts a document to an array of documents representing
key-value pairs. | |
Outputs an array containing a sequence of integers according
to user-defined inputs. | |
Applies an expression to each element in an array and
combines them into a single value. | |
Returns an array with the elements in reverse order. | |
Returns the number of elements in the array. Accepts a single
expression as argument. | |
Returns a subset of an array. | |
Merge two arrays together. |
Boolean Expression Operators¶
Boolean expressions evaluate their argument expressions as booleans and return a boolean as the result.
In addition to the false
boolean value, Boolean expression evaluates
as false
the following: null
, 0
, and undefined
values. The Boolean expression evaluates all other values as true
,
including non-zero numeric values and arrays.
Name | Description |
---|---|
Returns true only when all its expressions evaluate to
true . Accepts any number of argument expressions. | |
Returns the boolean value that is the opposite of its
argument expression. Accepts a single argument expression. | |
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 $cmp
which returns a number.
The comparison expressions take two argument expressions and compare both value and type, using the specified BSON comparison order for values of different types.
Name | Description |
---|---|
Returns 0 if the two values are equivalent, 1 if the
first value is greater than the second, and -1 if the
first value is less than the second. | |
Returns true if the values are equivalent. | |
Returns true if the first value is greater than the
second. | |
Returns true if the first value is greater than or equal
to the second. | |
Returns true if the first value is less than the second. | |
Returns true if the first value is less than or equal to
the second. | |
Returns true if the values are not equivalent. |
Conditional Expression Operators¶
Name | Description |
---|---|
A ternary operator that evaluates one expression, and
depending on the result, returns the value of one of the
other two expressions. Accepts either three expressions in an
ordered list or three named parameters. | |
Returns either the non-null result of the first expression or
the result of the second expression if the first expression
results in a null result. Null result encompasses instances
of undefined values or missing fields. Accepts two
expressions as arguments. The result of the second expression
can be null. | |
Evaluates a series of case expressions. When it finds an
expression which evaluates to true , $switch executes
a specified expression and breaks out of the control flow. |
Custom Aggregation Expression Operators¶
Name | Description |
---|---|
Defines a custom accumulator function. New in version 4.4. | |
Defines a custom function. New in version 4.4. |
Data Size Expression Operators¶
The following operators return the size of a data element:
Name | Description |
---|---|
Returns the size of a given string or binary data value's
content in bytes. | |
Returns the size in bytes of a given document (i.e. bsontype
Object ) when encoded as BSON. |
Date Expression Operators¶
The following operators returns date objects or components of a date object:
Name | Description |
---|---|
Adds a number of time units to a date object. | |
Returns the difference between two dates. | |
Constructs a BSON Date object given the date's constituent
parts. | |
Converts a date/time string to a date object. | |
Subtracts a number of time units from a date object. | |
Returns a document containing the constituent parts of a date. | |
Returns the date as a formatted string. | |
Returns the day of the month for a date as a number between 1
and 31. | |
Returns the day of the week for a date as a number between 1
(Sunday) and 7 (Saturday). | |
Returns the day of the year for a date as a number between 1
and 366 (leap year). | |
Returns the hour for a date as a number between 0 and 23. | |
Returns the weekday number in ISO 8601 format, ranging from
1 (for Monday) to 7 (for Sunday). | |
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 first
Thursday. | |
Returns the year number in ISO 8601 format. The year starts
with the Monday of week 1 (ISO 8601) and ends with the Sunday
of the last week (ISO 8601). | |
Returns the milliseconds of a date as a number between 0 and
999. | |
Returns the minute for a date as a number between 0 and 59. | |
Returns the month for a date as a number between 1 (January)
and 12 (December). | |
Returns the seconds for a date as a number between 0 and 60
(leap seconds). | |
Converts value to a Date. New in version 4.0. | |
Returns the week number for a date as a number between 0 (the
partial week that precedes the first Sunday of the year) and
53 (leap year). | |
Returns the year for a date as a number (e.g. 2014). |
The following arithmetic operators can take date operands:
Name | Description |
---|---|
Adds numbers and a date to return a new date. If adding
numbers and a date, treats the numbers as milliseconds.
Accepts any number of argument expressions, but at most, one
expression can resolve to a date. | |
Returns the result of subtracting the second value from the
first. If the two values are dates, return the difference in
milliseconds. If the two values are a date and a number in
milliseconds, return the resulting date. Accepts two argument
expressions. If the two values are a date and a number,
specify the date argument first as it is not meaningful to
subtract a date from a number. |
Literal Expression Operator¶
Miscellaneous Operators¶
Name | Description |
---|---|
Returns the value of a specified field from a document.
You can use New in version 5.0. | |
Returns a random float between 0 and 1 | |
Randomly select documents at a given rate. Although the exact
number of documents selected varies on each run, the quantity
chosen approximates the sample rate expressed as a percentage
of the total number of documents. |
Object Expression Operators¶
Name | Description |
---|---|
Combines multiple documents into a single document. New in version 3.6. | |
Converts a document to an array of documents representing key-value pairs. New in version 3.6. | |
Adds, updates, or removes a specified field in a document. You
can use New in version 5.0. |
Set Expression Operators¶
Set expressions performs set operation on arrays, treating arrays as sets. Set expressions ignores the duplicate entries in each input array and the order of the elements.
If the set operation returns a set, the operation filters out duplicates in the result to output an array that contains only unique entries. The order of the elements in the output array is unspecified.
If a set contains a nested array element, the set expression does not descend into the nested array but evaluates the array at top-level.
Name | Description |
---|---|
Returns true if no element of a set evaluates to
false , otherwise, returns false . Accepts a single
argument expression. | |
Returns true if any elements of a set evaluate to
true ; otherwise, returns false . Accepts a single
argument expression. | |
Returns a set with elements that appear in the first set
but not in the second set; i.e. performs a relative
complement
of the second set relative to the first. Accepts exactly
two argument expressions. | |
Returns true if the input sets have the same distinct
elements. Accepts two or more argument expressions. | |
Returns a set with elements that appear in all of the
input sets. Accepts any number of argument expressions. | |
Returns true if all elements of the first set appear
in the second set, including when the first set equals the
second set; i.e. not a strict subset. Accepts exactly
two argument expressions. | |
Returns a set with elements that appear in any of the
input 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 of
the characters used.
Name | Description |
---|---|
Concatenates any number of strings. | |
Converts a date/time string to a date object. | |
Returns the date as a formatted string. | |
Searches a string for an occurrence of a substring and returns
the UTF-8 byte index of the first occurrence. If the substring
is not found, returns -1 . | |
Searches a string for an occurrence of a substring and returns
the UTF-8 code point index of the first occurrence. If the
substring is not found, returns -1 | |
Removes whitespace or the specified characters from the beginning of a string. New in version 4.0. | |
Applies a regular expression (regex) to a string and returns information on the first matched substring. New in version 4.2. | |
Applies a regular expression (regex) to a string and returns information on the all matched substrings. New in version 4.2. | |
Applies a regular expression (regex) to a string and returns a boolean that indicates if a match is found or not. New in version 4.2. | |
Replaces the first instance of a matched string in a given input. New in version 4.4. | |
Replaces all instances of a matched string in a given input. New in version 4.4. | |
Removes whitespace or the specified characters from the end of a string. New in version 4.0. | |
Splits a string into substrings based on a delimiter. Returns
an array of substrings. If the delimiter is not found within
the string, returns an array containing the original string. | |
Returns the number of UTF-8 encoded bytes in a string. | |
Returns the number of UTF-8 code points in a string. | |
Performs case-insensitive string comparison and returns:
0 if two strings are equivalent, 1 if the first
string is greater than the second, and -1 if the first
string is less than the second. | |
Deprecated. Use $substrBytes or
$substrCP . | |
Returns the substring of a string. Starts with the character
at the specified UTF-8 byte index (zero-based) in the string
and continues for the specified number of bytes. | |
Returns the substring of a string. Starts with the character
at the specified UTF-8 code point (CP) index
(zero-based) in the string and continues for the number of
code points specified. | |
Converts a string to lowercase. Accepts a single argument
expression. | |
Converts value to a string. New in version 4.0. | |
Removes whitespace or the specified characters from the beginning and end of a string. New in version 4.0. | |
Converts a string to uppercase. Accepts a single argument
expression. |
Text Expression Operator¶
Name | Description |
---|---|
Access available per-document metadata related to the
aggregation operation. |
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
to
convert between degree and radian measurements.
Name | Description |
---|---|
Returns the sine of a value that is measured in radians. | |
Returns the cosine of a value that is measured in radians. | |
Returns the tangent of a value that is measured in radians. | |
Returns the inverse sin (arc sine) of a value in radians. | |
Returns the inverse cosine (arc cosine) of a value in radians. | |
Returns the inverse tangent (arc tangent) of a value in
radians. | |
Returns the inverse tangent (arc tangent) of y / x in
radians, where y and x are the first and second
values passed to the expression respectively. | |
Returns the inverse hyperbolic sine (hyperbolic arc sine) of a
value in radians. | |
Returns the inverse hyperbolic cosine (hyperbolic arc cosine)
of a value in radians. | |
Returns the inverse hyperbolic tangent (hyperbolic arc
tangent) of a value in radians. | |
Returns the hyperbolic sine of a value that is measured in
radians. | |
Returns the hyperbolic cosine of a value that is measured in
radians. | |
Returns the hyperbolic tangent of a value that is measured in
radians. | |
Converts a value from degrees to radians. | |
Converts a value from radians to degrees. |
Type Expression Operators¶
Name | Description |
---|---|
Converts a value to a specified type. New in version 4.0. | |
Converts value to a boolean. New in version 4.0. | |
Converts value to a Date. New in version 4.0. | |
Converts value to a Decimal128. New in version 4.0. | |
Converts value to a double. New in version 4.0. | |
Converts value to an integer. New in version 4.0. | |
Converts value to a long. New in version 4.0. | |
Converts value to an ObjectId. New in version 4.0. | |
Converts value to a string. New in version 4.0. | |
Return the BSON data type of the field. |
Accumulators ($group, $bucket, $bucketAuto, $setWindowFields
)¶
Aggregation accumulator operators:
- Maintain their state as documents progress through the aggregation pipeline.
- Return totals, maxima, minima, and other values.
Can be used in these aggregation pipeline stages:
$group
$bucket
$bucketAuto
$setWindowFields
starting in MongoDB 5.0 (except when you are using the$accumulator
or$mergeObjects
operators, which cannot be used with$setWindowFields
)
Changed in version 5.0.
Name | Description |
---|---|
Returns the result of a user-defined accumulator function. | |
Returns an array of unique expression values for each group. Order of the array elements is undefined. Changed in version 5.0: Available in | |
Returns an average of numerical values. Ignores non-numeric values. Changed in version 5.0: Available in | |
Returns the number of documents in a group. Distinct from the New in version 5.0: Available in | |
Returns a value from the first document for each group. Order is only defined if the documents are sorted. Distinct from the Changed in version 5.0: Available in | |
Returns a value from the last document for each group. Order is only defined if the documents are sorted. Distinct from the Changed in version 5.0: Available in | |
Returns the highest expression value for each group. Changed in version 5.0: Available in | |
Returns a document created by combining the input documents
for each group. | |
Returns the lowest expression value for each group. Changed in version 5.0: Available in | |
Returns an array of expression values for documents in each group. Changed in version 5.0: Available in | |
Returns the population standard deviation of the input values. Changed in version 5.0: Available in | |
Returns the sample standard deviation of the input values. Changed in version 5.0: Available in | |
Returns a sum of numerical values. Ignores non-numeric values. Changed in version 5.0: Available in |
Accumulators (in Other Stages)¶
Some operators that are available as accumulators for the
$group
stage are also available for use in other stages but
not as accumulators. When used in these other stages, these operators
do not maintain their state and can take as input either a single
argument or multiple arguments. For details, refer to the specific
operator page.
Changed in version 5.0.
The following accumulator operators are also available in the
$project
, $addFields
, $set
, and,
starting in MongoDB 5.0, the $setWindowFields
stages.
Name | Description |
---|---|
Returns an average of the specified expression or list of
expressions for each document. Ignores non-numeric values. | |
Returns the maximum of the specified expression or list of
expressions for each document | |
Returns the minimum of the specified expression or list of
expressions for each document | |
Returns the population standard deviation of the input values. | |
Returns the sample standard deviation of the input values. | |
Returns a sum of numerical values. Ignores non-numeric values. |
Variable Expression Operators¶
Name | Description |
---|---|
Defines variables for use within the scope of a subexpression and returns the result of the subexpression. Accepts named parameters. Accepts any number of argument expressions. |
Window Operators¶
New in version 5.0.
Window operators return values from a defined span of documents from
a collection, known as a window. A window is defined in the
$setWindowFields
stage, available starting in MongoDB
5.0.
The following window operators are available in the
$setWindowFields
stage.
Name | Description |
---|---|
Returns an array of all unique values that results from applying an expression to each document. Changed in version 5.0: Available in | |
Returns the average for the specified expression. Ignores non-numeric values. Changed in version 5.0: Available in | |
Returns the number of documents in the group or window. Distinct from the New in version 5.0. | |
Returns the population covariance of two numeric expressions. New in version 5.0. | |
Returns the sample covariance of two numeric expressions. New in version 5.0. | |
Returns the document position (known as the rank) relative to
other documents in the New in version 5.0. | |
Returns the mathematical derivative of the numeric expression. New in version 5.0. | |
Returns the position of a document (known as the document
number) in the New in version 5.0. | |
Returns the exponential moving average for the numeric expression. New in version 5.0. | |
Returns the value that results from applying an expression to the first document in a group or window. Changed in version 5.0: Available in | |
Returns the mathematical integral of the numeric expression. New in version 5.0. | |
Returns the value that results from applying an expression to the last document in a group or window. Changed in version 5.0: Available in | |
Returns the maximum value that results from applying an expression to each document. Changed in version 5.0: Available in | |
Returns the minimum value that results from applying an expression to each document. Changed in version 5.0: Available in | |
Returns an array of values that result from applying an expression to each document. Changed in version 5.0: Available in | |
Returns the document position (known as the rank) relative to
other documents in the New in version 5.0. | |
Returns the value from an expression applied to a document in a
specified position relative to the current document in the
New in version 5.0. | |
Returns the population standard deviation that results from applying a numeric expression to each document. Changed in version 5.0: Available in | |
Returns the sample standard deviation that results from applying a numeric expression to each document. Changed in version 5.0: Available in | |
Returns the sum that results from applying a numeric expression to each document. Changed in version 5.0: Available in |