MongoDB – $lookup, $unwind and $facet

MongoDB can join. $lookup pulls documents from another collection into the pipeline, and it works well — within limits that are worth knowing before you build a report on it. This lesson covers the join, the stages that usually accompany it, and the one mistake that makes a join return absolutely nothing without any error at all.

$lookup

Four fields, and the names are unfortunately easy to mix up:

db.comments.aggregate([
  { $lookup: {
      from:         "reels",     // the OTHER collection
      localField:   "reelId",    // field on the document flowing through
      foreignField: "_id",       // field on the other collection
      as:           "reel"       // where to put the result
  } }
])

as is always an array, even when exactly one document matches — because $lookup is a left outer join and cannot know there will only be one. A non-match produces an empty array rather than dropping the row.

The trap: types must match exactly

$lookup compares localField to foreignField with strict type equality. A BSON string never equals an ObjectId, even when they print identically. When they differ, every row gets an empty array, the $unwind after it drops all of them, and your report is []:

// metadata.reelId stored as a STRING, reels._id is an ObjectId
db.view_events.aggregate([
  { $lookup: { from: "reels", localField: "metadata.reelId", foreignField: "_id", as: "reel" } },
  { $match: { "reel.0": { $exists: true } } },
  { $count: "matched" }
])
// []   ← nothing matched, and nothing said so

This is not hypothetical. It is the single most common way a MongoDB aggregation silently fails, and it usually arrives through an object mapper: you declare a Java field as String because your API uses strings, and it lands in BSON as a string while the _id it points at is an ObjectId.

Two fixes. Store the right type in the first place — which is what ReelCMS does with @Field(targetType = FieldType.OBJECT_ID), from lesson 3. Or convert inside the pipeline, which works but pays the cost on every query rather than once at write time:

{ $addFields: { reelOid: { $toObjectId: "$metadata.reelId" } } }

When a join returns nothing, check the types before you check the logic:

db.view_events.findOne().metadata.reelId    // ObjectId('...') or '...' ?
db.reels.findOne()._id                      // compare

$unwind after a join

Because as is an array, most joins are followed by an $unwind to flatten it back to an object:

{ $unwind: "$reel" }                                                   // drops non-matches
{ $unwind: { path: "$reel", preserveNullAndEmptyArrays: true } }       // keeps them

The default discards documents whose array is empty, which turns your left outer join into an inner join. That is often what you want — a view event whose reel was deleted has nothing useful to report — but it should be a decision, not a surprise. It is also why a type mismatch produces zero rows rather than rows full of nulls.

Order matters more than anything else

A join is the most expensive stage in most pipelines, so the rule is: reduce the stream before you join, never after.

ReelCMS’s top-reels report aggregates thirty thousand view events down to eight rows, and only then joins:

var agg = newAggregation(
        match(Criteria.where("ts").gte(Date.from(from))),
        group("metadata.reelId").count().as("views").avg("watchSeconds").as("avgWatch"),
        sort(Sort.Direction.DESC, "views"),
        limit(limit),
        lookup("reels", "_id", "_id", "reel"),
        unwind("reel", false));

Read the stage order: $match narrows by date and can use an index, $group collapses thirty thousand events into one row per reel, $limit cuts that to eight, and then eight lookups run. Move the $lookup to the top and it joins every event ever recorded before throwing almost all of it away.

Notice the localField in that call is _id, not metadata.reelId. After a $group, the grouping key lives in _id — the original field name is gone.

The sub-pipeline form

The four-field $lookup can only express “this field equals that field”. For anything else — a join with an extra condition, or one that pre-filters the other collection — there is a second form that takes a whole pipeline:

db.reels.aggregate([
  { $lookup: {
      from: "comments",
      let:  { reelId: "$_id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$reelId", "$$reelId"] } } },
        { $match: { likes: { $gt: 10 } } },
        { $sort:  { createdAt: -1 } },
        { $limit: 3 }
      ],
      as: "topComments"
  } }
])

Three pieces of syntax there. let declares variables from the outer document; inside the sub-pipeline they are referenced with two dollar signs ($$reelId) to distinguish them from the inner collection’s own fields. And the join condition has to go inside $expr, because comparing two fields is not something an ordinary filter can do.

This form is how you fetch “the three most-liked comments per reel” in one query rather than one query per reel. It is more expensive than the simple form — the sub-pipeline runs once per input document — so the advice about reducing the stream first applies twice over.

A dashboard usually needs four unrelated figures at once. $facet runs several pipelines over the same input and returns all their results in a single document, which means one round trip instead of four:

db.reels.aggregate([
  { $match: { status: "PUBLISHED" } },
  { $facet: {
      totals:     [ { $group: { _id: null, views: { $sum: "$stats.views" } } } ],
      byStatus:   [ { $sortByCount: "$status" } ],
      topByLikes: [ { $sort: { "stats.likes": -1 } }, { $limit: 5 },
                    { $project: { title: 1, "stats.likes": 1 } } ]
  } }
])

Each sub-pipeline sees the same documents the $facet received, and they do not affect one another. The catch: only the stages before the $facet can use an index, so put your $match above it and keep the input small.

$dateTrunc — grouping by day

Time-series reports group by a truncated date. The older idiom formats the date into a string with $dateToString and groups on that, which works but produces a string key — so the following sort is lexicographic and anything downstream has to re-parse it. $dateTrunc keeps it a real date:

db.view_events.aggregate([
  { $match: { ts: { $gte: from } } },
  { $group: { _id: { $dateTrunc: { date: "$ts", unit: "day" } }, views: { $sum: 1 } } },
  { $sort: { _id: 1 } }
])

unit takes "hour", "day", "week", "month" and more, and binSize lets you bucket by, say, fifteen minutes. Add timezone if “a day” means a day where your users are rather than in UTC — a report that shifts by a few hours is usually this.

When a join means you modelled it wrong

One $lookup in a reporting pipeline is fine. A $lookup on your hottest read path is a signal. If every feed request has to join creators to reels, the creator’s display fields probably belong on the reel — which is exactly the denormalization from lesson 6.

The rule of thumb: joins belong in reports, which run occasionally and tolerate latency. On the path that serves every page view, model the join away instead.

The short version

as is always an array; $unwind flattens it and drops non-matches by default.
Types must match exactly. String versus ObjectId is the classic silent failure.
Reduce before you join. $match, $group, $limit, then $lookup.
After $group, the key is in _id — join on that.
$facet gives a whole dashboard in one round trip; index use stops at its boundary.
$dateTrunc beats $dateToString because it keeps a real date.
A join on a hot path is usually a modelling decision waiting to be made.