MongoDB — Aggregation Pipeline

A multi-stage data-processing language. Each stage takes documents in and emits documents out. The replacement for SQL’s GROUP BY / JOIN / window functions / CTEs — different syntax, similar power.

The pipeline shape

db.orders.aggregate([
    {"$match": {"status": "completed", "created_at": {"$gte": cutoff}}},
    {"$lookup": {"from": "users", "localField": "user_id", "foreignField": "_id", "as": "user"}},
    {"$unwind": "$user"},
    {"$group": {"_id": "$user.country", "total": {"$sum": "$amount"}, "count": {"$sum": 1}}},
    {"$sort": {"total": -1}},
    {"$limit": 10},
])

Read like SQL’s left-to-right: filter → join → flatten → aggregate → sort → limit.

Core stages

$match — filter

{"$match": {"status": "pending", "total": {"$gt": 100}}}

Same query syntax as .find(). Put $match as early as possible — it reduces work for downstream stages and can use indexes (if it’s first in the pipeline).

$project — shape output

{"$project": {
    "_id": 0,
    "order_id": "$_id",
    "year": {"$year": "$created_at"},
    "total_with_tax": {"$multiply": ["$total", 1.07]},
}}

Drops fields not mentioned, computes new ones with expressions. Like SQL SELECT.

$group — aggregate

{"$group": {
    "_id": {"country": "$user.country", "month": {"$month": "$created_at"}},
    "total": {"$sum": "$amount"},
    "avg": {"$avg": "$amount"},
    "count": {"$sum": 1},
    "max_order": {"$max": "$amount"},
    "items": {"$push": "$items"},        # collect all values into an array
}}

The _id is the grouping key (single field, document, or expression). Accumulators: $sum, $avg, $min, $max, $first, $last, $push, $addToSet.

$sort and $limit

{"$sort": {"total": -1}},
{"$limit": 100},

$sort in memory is limited to 100 MB by default (raise with allowDiskUse=True if you need bigger sorts).

$lookup — left outer join

{"$lookup": {
    "from": "users",
    "localField": "user_id",
    "foreignField": "_id",
    "as": "user",
}}

Joins orders.user_id = users._id, embeds the matching users doc(s) as user array. Always an array — even single matches. Use $unwind to flatten.

Performance: $lookup is expensive; it scans the foreign collection per input doc unless the foreign side is indexed. Always index foreignField.

For “JOIN then aggregate,” there’s also a pipeline-form $lookup that pre-filters the foreign side:

{"$lookup": {
    "from": "orders",
    "let": {"uid": "$_id"},
    "pipeline": [
        {"$match": {"$expr": {"$eq": ["$user_id", "$$uid"]}, "status": "completed"}},
        {"$project": {"total": 1}},
    ],
    "as": "completed_orders",
}}

Reduces the join’s output before it’s attached.

$unwind — flatten arrays

{"$unwind": "$items"}

A doc with items: [a, b, c] becomes 3 docs, each with items: a, items: b, items: c. Standard before grouping by array elements.

Options: preserveNullAndEmptyArrays: true keeps docs with empty/missing arrays (analogous to LEFT JOIN).

$facet — multiple pipelines, one pass

{"$facet": {
    "by_country": [
        {"$group": {"_id": "$country", "total": {"$sum": "$amount"}}}
    ],
    "by_month": [
        {"$group": {"_id": {"$month": "$created_at"}, "total": {"$sum": "$amount"}}}
    ],
    "top_users": [
        {"$group": {"_id": "$user_id", "total": {"$sum": "$amount"}}},
        {"$sort": {"total": -1}},
        {"$limit": 10}
    ],
}}

Run multiple aggregations on the same input set, returning all results in one document. Saves N queries.

$addFields / $set

{"$addFields": {"total_with_tax": {"$multiply": ["$total", 1.07]}}}

Adds computed fields without dropping others (vs $project which is exclusive). $set is an alias.

$replaceRoot — promote a sub-doc

{"$replaceRoot": {"newRoot": "$user"}}

Replaces the document with the value at $user. Common after $lookup + $unwind when you want the joined doc to be the result.

$bucket, $bucketAuto — histograms

{"$bucket": {
    "groupBy": "$age",
    "boundaries": [0, 18, 30, 45, 60, 100],
    "default": "other",
    "output": {"count": {"$sum": 1}},
}}

Histogram-style aggregation. $bucketAuto picks boundaries automatically.

$out / $merge — write results

{"$out": "monthly_stats"}                # replaces target collection
{"$merge": {"into": "monthly_stats", "whenMatched": "merge"}}   # upsert per doc

Persists pipeline output as a collection. Useful for pre-computed views / reports.

Performance tips

1. $match first. Filters before joins, projects, and groups. Hits indexes when first in the pipeline.
2. $project early to reduce doc size. Less data per stage.
3. Index the join key on the foreign side. $lookup without an index = full scan per input doc.
4. Avoid $unwind of huge arrays. Doc count explodes.
5. Don’t sort before grouping if you can avoid it. $group doesn’t need sorted input.
6. allowDiskUse=True for big sorts / groups exceeding 100 MB.
7. Use explain("executionStats") to verify stage costs.

Common patterns

Top-N per group

“Top 3 most-expensive orders per user”:

[
    {"$sort": {"user_id": 1, "total": -1}},
    {"$group": {
        "_id": "$user_id",
        "top_orders": {"$push": "$$ROOT"},
    }},
    {"$project": {
        "top_orders": {"$slice": ["$top_orders", 3]}
    }},
]

$$ROOT is the entire document. $slice takes the first 3.

Running totals

[
    {"$sort": {"date": 1}},
    {"$setWindowFields": {
        "partitionBy": "$user_id",
        "sortBy": {"date": 1},
        "output": {
            "running_total": {
                "$sum": "$amount",
                "window": {"documents": ["unbounded", "current"]}
            }
        }
    }},
]

$setWindowFields (5.0+) — window functions, like SQL.

Time-series rollup

[
    {"$match": {"timestamp": {"$gte": last_hour}}},
    {"$group": {
        "_id": {
            "device": "$device_id",
            "minute": {"$dateTrunc": {"date": "$timestamp", "unit": "minute"}}
        },
        "avg": {"$avg": "$value"},
        "min": {"$min": "$value"},
        "max": {"$max": "$value"},
    }},
    {"$out": "device_metrics_per_minute"},
]

Bucket time-series into per-minute aggregates. Run on a schedule.

Aggregation vs map-reduce

MongoDB has both. Map-reduce is legacy — slower, less expressive, deprecated in newer versions. Use aggregation pipeline.

Aggregation vs MongoDB views

db.create_collection(
    "high_value_orders",
    viewOn="orders",
    pipeline=[{"$match": {"total": {"$gte": 100}}}],
)

A view is a saved pipeline. Read-only, evaluated at query time. Useful for sharing pre-filtered access patterns. Not materialized (unless you use $out/$merge).

Async with Motor

async def top_users():
    cursor = db.orders.aggregate([
        {"$group": {"_id": "$user_id", "total": {"$sum": "$amount"}}},
        {"$sort": {"total": -1}},
        {"$limit": 10},
    ])
    return [doc async for doc in cursor]

Aggregation returns a cursor; iterate with async for.

Interview angle

• “What’s the aggregation pipeline?” — multi-stage data processing language. Each stage transforms documents ($match, $group, $lookup, $project, etc.). Replaces SQL’s GROUP BY / JOIN / window functions with composable stages.
• “How does $lookup work?” — left outer join. localField from input matched against foreignField in target collection; matches embedded as array in as field. Always an array (even single match) — use $unwind to flatten.
• “Performance tips for a slow aggregation?” — $match and $project early; index $lookup foreign field; avoid $unwind on huge arrays; allowDiskUse=True for big sorts; use explain to find bottlenecks.
• “How do you compute top-N per group?” — sort by group + metric, then $group with $push + $slice. Or in 5.0+, use $setWindowFields with $rank.
• “$facet use case?” — run multiple aggregations on the same input in one pass. Dashboard backends: counts by country, counts by month, top users — all from one collection scan.
• “How do you persist aggregation output?” — $out (replaces target collection) or $merge (upsert per doc). Use for materialized views / pre-computed reports refreshed on a schedule.
• “Aggregation vs MongoDB views?” — view is a saved pipeline, evaluated at query time, not materialized. Pipeline is the actual computation. Views encapsulate; pipelines transform.