Explain MongoDB aggregation pipeline and how it differs from SQL GROUP BY with practical examples

MongoDB Aggregation Pipeline

db.applications.aggregate([
  // Stage 1: Filter
  { $match: { status: { $in: ["OFFER", "REJECTED", "WITHDRAWN"] } } },

  // Stage 2: Group by month + company
  {
    $group: {
      id: {
        month: { $dateToString: { format: "%Y-%m", date: "$createdAt" } },
        company: "$company",
      },
      total: { $sum: 1 },
      offers: {
        $sum: { $cond: [{ $eq: ["$status", "OFFER"] }, 1, 0] }
      },
      avgDaysToOffer: {
        $avg: {
          $cond: [
            { $eq: ["$status", "OFFER"] },
            { $divide: [
              { $subtract: ["$offerDate", "$dateApplied"] },
              86400000 // ms in a day
            ]},
            null
          ]
        }
      },
    },
  },

  // Stage 3: Calculate success rate
  {
    $addFields: {
      successRate: { $multiply: [{ $divide: ["$offers", "$total"] }, 100] },
    },
  },

  // Stage 4: Sort
  { $sort: { "id.month": -1, total: -1 } },

  // Stage 5: Limit
  { $limit: 100 },
]);

SQL Equivalent

SELECT
  TOCHAR(dateapplied, 'YYYY-MM') AS month,
  company,
  COUNT(*) AS total,
  SUM(CASE WHEN status = 'OFFER' THEN 1 ELSE 0 END) AS offers,
  ROUND(
    100.0 * SUM(CASE WHEN status = 'OFFER' THEN 1 ELSE 0 END) / COUNT(*),
    2
  ) AS successrate,
  AVG(
    CASE WHEN status = 'OFFER'
    THEN EXTRACT(EPOCH FROM (offerdate - dateapplied)) / 86400
    END
  ) AS avgdaystooffer
FROM applications
WHERE status IN ('OFFER', 'REJECTED', 'WITHDRAWN')
GROUP BY TOCHAR(dateapplied, 'YYYY-MM'), company
ORDER BY month DESC, total DESC
LIMIT 100;

Pipeline Stages Cheat Sheet

StageSQL EquivalentPurpose

$matchWHEREFilter documents
$groupGROUP BY + aggregate functionsGroup and calculate
$sortORDER BYSort results
$limitLIMITLimit results
$skipOFFSETPagination
$projectSELECTShape output
$lookupJOINJoin collections
$unwind—Flatten arrays
$facet—Multiple pipelines in one
$addFieldscomputed columnsAdd calculated fields

Performance Tips

1. Put $match first — Reduces documents flowing through pipeline
2. Use indexes — $match can use indexes if first stage
3. Avoid $unwind on large arrays — Creates a document per element
4. Use $facet for multiple aggregations — Runs in parallel on same input

db.applications.aggregate([
  { $facet: {
    byStatus: [{ $group: { id: "$status", count: { $sum: 1 } } }],
    byMonth: [{ $group: { id: { $month: "$createdAt" }, count: { $sum: 1 } } }],
    totalApplications: [{ $count: {} }],
  }}
]);