Aggregation Pipeline Operators¶
Note
For details on specific operator, including syntax and examples, click on the specific operator to go to its reference page.
Expression Operators¶
In this section
- Arithmetic Expression Operators
- Array Expression Operators
- Boolean Expression Operators
- Comparison Expression Operators
- Conditional Expression Operators
- Date Expression Operators
- Literal Expression Operator
- Object Expression Operators
- Set Expression Operators
- String Expression Operators
- Text Expression Operator
- Type Expression Operators
- Accumulators (
$group
) - Accumulators (
$project
) - Variable Expression Operators
These expression operators are available to construct expressions for use in the aggregation pipeline stages.
Operator expressions are similar to functions that take arguments. In general, these expressions take an array of arguments and have the following form:
If operator accepts a single argument, you can omit the outer array designating the argument list:
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 |
---|---|
$abs |
Returns the absolute value of a number. |
$add |
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. |
$ceil |
Returns the smallest integer greater than or equal to the specified number. |
$divide |
Returns the result of dividing the first number by the second. Accepts two argument expressions. |
$exp |
Raises e to the specified exponent. |
$floor |
Returns the largest integer less than or equal to the specified 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 the second. Accepts two argument expressions. |
$multiply |
Multiplies numbers to return the product. Accepts any number of argument expressions. |
$pow |
Raises a number to the specified exponent. |
$sqrt |
Calculates the square root. |
$subtract |
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. |
$trunc |
Truncates a number to its integer. |
Array Expression Operators¶
Name | Description |
---|---|
$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 only the elements that match the filter condition. |
$in |
Returns a boolean indicating whether a specified value is in an array. |
$indexOfArray |
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 . |
$isArray |
Determines if the operand is an array. Returns a boolean. |
$map |
Applies a subexpression to each element of an array and returns the array of resulting values in order. Accepts named parameters. |
$objectToArray |
Converts a document to an array of documents representing key-value pairs. |
$range |
Outputs an array containing a sequence of integers according to user-defined inputs. |
$reduce |
Applies an expression to each element in an array and combines 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 single expression 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 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 |
---|---|
$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 its argument 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 $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 |
---|---|
$cmp |
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. |
$eq |
Returns true if the values are equivalent. |
$gt |
Returns true if the first value is greater than the
second. |
$gte |
Returns true if the first value is greater than or equal
to 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 to
the second. |
$ne |
Returns true if the values are not equivalent. |
Conditional Expression Operators¶
Name | Description |
---|---|
$cond |
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. |
$ifNull |
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. |
$switch |
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. |
Date Expression Operators¶
The following operators returns date objects or components of a date object:
Name | Description |
---|---|
$dateFromParts |
Constructs a BSON Date object given the date’s constituent parts. |
$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 1 and 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 1 and 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 first
Thursday. |
$isoWeekYear |
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). |
$millisecond |
Returns the milliseconds of a date as a number between 0 and 999. |
$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). |
$week |
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). |
$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 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. |
$subtract |
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¶
Name | Description |
---|---|
$literal |
Return a value without parsing. Use for values that the
aggregation pipeline may interpret as an expression. For
example, use a $literal expression to a string
that 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 representing key-value pairs. New in version 3.6. |
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 |
---|---|
$allElementsTrue |
Returns true if no element of a set evaluates to
false , otherwise, returns false . Accepts a single
argument expression. |
$anyElementTrue |
Returns true if any elements of a set evaluate to
true ; otherwise, returns false . Accepts a single
argument expression. |
$setDifference |
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. |
$setEquals |
Returns true if the input sets have the same distinct
elements. Accepts two or more argument expressions. |
$setIntersection |
Returns a set with elements that appear in all of the input sets. Accepts any number of argument expressions. |
$setIsSubset |
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. |
$setUnion |
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 |
---|---|
$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 occurrence of a substring and returns
the UTF-8 byte index of the first occurrence. If the substring
is not found, returns -1 . |
$indexOfCP |
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 |
$split |
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. |
$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 first
string is greater than the second, and -1 if the first
string is less than the second. |
$substr |
Deprecated. Use $substrBytes or
$substrCP . |
$substrBytes |
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. |
$substrCP |
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. |
$toLower |
Converts a string to lowercase. Accepts a single argument expression. |
$toUpper |
Converts a string to uppercase. Accepts a single argument expression. |
Accumulators ($group
)¶
Available for use in the $group
stage, accumulators are
operators 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, these
operators take as input a single expression, evaluating the expression
once for each input document, and maintain their stage for the group of
documents that share the same group key.
Name | Description |
---|---|
$addToSet |
Returns an array of unique expression values for each group. 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. Order is only defined if the documents are in a defined order. |
$last |
Returns a value from the last document for each group. Order is 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 documents for 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 ($project
)¶
Some operators that are available as accumulators for the
$group
stage are also available for use in the
$project
stage but not as accumulators. When used in the
$project
stage, these operators do not maintain their state
and can take as input either a single argument or multiple arguments.
Changed in version 3.2.
The following accumulator operators are also available in the
$project
and $addFields
stages.
Name | Description |
---|---|
$avg |
Returns an average of the specified expression or list of expressions for each document. Ignores non-numeric values. |
$max |
Returns the maximum of the specified expression or list of expressions for each document |
$min |
Returns the minimum of the specified expression or list of expressions 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. |
Alphabetical Listing of Expression Operators¶
Name | Description |
---|---|
$abs |
Returns the absolute value of a number. |
$add |
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. |
$addToSet |
Returns an array of unique expression values for each group. Order of the array elements is undefined. Available in |
$allElementsTrue |
Returns true if no element of a set evaluates to false ,
otherwise, returns false . Accepts a single argument expression. |
$and |
Returns true only when all its expressions evaluate to
true . Accepts any number of argument expressions. |
$anyElementTrue |
Returns true if any elements of a set evaluate to true ;
otherwise, returns false . Accepts a single argument expression. |
$arrayElemAt |
Returns the element at the specified array index. |
$arrayToObject |
Converts an array of key value pairs to a document. |
$avg |
Returns an average of numerical values. Ignores non-numeric values. |
$ceil |
Returns the smallest integer greater than or equal to the specified number. |
$cmp |
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. |
$concat |
Concatenates any number of strings. |
$concatArrays |
Concatenates arrays to return the concatenated array. |
$cond |
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. |
$dateFromParts |
Constructs a BSON Date object given the date’s constituent parts. |
$dateToParts |
Returns a document containing the constituent parts of a date. |
$dateFromString |
Returns a date/time as a date object. |
$dateToString |
Returns the date as a formatted string. |
$dayOfMonth |
Returns the day of the month for a date as a number between 1 and 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 1 and 366 (leap year). |
$divide |
Returns the result of dividing the first number by the second. Accepts two argument expressions. |
$eq |
Returns true if the values are
equivalent. |
$exp |
Raises e to the specified exponent. |
$filter |
Selects a subset of the array to return an array with only the elements that match the filter condition. |
$first |
Returns a value from the first document for each group. Order is only defined if the documents are in a defined order. Available in |
$floor |
Returns the largest integer less than or equal to the specified number. |
$gt |
Returns true if the first value is
greater than the second. |
$gte |
Returns true if the first value is
greater than or equal to the second. |
$hour |
Returns the hour for a date as a number between 0 and 23. |
$ifNull |
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. |
$in |
Returns a boolean indicating whether a specified value is in an array. |
$indexOfArray |
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 . |
$indexOfBytes |
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 . |
$indexOfCP |
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 . |
$isArray |
Determines if the operand is an array. Returns a boolean. |
$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 first Thursday. |
$isoWeekYear |
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). |
$last |
Returns a value from the last document for each group. Order is only defined if the documents are in a defined order. Available in |
$let |
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. |
$literal |
Return a value without parsing. Use for values that the aggregation
pipeline may interpret as an expression. For example, use a
$literal expression to a string that starts with a
$ to avoid parsing as a field path. |
$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. |
$lt |
Returns true if the first value is less
than the second. |
$lte |
Returns true if the first value is less
than or equal to the second. |
$map |
Applies a subexpression to each element of an array and returns the array of resulting values in order. Accepts named parameters. |
$max |
Returns the highest expression value for each group. |
$mergeObjects |
Combines multiple documents into a single document. |
$meta |
Access text search metadata. |
$min |
Returns the lowest expression value for each group. |
$millisecond |
Returns the milliseconds of a date as a number between 0 and 999. |
$minute |
Returns the minute for a date as a number between 0 and 59. |
$mod |
Returns the remainder of the first number divided by the second. Accepts two argument expressions. |
$month |
Returns the month for a date as a number between 1 (January) and 12 (December). |
$multiply |
Multiplies numbers to return the product. Accepts any number of argument expressions. |
$ne |
Returns true if the values are not
equivalent. |
$not |
Returns the boolean value that is the opposite of its argument expression. Accepts a single argument expression. |
$objectToArray |
Converts a document to an array of documents representing key-value pairs. |
$or |
Returns true when any of its expressions evaluates to true .
Accepts any number of argument expressions. |
$pow |
Raises a number to the specified exponent. |
$push |
Returns an array of expression values for each group. Available in |
$range |
Outputs an array containing a sequence of integers according to user-defined inputs. |
$reduce |
Applies an expression to each element in an array and combines them into a single value. |
$reverseArray |
Returns an array with the elements in reverse order. |
$second |
Returns the seconds for a date as a number between 0 and 60 (leap seconds). |
$setDifference |
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. |
$setEquals |
Returns true if the input sets have the same distinct elements.
Accepts two or more argument expressions. |
$setIntersection |
Returns a set with elements that appear in all of the input sets. Accepts any number of argument expressions. |
$setIsSubset |
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. |
$setUnion |
Returns a set with elements that appear in any of the input sets. |
$size |
Returns the number of elements in the array. Accepts a single expression as argument. |
$slice |
Returns a subset of an array. |
$split |
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. |
$sqrt |
Calculates the square root. |
$stdDevPop |
Returns the population standard deviation of the input values. |
$stdDevSamp |
Returns the sample standard deviation of the input values. |
$strcasecmp |
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. |
$strLenBytes |
Returns the number of UTF-8 encoded bytes in a string. |
$strLenCP |
Returns the number of UTF-8 code points in a string. |
$substr |
Deprecated. Use $substrBytes or $substrCP . |
$substrBytes |
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. |
$substrCP |
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. |
$subtract |
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. |
$sum |
Returns a sum of numerical values. Ignores non-numeric values. |
$switch |
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. |
$toLower |
Converts a string to lowercase. Accepts a single argument expression. |
$toUpper |
Converts a string to uppercase. Accepts a single argument expression. |
$trunc |
Truncates a number to its integer. |
$type |
Return the BSON data type of the field. |
$week |
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). |
$year |
Returns the year for a date as a number (e.g. 2014). |
$zip |
Merge two arrays together. |
For the pipeline stages, see Aggregation Pipeline Stages.