Aggregation Commands

Some stages do more than transform — they write results ($out), count documents ($count), join collections ($lookup), or accumulate in $group.

Stages that output, join, and count

Some stages do more than transform — they write results ($out), count documents ($count), join collections ($lookup), or accumulate in $group.

Real-life example: $lookup is HR pulling employee names from the staff directory when payroll only stored employee IDs.

$lookup — left outer join between collections
$group + accumulators — $sum, $avg, $first, $last, $max, $min
$count — shortcut for { $group: { _id: null, n: { $sum: 1 } } }
$out — write pipeline results to a collection

$lookup — join two collections

$lookup runs a sub-pipeline on the foreign collection (modern syntax) or matches localField to foreignField (classic syntax).

Results land in a new array field — usually followed by $unwind when you expect one match.

Real-life example: Orders store userId; users store name and email — $lookup attaches customer details to each order for a support dashboard.

Frequent $lookup on hot read paths is a smell — consider embedding customer name on the order at write time for checkout APIs.

Classic and pipeline $lookup

// Classic: orders → users
db.orders.aggregate([
  { $match: { status: "completed" } },
  {
    $lookup: {
      from: "users",
      localField: "userId",
      foreignField: "_id",
      as: "customer"
    }
  },
  { $unwind: "$customer" },
  {
    $project: {
      orderId: 1,
      total: 1,
      customerName: "$customer.name",
      customerEmail: "$customer.email"
    }
  }
])

// Pipeline $lookup — join with extra filter on users
db.orders.aggregate([
  {
    $lookup: {
      from: "users",
      let: { uid: "$userId" },
      pipeline: [
        { $match: { $expr: { $eq: ["$_id", "$$uid"] } } },
        { $project: { name: 1, tier: 1 } }
      ],
      as: "customer"
    }
  }
])

$group accumulators — $first and friends

Inside $group, accumulators reduce many documents to one value per _id key.

$first and $last capture values after a $sort — common for 'latest order per user'.

Real-life example: $first after sorting by date gives you each student's most recent exam score in one row.

Latest order per customer

db.orders.aggregate([
  { $match: { status: "completed" } },
  { $sort: { userId: 1, createdAt: -1 } },
  {
    $group: {
      _id: "$userId",
      latestOrderId: { $first: "$_id" },
      latestTotal: { $first: "$total" },
      latestDate: { $first: "$createdAt" },
      orderCount: { $sum: 1 },
      lifetimeSpend: { $sum: "$total" }
    }
  },
  { $sort: { lifetimeSpend: -1 } },
  { $limit: 20 }
])

$count and $out

$count is sugar for counting documents at that pipeline point — great after $match.

$out replaces a collection with pipeline output — use for materialized views and nightly ETL jobs (not mid-request in user-facing APIs).

Real-life example: $out is printing yesterday's sales summary into a fresh binder labelled daily_summary — readers grab the binder instead of rerunning the whole calculation.

Count matching docs and materialize a report

// Quick count without fetching rows
db.orders.aggregate([
  { $match: { status: "pending", createdAt: { $lt: new Date(Date.now() - 86400000) } } },
  { $count: "stalePendingOrders" }
])

// Nightly: build summary collection for BI tools
db.orders.aggregate([
  { $match: { status: "completed" } },
  {
    $group: {
      _id: { $dateToString: { format: "%Y-%m-%d", date: "$createdAt" } },
      revenue: { $sum: "$total" },
      orders: { $sum: 1 }
    }
  },
  { $sort: { _id: 1 } },
  { $out: "daily_revenue_summary" }
])

Practical join + group: category leaderboard

Combine $lookup, $unwind, $group, and $sort for reports that would take many SQL joins and subqueries.

Real-life example: Rishtaara marketplace — show top sellers per category with shop name and total GMV for the admin homepage.

Seller leaderboard with $lookup

db.order_items.aggregate([
  { $match: { createdAt: { $gte: ISODate("2025-01-01") } } },
  {
    $lookup: {
      from: "sellers",
      localField: "sellerId",
      foreignField: "_id",
      as: "seller"
    }
  },
  { $unwind: "$seller" },
  {
    $group: {
      _id: { category: "$category", sellerId: "$sellerId" },
      shopName: { $first: "$seller.shopName" },
      gmv: { $sum: { $multiply: ["$qty", "$price"] } },
      units: { $sum: "$qty" }
    }
  },
  { $sort: { "_id.category": 1, gmv: -1 } },
  {
    $group: {
      _id: "$_id.category",
      topSeller: { $first: "$shopName" },
      topGmv: { $first: "$gmv" }
    }
  }
])