MongoDB aggregation pipeline - building reports with the database itself

Say the admin wants to know: last month, which food sold the most, how much money was sold in total, what the daily average was. If you try to do this math in code, you have to pull thousands of orders and add and subtract yourself - slow, and complicated. With the aggregation pipeline you can make the database itself do this math, step by step.

A pipeline means a row of steps

The name says it - data goes through a pipe, and each step (stage) does one job on it. $match trims away unnecessary data (like SQL's WHERE), $group groups and calculates, $sort arranges, $project picks which fields go forward, and $lookup joins data from another collection (close to a JOIN).

db.orders.aggregate([
  // 1. Only this month's delivered orders
  { $match: { status: "delivered", createdAt: { $gte: monthStart } } },
  // 2. Open the items array so each item becomes a separate row
  { $unwind: "$items" },
  // 3. Group by product and calculate
  { $group: {
      _id: "$items.name",
      totalQty: { $sum: "$items.qty" },
      revenue: { $sum: { $multiply: ["$items.qty", "$items.price"] } }
  } },
  // 4. Most sold first
  { $sort: { totalQty: -1 } },
  { $limit: 5 }
]);

The most important habit: the earlier you place $match, the better. If you cut the data down in the first step, the remaining steps work with less data - the pipeline gets fast. For the same reason, having indexes on the fields you match or group by is gold. And one more thing - when a pipeline gets big, keep comments step by step in the code, otherwise six months later you will not understand your own writing.

The mistake everyone makes

Avoiding aggregation and pulling all the data into code to calculate with JavaScript. When data is small you do not notice, but once orders pass a hundred thousand, the API hangs for seconds and eats memory. Calculation work belongs to the database - that is what it is built for.

Today's practice

Build three reports with aggregation from your orders collection: (1) total sales by day, (2) the 5 best-selling items, (3) the 5 customers who ordered the most. Before each one, write on paper which stages you will need.

How to say it in an interview

With the aggregation pipeline I build complex reports in the database itself - match to trim first, group to calculate, sort-limit to arrange the result. That way raw data does not have to travel over the network, and keeping match first keeps the pipeline fast.