aggregation.txt

.. ensure that the changes to this text are reflected in /reference/operator/aggregation.txt
.. ensure that the changes to this text are reflected in /meta/aggregation-quick-reference.txt
   with slight difference. Quick Reference contains information on different expressions,
   such as field paths, expression objects, etc.

==============================
Aggregation Pipeline Operators
==============================

.. default-domain:: mongodb


.. include:: /includes/extracts/operators-toc-explanation.rst

.. COMMENT - this page omits the sections on Expressions in general
   as well as Literals, Field Paths, and Expression Objects which
   are in the meta/aggregation-quick-reference.txt.
   This page only focuses on operators.

.. _aggregation-expression-operators:

Expression Operators
--------------------

.. contents:: In this section
   :local:
   :backlinks: none
   :depth: 1
   :class: twocols

These expression operators are available to construct :ref:`expressions
<aggregation-expressions>` for use in the :doc:`aggregation pipeline
stages </reference/operator/aggregation-pipeline>`.

.. include:: /includes/intro-aggregation-operator-expressions.rst


Arithmetic Expression Operators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. include:: /includes/extracts/agg-operators-arithmetic.rst


Array Expression Operators
~~~~~~~~~~~~~~~~~~~~~~~~~~

.. include:: /includes/extracts/agg-operators-array.rst

Boolean Expression Operators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. include:: /includes/extracts/agg-operators-boolean.rst

Comparison Expression Operators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. include:: /includes/extracts/agg-operators-comparison.rst


Conditional Expression Operators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. include:: /includes/extracts/agg-operators-conditional.rst

Date Expression Operators
~~~~~~~~~~~~~~~~~~~~~~~~~

.. include:: /includes/extracts/agg-operators-date.rst

Literal Expression Operator
~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. include:: /includes/extracts/agg-operators-literal.rst

Object Expression Operators
~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. include:: /includes/extracts/agg-operators-objects.rst

Set Expression Operators
~~~~~~~~~~~~~~~~~~~~~~~~

.. include:: /includes/extracts/agg-operators-set.rst

String Expression Operators
~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. include:: /includes/extracts/agg-operators-string.rst

Text Expression Operator
~~~~~~~~~~~~~~~~~~~~~~~~

.. include:: /includes/extracts/agg-operators-text.rst

Type Expression Operators
~~~~~~~~~~~~~~~~~~~~~~~~~

.. include:: /includes/extracts/agg-operators-type.rst

Accumulators (``$group``)
~~~~~~~~~~~~~~~~~~~~~~~~~~

.. include:: /includes/intro-aggregation-accumulator.rst

.. include:: /includes/extracts/agg-operators-accumulators-group.rst

Accumulators (``$project``)
~~~~~~~~~~~~~~~~~~~~~~~~~~~

Some operators that are available as accumulators for the
:pipeline:`$group` stage are also available for use in the
:pipeline:`$project` stage but not as accumulators. When used in the
:pipeline:`$project` stage, these operators do not maintain their state
and can take as input either a single argument or multiple arguments.

.. include:: /includes/extracts/agg-operators-accumulators-project.rst

Variable Expression Operators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. include:: /includes/extracts/agg-operators-variable-project.rst

Alphabetical Listing of Expression Operators
--------------------------------------------

.. list-table::
   :widths: 30,70
   :header-rows: 1

   * - Name

     - Description

   * - :expression:`$abs`

     - Returns the absolute value of a number.

   * - :expression:`$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.
   

   * - :group:`$addToSet`

     - Returns an array of *unique* expression values for each group. Order of the
       array elements is undefined.
   
       Available in :pipeline:`$group` stage only.
   

   * - :expression:`$allElementsTrue`

     - Returns ``true`` if *no* element of a set evaluates to ``false``,
       otherwise, returns ``false``. Accepts a single argument expression.
   

   * - :expression:`$and`

     - Returns ``true`` only when *all* its expressions evaluate to
       ``true``. Accepts any number of argument expressions.
   

   * - :expression:`$anyElementTrue`

     - Returns ``true`` if *any* elements of a set evaluate to ``true``;
       otherwise, returns ``false``. Accepts a single argument expression.
   

   * - :expression:`$arrayElemAt`

     - Returns the element at the specified array index.
   

   * - :expression:`$arrayToObject`

     - Converts an array of key value pairs to a document.
   

   * - :group:`$avg`

     - Returns an average of numerical values. Ignores non-numeric values.
   
       .. versionchanged:: 3.2
          Available in both :pipeline:`$group` and :pipeline:`$project`
          stages.
   

   * - :expression:`$ceil`

     - Returns the smallest integer greater than or equal to the specified number.
   

   * - :expression:`$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.
   

   * - :expression:`$concat`

     - Concatenates any number of strings.
   

   * - :expression:`$concatArrays`

     - Concatenates arrays to return the concatenated array.
   

   * - :expression:`$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.
   

   * - :expression:`$convert`

     - Converts a value to a specified type.
   

   * - :expression:`$dateFromParts`

     - Constructs a BSON Date object given the date's constituent
       parts.
   

   * - :expression:`$dateToParts`

     - Returns a document containing the constituent parts of a date.
   

   * - :expression:`$dateFromString`

     - Returns a date/time as a date object.
   

   * - :expression:`$dateToString`

     - Returns the date as a formatted string.
   

   * - :expression:`$dayOfMonth`

     - Returns the day of the month for a date as a number between 1 and 31.
   

   * - :expression:`$dayOfWeek`

     - Returns the day of the week for a date as a number between 1 (Sunday)
       and 7 (Saturday).
   

   * - :expression:`$dayOfYear`

     - Returns the day of the year for a date as a number between 1 and 366
       (leap year).
   

   * - :expression:`$divide`

     - Returns the result of dividing the first number by the second. Accepts
       two argument expressions.
   

   * - :expression:`$eq`

     - Returns ``true`` if the values are
       equivalent.
   

   * - :expression:`$exp`

     - Raises *e* to the specified exponent.

   * - :expression:`$filter`

     - Selects a subset of the array to return an array with only the elements
       that match the filter condition.
   

   * - :group:`$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 :pipeline:`$group` stage only.
   

   * - :expression:`$floor`

     - Returns the largest integer less than or equal to the specified number.
   

   * - :expression:`$gt`

     - Returns ``true`` if the first value is
       greater than the second.
   

   * - :expression:`$gte`

     - Returns ``true`` if the first value is
       greater than or equal to the second.
   

   * - :expression:`$hour`

     - Returns the hour for a date as a number between 0 and 23.
   

   * - :expression:`$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.
   

   * - :expression:`$in`

     - Returns a boolean indicating whether a specified value is in an array.
   

   * - :expression:`$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``.
   

   * - :expression:`$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``.
   

   * - :expression:`$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``.
   

   * - :expression:`$isArray`

     - Determines if the operand is an array. Returns a boolean.
   

   * - :expression:`$isoDayOfWeek`

     - Returns the weekday number in ISO 8601 format, ranging from
       ``1`` (for Monday) to ``7`` (for Sunday).
   

   * - :expression:`$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.
   

   * - :expression:`$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).
   

   * - :group:`$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 :pipeline:`$group` stage only.
   

   * - :expression:`$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.
   

   * - :expression:`$literal`

     - Return a value without parsing. Use for values that the aggregation
       pipeline may interpret as an expression. For example, use a
       :expression:`$literal` expression to a string that starts with a
       ``$`` to avoid parsing as a field path.
   

   * - :expression:`$ln`

     - Calculates the natural log of a number.

   * - :expression:`$log`

     - Calculates the log of a number in the specified base.

   * - :expression:`$log10`

     - Calculates the log base 10 of a number.

   * - :expression:`$lt`

     - Returns ``true`` if the first value is less
       than the second.
   

   * - :expression:`$lte`

     - Returns ``true`` if the first value is less
       than or equal to the second.
   

   * - :expression:`$ltrim`

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

   * - :expression:`$map`

     - Applies a subexpression to each element of an array and returns the
       array of resulting values in order. Accepts named parameters.
   

   * - :group:`$max`

     - Returns the highest expression value for each group.
   
       .. versionchanged:: 3.2
          Available in both :pipeline:`$group` and :pipeline:`$project`
          stages.
   

   * - :expression:`$mergeObjects`

     - Combines multiple documents into a single document.
   

   * - :expression:`$meta`

     - Access text search metadata.
   

   * - :group:`$min`

     - Returns the lowest expression value for each group.
   
       .. versionchanged:: 3.2
          Available in both :pipeline:`$group` and :pipeline:`$project`
          stages.
   

   * - :expression:`$millisecond`

     - Returns the milliseconds of a date as a number between 0
       and 999.
   

   * - :expression:`$minute`

     - Returns the minute for a date as a number between 0 and 59.
   

   * - :expression:`$mod`

     - Returns the remainder of the first number divided by the second.
       Accepts two argument expressions.
   

   * - :expression:`$month`

     - Returns the month for a date as a number between 1
       (January) and 12 (December).
   

   * - :expression:`$multiply`

     - Multiplies numbers to return the product. Accepts any number of
       argument expressions.
   

   * - :expression:`$ne`

     - Returns ``true`` if the values are *not*
       equivalent.
   

   * - :expression:`$not`

     - Returns the boolean value that is the opposite of its argument
       expression. Accepts a single argument expression.
   

   * - :expression:`$objectToArray`

     - Converts a document to an array of documents representing key-value pairs.
   

   * - :expression:`$or`

     - Returns ``true`` when *any* of its expressions evaluates to ``true``.
       Accepts any number of argument expressions.
   

   * - :expression:`$pow`

     - Raises a number to the specified exponent.

   * - :group:`$push`

     - Returns an array of expression values for each group.
   
       Available in :pipeline:`$group` stage only.
   

   * - :expression:`$range`

     - Outputs an array containing a sequence of integers according to
       user-defined inputs.
   

   * - :expression:`$reduce`

     - Applies an expression to each element in an array and combines them
       into a single value.
   

   * - :expression:`$reverseArray`

     - Returns an array with the elements in reverse order.
   

   * - :expression:`$rtrim`

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

   * - :expression:`$second`

     - Returns the seconds for a date as a number between 0 and 60
       (leap seconds).
   

   * - :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 <http://en.wikipedia.org/wiki/Complement_(set_theory)>`_
       of the second set relative to the first. Accepts exactly two
       argument expressions.
   

   * - :expression:`$setEquals`

     - Returns ``true`` if the input sets have the same distinct elements.
       Accepts two or more argument expressions.
   

   * - :expression:`$setIntersection`

     - Returns a set with elements that appear in *all* of the input sets.
       Accepts any number of argument expressions.
   

   * - :expression:`$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 <http://en.wikipedia.org/wiki/Subset>`_.
       Accepts exactly two argument expressions.
   

   * - :expression:`$setUnion`

     - Returns a set with elements that appear in *any* of the input sets.
   

   * - :expression:`$size`

     - Returns the number of elements in the array. Accepts a single
       expression as argument.
   

   * - :expression:`$slice`

     - Returns a subset of an array.
   

   * - :expression:`$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.
   

   * - :expression:`$sqrt`

     - Calculates the square root.
   

   * - :group:`$stdDevPop`

     - Returns the population standard deviation of the input values.
   
       .. versionchanged:: 3.2
          Available in both :pipeline:`$group` and :pipeline:`$project`
          stages.
   

   * - :group:`$stdDevSamp`

     - Returns the sample standard deviation of the input values.
   
       .. versionchanged:: 3.2
          Available in both :pipeline:`$group` and :pipeline:`$project`
          stages.
   

   * - :expression:`$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.
   

   * - :expression:`$strLenBytes`

     - Returns the number of UTF-8 encoded bytes in a string.
   

   * - :expression:`$strLenCP`

     - Returns the number of UTF-8 `code points
       <http://www.unicode.org/glossary/#code_point>`_ in a string.
   

   * - :expression:`$substr`

     - Deprecated. Use :expression:`$substrBytes` or :expression:`$substrCP`.
   

   * - :expression:`$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.
   

   * - :expression:`$substrCP`

     - Returns the substring of a string. Starts with the
       character at the specified UTF-8 `code point (CP)
       <http://www.unicode.org/glossary/#code_point>`_ index (zero-based)
       in the string and continues for the number of code points specified.
   

   * - :expression:`$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.
   

   * - :group:`$sum`

     - Returns a sum of numerical values. Ignores non-numeric values.
   
       .. versionchanged:: 3.2
          Available in both :pipeline:`$group` and :pipeline:`$project`
          stages.
   

   * - :expression:`$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.
   

   * - :expression:`$toBool`

     - Converts value to a boolean.
   

   * - :expression:`$toDate`

     - Converts value to a Date.
   

   * - :expression:`$toDecimal`

     - Converts value to a Decimal128.
   

   * - :expression:`$toDouble`

     - Converts value to a double.
   

   * - :expression:`$toInt`

     - Converts value to an integer.
   

   * - :expression:`$toLong`

     - Converts value to a long.
   

   * - :expression:`$toObjectId`

     - Converts value to an ObjectId.
   

   * - :expression:`$toString`

     - Converts value to a string.
   

   * - :expression:`$toLower`

     - Converts a string to lowercase. Accepts a single argument expression.
   

   * - :expression:`$toUpper`

     - Converts a string to uppercase. Accepts a single argument expression.
   

   * - :expression:`$trim`

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

   * - :expression:`$trunc`

     - Truncates a number to its integer.

   * - :expression:`$type`

     - Return the BSON data type of the field.
   

   * - :expression:`$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).
   

   * - :expression:`$year`

     - Returns the year for a date as a number (e.g. 2014).
   

   * - :expression:`$zip`

     - Merge two arrays together.
   


.. toctree::
   :titlesonly: 
   :hidden: 

   /reference/operator/aggregation/abs
   /reference/operator/aggregation/add
   /reference/operator/aggregation/addToSet
   /reference/operator/aggregation/allElementsTrue
   /reference/operator/aggregation/and
   /reference/operator/aggregation/anyElementTrue
   /reference/operator/aggregation/arrayElemAt
   /reference/operator/aggregation/arrayToObject
   /reference/operator/aggregation/avg
   /reference/operator/aggregation/ceil
   /reference/operator/aggregation/cmp
   /reference/operator/aggregation/concat
   /reference/operator/aggregation/concatArrays
   /reference/operator/aggregation/cond
   /reference/operator/aggregation/convert
   /reference/operator/aggregation/dateFromParts
   /reference/operator/aggregation/dateToParts
   /reference/operator/aggregation/dateFromString
   /reference/operator/aggregation/dateToString
   /reference/operator/aggregation/dayOfMonth
   /reference/operator/aggregation/dayOfWeek
   /reference/operator/aggregation/dayOfYear
   /reference/operator/aggregation/divide
   /reference/operator/aggregation/eq
   /reference/operator/aggregation/exp
   /reference/operator/aggregation/filter
   /reference/operator/aggregation/first
   /reference/operator/aggregation/floor
   /reference/operator/aggregation/gt
   /reference/operator/aggregation/gte
   /reference/operator/aggregation/hour
   /reference/operator/aggregation/ifNull
   /reference/operator/aggregation/in
   /reference/operator/aggregation/indexOfArray
   /reference/operator/aggregation/indexOfBytes
   /reference/operator/aggregation/indexOfCP
   /reference/operator/aggregation/isArray
   /reference/operator/aggregation/isoDayOfWeek
   /reference/operator/aggregation/isoWeek
   /reference/operator/aggregation/isoWeekYear
   /reference/operator/aggregation/last
   /reference/operator/aggregation/let
   /reference/operator/aggregation/literal
   /reference/operator/aggregation/ln
   /reference/operator/aggregation/log
   /reference/operator/aggregation/log10
   /reference/operator/aggregation/lt
   /reference/operator/aggregation/lte
   /reference/operator/aggregation/ltrim
   /reference/operator/aggregation/map
   /reference/operator/aggregation/max
   /reference/operator/aggregation/mergeObjects
   /reference/operator/aggregation/meta
   /reference/operator/aggregation/min
   /reference/operator/aggregation/millisecond
   /reference/operator/aggregation/minute
   /reference/operator/aggregation/mod
   /reference/operator/aggregation/month
   /reference/operator/aggregation/multiply
   /reference/operator/aggregation/ne
   /reference/operator/aggregation/not
   /reference/operator/aggregation/objectToArray
   /reference/operator/aggregation/or
   /reference/operator/aggregation/pow
   /reference/operator/aggregation/push
   /reference/operator/aggregation/range
   /reference/operator/aggregation/reduce
   /reference/operator/aggregation/reverseArray
   /reference/operator/aggregation/rtrim
   /reference/operator/aggregation/second
   /reference/operator/aggregation/setDifference
   /reference/operator/aggregation/setEquals
   /reference/operator/aggregation/setIntersection
   /reference/operator/aggregation/setIsSubset
   /reference/operator/aggregation/setUnion
   /reference/operator/aggregation/size
   /reference/operator/aggregation/slice
   /reference/operator/aggregation/split
   /reference/operator/aggregation/sqrt
   /reference/operator/aggregation/stdDevPop
   /reference/operator/aggregation/stdDevSamp
   /reference/operator/aggregation/strcasecmp
   /reference/operator/aggregation/strLenBytes
   /reference/operator/aggregation/strLenCP
   /reference/operator/aggregation/substr
   /reference/operator/aggregation/substrBytes
   /reference/operator/aggregation/substrCP
   /reference/operator/aggregation/subtract
   /reference/operator/aggregation/sum
   /reference/operator/aggregation/switch
   /reference/operator/aggregation/toBool
   /reference/operator/aggregation/toDate
   /reference/operator/aggregation/toDecimal
   /reference/operator/aggregation/toDouble
   /reference/operator/aggregation/toInt
   /reference/operator/aggregation/toLong
   /reference/operator/aggregation/toObjectId
   /reference/operator/aggregation/toString
   /reference/operator/aggregation/toLower
   /reference/operator/aggregation/toUpper
   /reference/operator/aggregation/trim
   /reference/operator/aggregation/trunc
   /reference/operator/aggregation/type
   /reference/operator/aggregation/week
   /reference/operator/aggregation/year
   /reference/operator/aggregation/zip

For the pipeline stages, see
:doc:`/reference/operator/aggregation-pipeline`.