What does the $unionWith stage add to a pipeline, and how does it differ from $lookup?

## Vertical, not horizontal The cleanest way to hold the difference is by what happens to the document count. - `$lookup` keeps the count the same and makes each document **wider** — it adds an array field of matched foreign documents. - `$unionWith` keeps documents the same width and makes the stream **longer** — it appends documents from another collection. So they answer different questions. "Which customer placed this order?" is `$lookup`. "Show me last month's orders together with the archived ones from two years ago" is `$unionWith`. ## Syntax ```js db.orders.aggregate([ { $match: { placedAt: { $gte: ISODate("2024-01-01") } } }, { $unionWith: { coll: "archived_orders", pipeline: [ { $match: { placedAt: { $gte: ISODate("2024-01-01") } } } ] } }, { $group: { _id: "$customerId", total: { $sum: "$amount" } } } ]) ``` There is also a shorthand — `{ $unionWith: "archived_orders" }` — which brings in every document from that collection with no filtering. Use it only when you really do want the whole collection, because the shorthand hides the cost. The optional `pipeline` runs against `coll` before its documents join the stream. It is the right place to put the filter and the projection, so that the incoming side is narrowed at the source rather than after being merged. ## It does not deduplicate This is the property most often assumed wrong. `$unionWith` concatenates; it does not compute a set union. If the same logical record exists in both collections, it appears twice in the output. To collapse duplicates you have to do it explicitly, typically with a `$group` on the identifying key after the union: ```js { $group: { _id: "$_id", doc: { $first: "$$ROOT" } } } ``` Similarly, the resulting order is not defined. Documents from the two sources are not interleaved in any promised way, so if order matters, add an explicit `$sort` after the union rather than relying on what you observe. ## Shape mismatch is your problem The two collections are not required to share a schema, and MongoDB will not complain if they do not. A union of collections whose field names diverge produces a heterogeneous stream, and any downstream `$group` or `$project` will quietly produce nulls for the fields one side is missing. When unioning collections that drifted apart — an archive written by an older version of the application, for instance — normalize the shape inside the union's sub-pipeline with `$project`/`$set` so everything reaching the next stage looks the same. ## Restrictions The sub-pipeline is a real aggregation pipeline but cannot contain the output stages `$out` or `$merge` — writing from inside a union has no coherent meaning. The overall aggregation can still end in `$out` or `$merge`, which is a common pattern: union several sources and materialize the combined result into one collection. ## Where it earns its keep - **Hot/cold split.** Recent data in a small working collection, older data in an archive; one query reads both. - **Per-period or per-tenant collections.** Where data was deliberately partitioned into separate collections, `$unionWith` is how a cross-partition report is assembled. - **Adding a synthetic row.** Unioning against a small collection lets you append totals or reference rows to a result set. - **Migration windows.** While records are moving between two collections, a union keeps reads complete without waiting for the move to finish. It is not, however, a substitute for a data model. If every query has to union five collections, the split that produced those collections is fighting the access pattern — that is a modelling signal, not a stage to optimize.