Expressions: dates, strings and conditionals

conceptThe concept

• Dates: $dateTrunc (5.0+) truncates to a unit with an optional timezone; $dateToString formats; $dateDiff, $dateAdd; $year, $month, $dayOfWeek, $hour.
• Strings: $concat, $toLower/$toUpper, $trim, $split, $substrCP, $regexMatch, $strLenCP.
• Types: $toInt, $toDouble, $toDate, $toString, and $convert with onError/onNull for dirty data.
• Conditionals: $cond, $switch, $ifNull.

whyWhy it matters

Time-bucketed reports are the most common analytic query. Timezones matter: a day in India starts 5.5 hours before a UTC day, so "orders per day" differs depending on where you truncate.

Use caseReal-world scenario

A finance report needs revenue per calendar month in the Asia/Kolkata timezone. $dateTrunc: { date: "$createdAt", unit: "month", timezone: "Asia/Kolkata" } groups correctly; truncating in UTC would put the first hours of each month into the previous one.

Try itExamples

Run and edit these in the interactive lesson.

db.orders.aggregate([
  { $match: { createdAt: { $gte: ISODate("2026-01-01") } } },
  { $group: { _id: { $dateTrunc: { date: "$createdAt", unit: "month" } }, orders: { $sum: 1 }, revenue: { $sum: "$total" } } },
  { $sort: { _id: 1 } },
  { $project: { _id: 0, month: { $dateToString: { date: "$_id", format: "%Y-%m" } }, orders: 1, revenue: { $round: ["$revenue", 0] } } }
])

db.events.aggregate([
  { $match: { type: "checkout" } },
  { $group: { _id: { $dayOfWeek: "$ts" }, n: { $sum: 1 }, p95ish: { $max: "$latencyMs" } } },
  { $sort: { _id: 1 } }
])

db.customers.aggregate([
  { $limit: 5 },
  { $project: {
      _id: 0,
      first: { $arrayElemAt: [{ $split: ["$name", " "] }, 0] },
      domain: { $arrayElemAt: [{ $split: ["$email", "@"] }, 1] },
      label: { $concat: [{ $toUpper: "$tier" }, " / ", "$address.city"] },
      phone: { $ifNull: ["$phone", "not provided"] } } }
])

db.lab_dirty.drop()
db.lab_dirty.insertMany([{ v: "42" }, { v: "4.5" }, { v: "n/a" }, { v: null }])
db.lab_dirty.aggregate([{ $project: { _id: 0, v: 1, n: { $convert: { input: "$v", to: "double", onError: -1, onNull: 0 } } } }])

internalsUnder the hood

Expressions are evaluated per document in the pipeline; they cannot use indexes unless they appear in a leading $match as simple comparisons. That is why you filter on the raw createdAt range first and bucket afterwards, never $match: { $expr: { $eq: [{ $month: "$createdAt" }, 9] } } on a large collection.

Avoid / preferDo this, not that

{ $group: { _id: { $dateToString: { format: "%Y-%m-%d", date: "$createdAt" } } } }

{ $group: { _id: { $dateToString: {
  format: "%Y-%m-%d", date: "$createdAt", timezone: "Asia/Kolkata"
} } } }

{ $project: { initials: { $substr: ["$name", 0, 3] } } }

{ $project: { initials: { $substrCP: ["$name", 0, 3] } } }

PitfallsCommon mistakes

• Bucketing by day in UTC when the business reports in local time.
• Filtering with $expr on computed date parts instead of a raw date range, which forces a full scan.
• Using $toInt on dirty data without $convert + onError, which fails the whole pipeline on the first bad value.

LimitsWhen not to use it

Heavy string work such as fuzzy matching or extracting fields from free text across millions of documents is slow in a pipeline. Do it once at write time, in the application, or in a search engine.

SecuritySecurity notes

Validate timezone names and regex patterns that come from users. An unknown timezone fails the whole pipeline, and an unescaped pattern in $regexMatch can be made to backtrack for a very long time.

ChallengeTry it yourself

db.orders.aggregate([
  { $match: { createdAt: { $gte: ISODate("2025-01-01T00:00:00+05:30"), $lt: ISODate("2026-01-01T00:00:00+05:30") } } },
  { $group: { _id: { $isoDayOfWeek: { date: "$createdAt", timezone: "Asia/Kolkata" } }, orders: { $sum: 1 } } },
  { $sort: { _id: 1 } }
])

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.