MongoDB, Part 2: The Aggregation Pipeline, Stage by Stage

In part 1 I modelled a tiny commerce domain in MongoDB: products and customers as their own collections, and an Order that embeds its lines but references its customer by id. That gives you documents that read in one query. Now the harder question: how do you get reporting out of them — revenue per product, top customers, average order value — when the numbers you want are buried inside arrays across thousands of documents?

That's the aggregation pipeline. This post walks the five queries I wrote to learn it, each one earning a new stage.

Where repositories stop

Spring Data repositories are great until they aren't. findByStatus(...) writes itself; SELECT status, COUNT(*), AVG(total) GROUP BY status does not. There's no method name for that. So aggregation is where you drop to the lower-level API, MongoTemplate:

@Component
@RequiredArgsConstructor
public class AggregationDemo {
    private final MongoTemplate mongo;

    private void run(String title, Aggregation agg) {
        AggregationResults<Document> results =
            mongo.aggregate(agg, "orders", Document.class);
        results.getMappedResults().forEach(doc -> log.info(doc.toJson()));
    }
}

The mental model that unlocked it for me: a pipeline is an assembly line. Documents from the orders collection enter stage one, each stage reshapes the stream and hands it to the next, and whatever falls out the end is your result. It's not one query — it's a sequence of transforms. Very Unix-pipe, if that's your background.

$unwind: exploding the embedded array

Here's the tension. Embedding order lines was the right call for reads — one document, everything you need. But now I want revenue per product, and a single order document holds several products inside its lines array. You can't group across arrays that are still nested. So the first move is to flatten:

Aggregation agg = newAggregation(
    unwind("lines"),
    project()
        .and("lines.productName").as("product")
        .andExpression("lines.unitPrice * lines.quantity").as("revenue"),
    group("product").sum("revenue").as("revenue"),
    sort(Sort.Direction.DESC, "revenue"));

$unwind is the star. It takes one order with three lines and emits three documents, each a copy of the order but carrying a single line. Now every product sits in its own document and $group can fold them together. In SQL terms this is the lines table you never had to declare — MongoDB materialises it on the fly. $project then computes unitPrice * quantity per line, and $group sums by product name. Same shape as SUM(unitPrice*quantity) GROUP BY productName, just spelled as stages.

One nicety: you don't always need the intermediate $project. Grouping straight on a nested path works too — group("lines.productName").sum("lines.quantity") gives you units sold per product with one fewer stage.

The gotcha: after $group, the key is always _id

This one bit me. When you $group, the field you grouped by is always renamed to _id in the output — not the name you grouped on. Group by customerId and the result documents have that value sitting in _id, not customerId. It's obvious in hindsight and invisible until your next stage references the wrong field and quietly returns nothing.

$lookup: a JOIN with sharp edges

MongoDB isn't relational, but $lookup is the closest thing to a JOIN. "Top customers by spend" needs one — orders hold a customerId, but I want the customer's name:

Aggregation agg = newAggregation(
    group("customerId").sum("total").as("spend").count().as("orders"),
    sort(Sort.Direction.DESC, "spend"),
    lookup("customers", "_id", "_id", "customer"),
    unwind("customer"),
    project("spend", "orders").and("customer.name").as("name"));

Three things worth internalising here. First, notice localField is "_id" — that's the _id gotcha from above paying off, since after the group it holds the customer id. Second, $lookup always produces an array in the target field, even for a 1:1 match, so you almost always $unwind it immediately afterwards. Third — and this is the real lesson — I group first, then look up. Joining four already-grouped rows is cheap; looking up the customer once per raw order would do the join thousands of times over. Stage order is a cost decision.

The type-mismatch bug that joins nothing

Here's the one that cost me a genuinely confusing hour, and the reason my customers have ids like "alice" instead of generated ObjectIds. $lookup matches on exact BSON type. My Order.customerId is a plain String field, so it's stored as a BSON string. But an @Id that looks like a 24-char hex string gets stored as an ObjectId. String and ObjectId are different types, so the join matches… nothing. No error, no warning — just an empty customer array every time.

String ≠ ObjectId. A $lookup across a type boundary silently returns zero matches. Keep both sides the same BSON type, or cast explicitly in the pipeline.

Giving customers explicit string ids keeps both sides String and the join works. In a real system you'd more likely store the reference as an ObjectId on both ends — the point is that one of them has to give, and MongoDB won't tell you which.

$match first, always

The last query — revenue from PAID orders only — exists purely to make one rule stick:

Aggregation agg = newAggregation(
    match(Criteria.where("status").is("PAID")),  // stage 1: filter early
    unwind("lines"),
    project()
        .and("lines.productName").as("product")
        .andExpression("lines.unitPrice * lines.quantity").as("revenue"),
    group("product").sum("revenue").as("revenue"),
    sort(Sort.Direction.DESC, "revenue"));

Put $match first. It runs before $unwind and $group, it can use the index on status, and it throws away documents before any real work happens. The exact same $match placed last produces the identical answer — just slowly, after you've unwound and grouped rows you were always going to discard. Filter early, filter on an indexed field, and the rest of the pipeline has less to chew on.

What actually stuck

Four things I'll carry forward: $unwind is how you pay back the convenience of embedding when it's time to aggregate; after $group your key lives in _id; $lookup is a JOIN that returns an array and demands matching BSON types; and stage order is performance, not just style — match early, join late. Repositories cover the CRUD 90%; the pipeline is where the interesting 10% lives.

Next in the series: indexes — how to actually know whether that $match hit the index or quietly scanned the whole collection.