What does MongoDB's $lookup equality form return for each input document?
answer
- It is a join, but not a relational one
- Think about which side keeps its rows
- Look at the type of the output field
- Non-matching input documents are not lost
- One array field per input document
basics
~20 s$lookup is a left outer join: for each input document it adds an array field, named by as, holding every document from the from collection whose foreignField equals the input's localField. No match yields an empty array, never a dropped document.
solid answer
~40 sThe equality form takes four fields: `from` (a collection in the **same database**), `localField` (a field on the incoming document), `foreignField` (a field on the joined documents), and `as` (the output field name). For every input document, MongoDB finds all documents in `from` whose `foreignField` equals the input's `localField` and attaches them as an **array** under `as`. It is a left outer join, so input documents that match nothing still come through, with an empty array. Two details bite people: if `as` names a field that already exists, it is overwritten; and a missing `localField` is treated as `null` for matching, so it can pull back foreign documents whose `foreignField` is null or absent. Matching is exact BSON equality — an ObjectId never matches its string form.
code
javascript · 9 linesdb.orders.aggregate([
{ $match: { status: "shipped" } },
{ $lookup: {
from: "customers",
localField: "customerId",
foreignField: "_id",
as: "customer"
} }
])go deeper
Memorize the four fields — from, localField, foreignField, as — and that the result is an array attached to each input document, empty when nothing matched.
Be ready to explain the left-outer semantics and the consequences: array output needs flattening, missing local fields match as null, and the foreign field needs an index for the join to be cheap.
Interviewers expect you to reason about where the join sits in the pipeline — filtering the left side before the stage, trimming the right side, and spotting when repeated $lookup on a hot path is really a data-model problem.
Own the position that ad-hoc joins are a reporting affordance, not an architecture. Be able to argue when a service should keep joining at query time and when the read pattern justifies changing how the data is stored.
## What the stage is `$lookup` is the aggregation pipeline's join stage. Its simplest shape — the *equality form* — joins the collection the aggregation is running on (the "left" side) to a second collection in the same database (the "right" or foreign side) on a single equality condition. ```js db.orders.aggregate([ { $lookup: { from: "customers", localField: "customerId", foreignField: "_id", as: "customer" } } ]) ``` ## The four fields - **`from`** — the name of the collection to join with. For a standard `$lookup` this collection must be in the **same database** as the one being aggregated. - **`localField`** — the field on each incoming document whose value is matched. - **`foreignField`** — the field on documents in `from` that the local value is compared against. - **`as`** — the name of the new field added to each output document. ## The output is always an array This is the single most important shape fact. Even a strictly one-to-one relationship comes back as a one-element array, because `$lookup` has no way to know the relationship is unique. So after joining an order to its customer you have `customer: [ { ... } ]`, and downstream stages must reach through the array — with `$arrayElemAt`, `$first`, or an `$unwind` — before they can read `customer.name` the way a relational result set would let you. ## Left outer semantics `$lookup` never removes an input document. A document with no match gets `as` set to `[]`. That is what makes it a *left outer* join, and it is why adding an `$unwind` on the `as` field afterwards silently converts the join to an inner one: unwinding an empty array produces zero output documents for that input. ## Matching rules that surprise people - **Missing local field.** If the input document has no `localField`, `$lookup` treats the value as `null` for matching purposes. That means it can match foreign documents whose `foreignField` is `null` or missing — a common source of "why did this junk document join onto everything". - **Array-valued fields.** If `localField` (or `foreignField`) holds an array, the match succeeds when *any* element is equal to the other side. That makes the equality form usable for many-to-many links stored as an array of ids, without any extra stage. - **No type coercion.** BSON equality is exact. An `ObjectId("652f...")` in `customerId` will never match the string `"652f..."` in `_id`. If a join mysteriously returns empty arrays for every document, check the BSON types of both sides first. - **`as` overwrites.** Naming the output field the same as an existing field replaces that field's value with the joined array. Writing `as: "customerId"` destroys the very key you joined on. ## Performance shape The foreign side is looked up per input document, so the field named by `foreignField` should be indexed — joining on `_id` is free because that index always exists; joining on any other field without an index means a collection scan on the foreign collection for every document flowing through the stage. Two habits follow: 1. **Filter first.** Put `$match` (and `$limit`, if the query allows) *before* `$lookup` so the join runs over the smallest possible left side. 2. **Trim the right side.** The equality form returns whole foreign documents. If you only need two fields, the pipeline form of `$lookup` lets you `$project` inside the join instead of dragging entire documents through the pipeline. Remember that the joined array lives inside the output document, so the result must still respect the 16 MB BSON document limit. Joining a collection where one key has hundreds of thousands of matches will blow past it. ## When it is the wrong tool `$lookup` is for the joins you did not anticipate when designing the schema — reporting, ad-hoc analytics, occasional enrichment. If a join is on the hot path of every read, that is a signal about the document model rather than about the stage: the data that is always read together is usually the data that should have been stored together.
- Your $lookup returns an empty array for every document even though the reference values look identical. What do you check first?BSON types on both sides. `$lookup` uses exact equality with no coercion, so an `ObjectId` in `localField` will never match the same hex digits stored as a string in `foreignField`. Compare one document from each collection with `$type` before touching the pipeline. The second thing to check is that `from` names a collection in the same database, since a wrong or non-existent name yields empty arrays rather than an error.
- How do you turn a one-to-one $lookup result into an embedded object rather than a one-element array?Follow the stage with `$set` using `$first` (or `$arrayElemAt` with index 0) on the `as` field: `{ $set: { customer: { $first: "$customer" } } }`. That keeps left-outer semantics — unmatched documents get `null` rather than being dropped. An `$unwind` also flattens it, but discards unmatched documents unless you pass `preserveNullAndEmptyArrays: true`.
- What index would you create to make a $lookup on a non-_id foreign field efficient?A single-field index on the collection named by `from`, over the field named by `foreignField` — the lookup runs against the foreign side once per input document, so an unindexed `foreignField` means a full scan of that collection per document. Joining on `_id` needs nothing extra, because that index always exists.
saying these in an interview costs you the question
- Says $lookup returns an object, not an array
- Thinks unmatched input documents are dropped
- Assumes an ObjectId matches its string form
- Believes $lookup can join across databases
- Indexes localField instead of the foreign field