Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 70 ยท Aggregation pipeline

Expressions and Operators

Compute values inside pipelines: field paths, $add and $multiply, $cond and $switch, string operators, $ifNull, date operators and $addFields.

A stage such as $group or $project decides where data goes. Expressions decide what values it holds. Whenever you write "$price", { $multiply: ["$qty", "$price"] } or { $cond: [...] } inside a pipeline, you are writing an expression. Expressions are evaluated once per document (or once per group for accumulators) and can be nested as deeply as you like.

In this lesson you will meet the everyday expression operators: arithmetic, conditionals, string and date helpers, and $ifNull for missing data. You will also learn $addFields (and its alias $set), the stage for adding computed fields without dropping the existing ones.

MongoDB cannot run on this site. The mongosh blocks are real commands shown read-only. The runnable examples use a stand-in engine written in plain JavaScript, with code for the operators used here at the top of each example. It treats dates as UTC and supports only the operators shown in this lesson; real MongoDB has many more.

Expression building blocks

Field paths "$price" reads a field.
Literals Numbers, strings, booleans.
Operators { $add: [ ... ] } and friends.
1 / 2

Field paths and operators

An expression is one of three things:

  1. A field path: a string starting with $, such as "$price" or "$address.city". It stands for the value of that field in the current document. The special path "$$ROOT" stands for the whole document.
  2. A literal: a number, boolean, null, or a string that does not start with $.
  3. An operator expression: an object with a single key that starts with $, whose value is the arguments, usually an array: { $add: ["$a", 5] }.

Because operator arguments are themselves expressions, you can nest them:

JavaScript
{ $multiply: ["$qty", { $subtract: ["$price", "$discount"] }] }

To use a string that starts with a dollar sign as plain text, wrap it: { $literal: "$100" }.

One syntax point catches many beginners: the comparison operators work differently in expressions than in $match filters. In a filter you write { qty: { $gt: 10 } }. In an expression you write { $gt: ["$qty", 10] }, with both operands in an array. To use an expression inside $match, wrap it in $expr, which you will see in Element and Evaluation Operators.

Arithmetic

Operator Meaning
$add: [a, b, ...] Sum. If one argument is a date, the others are milliseconds to add.
$subtract: [a, b] a - b. Two dates give the difference in milliseconds.
$multiply: [a, b, ...] Product.
$divide: [a, b] a / b; dividing by zero is an error.
$mod: [a, b] Remainder.
$round: [x, places], $ceil, $floor, $abs Rounding and absolute value.

If any argument is null or missing, $add, $subtract, $multiply and $divide return null, not zero. That is why $ifNull (below) is so useful in data that is not perfectly clean.

Powers, roots and logarithms

The same family has operators for scientific and financial calculations. They all take numbers (or expressions that give numbers) and return null when an argument is null or missing.

Operator Meaning Notes
$sqrt: x Square root A negative number is an error.
$pow: [base, exponent] base raised to exponent A negative exponent gives a fraction; 0 to a negative power is an error.
$exp: x e raised to x The inverse of $ln.
$ln: x Natural logarithm x must be greater than 0.
$log: [x, base] Logarithm of x in any base $log: [8, 2] is 3.
$log10: x Logarithm in base 10 Handy for orders of magnitude and decibel-style scales.
$trunc: [x, places] Cut off decimals without rounding Compare with $round and $floor.
$abs: x Absolute value Useful before comparing distances or differences.

A typical use is compound growth: a balance after n years at rate r is { $multiply: ["$principal", { $pow: [{ $add: [1, "$rate"] }, "$years"] }] }. The stand-in below implements the operators with JavaScript's Math functions so you can see the values they produce, including a negative exponent and a rejected square root.

JS Powers, roots and logarithms

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice that $trunc keeps 0.66 for document 3 (2 / 3 is 0.6666...) where rounding would give 0.67. Change side to -4 and the run reports the $sqrt error. Real MongoDB also raises an error for $ln or $log10 of a number that is zero or negative, so filter such data with $match first.

Conditionals: $cond and $switch

$cond is the "if/else" of expressions. It has an array form and a named form that is easier to read:

JavaScript
{ $cond: { if: { $gte: ["$total", 100] }, then: "free shipping", else: "standard" } }
{ $cond: [{ $gte: ["$total", 100] }, "free shipping", "standard"] }

The condition counts as false for false, 0, null and a missing field; everything else is true. For more than two outcomes, nesting $cond gets ugly, so use $switch:

JavaScript
{
  $switch: {
    branches: [
      { case: { $gte: ["$score", 90] }, then: "A" },
      { case: { $gte: ["$score", 80] }, then: "B" }
    ],
    default: "C"
  }
}

Branches are tried in order and the first true case wins. Without a default, a document that matches no branch causes an error, so always include one.

Handling missing values with $ifNull

{ $ifNull: ["$nickname", "$name", "anonymous"] } returns the first argument that is not null or missing. Use it to supply defaults before doing arithmetic: { $multiply: ["$qty", { $ifNull: ["$price", 0] }] }.

JS Arithmetic, $cond and $ifNull

Press Run (or Ctrl + Enter) to see the console output here.

No output.

String operators

Operator Result
$concat: [a, b, ...] Joins strings. Returns null if any argument is null or missing.
$toUpper / $toLower: s Changes case.
$substrCP: [s, start, length] Substring by code points (characters). $substrBytes counts UTF-8 bytes instead.
$strLenCP: s Length in code points.
$split, $trim, $replaceOne, $regexMatch Splitting, trimming, replacing and regex matching (not covered by the practice engine).

The old $substr operator is deprecated in favor of $substrCP and $substrBytes. $concat and null is a trap: { $concat: ["Dr. ", "$lastName"] } gives null for any document without a last name. Combine it with $ifNull: { $concat: ["Dr. ", { $ifNull: ["$lastName", "Unknown"] }] }.

JS Strings and a grading $switch

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Date operators

Dates in MongoDB are stored in UTC. Date operators pull parts out of a date:

  • $year, $month (1 to 12), $dayOfMonth, $hour, $minute, $second
  • $dayOfWeek (1 is Sunday, 7 is Saturday), $dayOfYear, $isoWeek
  • $dateToString: { format: "%Y-%m-%d", date: "$createdAt" } formats a date as text
  • $dateTrunc, $dateAdd, $dateDiff round, shift and compare dates in newer versions

These are the foundation of "per month" reports: group by { $dateToString: { format: "%Y-%m", date: "$createdAt" } }, or by { year: { $year: "$createdAt" }, month: { $month: "$createdAt" } }. Most date operators also accept a timezone option ({ date: "$createdAt", timezone: "Europe/Berlin" }), which matters when "today" should follow local midnight. The stand-in engine below always uses UTC.

$addFields and $set

$project replaces the document with exactly the fields you list. $addFields does the opposite: it keeps every existing field and adds or overwrites the ones you name. $set is an alias with the same behavior, named to match the update operator $set (and $unset removes fields in the same way). Use $addFields for intermediate calculations and $project for the final shape.

Both can reach into embedded documents with dot notation: { $addFields: { "address.country": "DE" } }.

JS Dates, $addFields and a monthly report

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Array, string, object and conversion expressions

The sections above cover the everyday operators. A second group is worth knowing, because it appears in $project, $addFields, $group and $expr whenever you reshape data. Each one is an expression: it takes values (field paths such as "$tags", or literals) and returns a value.

Arrays

Operator What it returns
$isArray: x true if x is an array (useful before $size, which fails on non-arrays)
$concatArrays: [a, b, ...] One array with all the elements, in order; null if any input is null
$reverseArray: a The elements in reverse order
$indexOfArray: [a, value] Position of the first match (0-based), or -1
$zip: { inputs: [a, b] } Pairs elements by position: [[a0, b0], [a1, b1], ...]; stops at the shortest array unless useLongestLength: true (with defaults)
$arrayElemAt, $first, $last, $slice, $range Pick, slice or generate elements
$map, $filter, $reduce Transform, keep or fold elements (see Working with Arrays in Pipelines)

Strings

Operator What it returns
$trim: { input, chars } Removes whitespace (or the given characters) from both ends; $ltrim and $rtrim do one side
$strcasecmp: [a, b] Case-insensitive comparison: -1, 0 or 1
$cmp: [a, b] Comparison of any two values in BSON order: -1, 0 or 1
$substrCP, $strLenCP, $split, $replaceOne, $replaceAll, $regexMatch Slice, measure, split, replace and match text

Objects

Operator What it returns
$mergeObjects: [a, b, ...] One document combining the inputs; later values win for duplicate keys
$objectToArray: doc [ { k: "name", v: "Ada" }, ... ], so you can iterate over unknown keys
$arrayToObject: pairs The reverse: builds a document from { k, v } pairs (or [k, v] pairs)
$getField, $setField Read or write a field whose name is computed or contains dots

Variables and type conversion

$let defines variables for one expression, so a repeated sub-expression is written once:

JavaScript
{ $let: { vars: { net: { $subtract: ["$price", "$discount"] } },
          in: { $multiply: ["$$net", 1.2] } } }      // $$net refers to the variable

Variables use a double dollar ($$name). $convert and its shortcuts ($toInt, $toDouble, $toString, $toDate, $toObjectId, $toBool) change types, which matters when imported data stores numbers as text:

JavaScript
{ $convert: { input: "$price", to: "double", onError: 0, onNull: 0 } }
{ $toInt: "$qty" }       // errors on "abc"; use $convert with onError to be forgiving

A realistic stage ties these together:

JavaScript
db.people.aggregate([
  { $project: {
      name: { $trim: { input: "$name" } },
      tagCount: { $cond: [{ $isArray: "$tags" }, { $size: "$tags" }, 0] },
      profile: { $mergeObjects: ["$defaults", "$overrides"] },
      qty: { $convert: { input: "$qty", to: "int", onError: 0, onNull: 0 } }
  } }
])

The mini evaluator below implements the operators from this section so you can try them. It is a stand-in written for this lesson, covering only these operators with simplified rules, and it is not MongoDB.

JS Array, string, object and conversion expressions

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Three of these deserve a second look. $size throws on a non-array, so guard it with $isArray when a field might hold something else. $mergeObjects lets later inputs win, which makes it ideal for "defaults, then overrides". And $convert with onError and onNull turns messy imported strings into numbers without stopping the whole aggregation.

Putting expressions to work

Expressions are not limited to $project and $addFields. You will use them:

  • inside $group as accumulator arguments: { $sum: { $multiply: ["$qty", "$price"] } }
  • inside $match through $expr
  • in $sort after an $addFields computed the key
  • in update pipelines (an update can be given an array of stages, so $set with expressions works in updateOne)

A few cautions. Expressions are evaluated at query time on every document, so they cannot use indexes; if you filter by a computed value often, store it (see the computed pattern in Schema Design Patterns). Comparing different types behaves by BSON ordering rules (see BSON Types). And errors such as dividing by zero stop the whole pipeline, so guard with $cond or $ifNull.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does { $multiply: ["$qty", "$price"] } return for a document whose price field is missing?

    Arithmetic operators return null when an argument is null or missing. Use $ifNull to supply a default such as 0.
  2. 2. What happens when no $switch branch matches and there is no default?

    A $switch without a matching branch or a default raises an error, so always add a default.
  3. 3. What is the difference between $project and $addFields?

    $project reshapes the document to exactly what you list (plus _id by default). $addFields (alias $set) keeps everything and adds or overwrites fields.
  4. 4. What does $dayOfWeek return for a Sunday?

    MongoDB numbers days from 1 (Sunday) to 7 (Saturday); there is no locale setting.
  5. 5. How do you write 'qty greater than 10' as an aggregation expression?

    In expressions comparison operators take an array of operands. The { qty: { $gt: 10 } } form belongs to query filters such as $match.

Summary

  • An expression is a field path ("$price"), a literal, or an operator object such as { $add: [...] }; they nest freely.
  • Arithmetic returns null for missing values, so guard with $ifNull.
  • $cond handles two outcomes, $switch several (always include a default).
  • $concat returns null if an argument is null; use $substrCP rather than the deprecated $substr.
  • Date operators such as $year, $month, $dayOfWeek (1 = Sunday) and $dateToString power per-period reports.
  • $addFields / $set add computed fields while keeping the rest; expressions cannot use indexes.

Next, learn to work with arrays inside pipelines in Working with Arrays in Pipelines.