Navigation

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

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:

{ <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
$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

$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 occurence of a specified value and returns the array index of the first occurence. 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.

$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).
$toDate

Converts value to a Date.

New in version 4.0.

$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 Expresion 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 occurence of a substring and returns the UTF-8 byte index of the first occurence. If the substring is not found, returns -1.
$indexOfCP Searches a string for an occurence of a substring and returns the UTF-8 code point index of the first occurence. If the substring is not found, returns -1
$ltrim

Removes whitespace or the specified characters from the beginning of a string.

New in version 4.0.

$rtrim

Removes whitespace or the specified characters from the end of a string.

New in version 4.0.

$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.
$toString

Converts value to a string.

New in version 4.0.

$trim

Removes whitespace or the specified characters from the beginning and end of a string.

New in version 4.0.

$toUpper Converts a string to uppercase. Accepts a single argument expression.

Text Expression Operator

Name Description
$meta Access text search metadata.

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 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
$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.
$min Returns the lowest expression value for each group.
$push Returns an array of expression values for each group.
$addToSet Returns an array of unique expression values for each group. Order of the array elements is undefined.
$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.

Variable Expression Operators

Name Description
$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.

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 $group stage only.

$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.

Changed in version 3.2: Available in both $group and $project stages.

$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.
$convert Converts a value to a specified type.
$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 $group stage only.

$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 occurence of a specified value and returns the array index of the first occurence. If the substring is not found, returns -1.
$indexOfBytes Searches a string for an occurence of a substring and returns the UTF-8 byte index of the first occurence. If the substring is not found, returns -1.
$indexOfCP Searches a string for an occurence of a substring and returns the UTF-8 code point index of the first occurence. 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 $group stage only.

$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.
$ltrim Removes whitespace or the specified characters from the beginning of a string.
$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.

Changed in version 3.2: Available in both $group and $project stages.

$mergeObjects Combines multiple documents into a single document.
$meta Access text search metadata.
$min

Returns the lowest expression value for each group.

Changed in version 3.2: Available in both $group and $project stages.

$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 $group stage only.

$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.
$rtrim Removes whitespace or the specified characters from the end of a string.
$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.

Changed in version 3.2: Available in both $group and $project stages.

$stdDevSamp

Returns the sample standard deviation of the input values.

Changed in version 3.2: Available in both $group and $project stages.

$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.

Changed in version 3.2: Available in both $group and $project stages.

$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.
$toBool Converts value to a boolean.
$toDate Converts value to a Date.
$toDecimal Converts value to a Decimal128.
$toDouble Converts value to a double.
$toInt Converts value to an integer.
$toLong Converts value to a long.
$toObjectId Converts value to an ObjectId.
$toString Converts value to a string.
$toLower Converts a string to lowercase. Accepts a single argument expression.
$toUpper Converts a string to uppercase. Accepts a single argument expression.
$trim Removes whitespace or the specified characters from the beginning and end of a string.
$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.