The syntax for joins that bring back multiple documents from foreign collections is very straightforward and yields exactly what one would expect, but simple joins that are bread and butter in SQL require very convoluted and expensive to run syntax.
Consider a product database that has `products`, `categories` and `reviews` collections. Each product has a unique category and may have multiple reviews. Getting all reviews in an aggregation is very straightforward (top stage), but getting categories, similar to SQL, is as convoluted as it gets (bottom stage).
```
db.products.aggregate(
[
//
// Document aggregates naturally aggregate foreign documents
// into the primary document.
//
{$lookup: {
from: "reviews",
localField: "_id",
foreignField: "product_id",
as: "reviews"
}},
//
// A simple join that is supposed to bring a couple of fields
// into the primary document translates into this convoluted
// syntax just to get those two fields.
//
{$lookup: {
from: "categories",
let: {category_id: "$category"},
pipeline: [
{$match: {
$expr: {$eq: ["$_id", "$$category_id"]}
}},
{$project: {
_id: 0,
cat_name: "$category",
cat_description: "$description"
}}
],
as: "category_details"
}},
{$replaceRoot: {
newRoot: {
$mergeObjects: [
"$$ROOT", {$arrayElemAt: ["$category_details", 0]}
]
}
}},
{$project: {
category_details: 0
}}
])
```
Notice that keeping `category.category` and `category.description` in `products`, the noSQL-way, isn't an option because updating 10000's of products when category or description change is just not a good idea any way one looks at it. Denormalization is useful in many noSQL scenarios, but this isn't one of them.
Having some simple join syntax that would identify the joined collection as a 1-to-1 join and would bring field names and values as if they existed in the primary document, possibly aliased via a projection-like syntax, would be extremely helpful in keeping aggregations readable, keeping data reasonably normalized, and would run much faster because it would be just multiple parallel BSON `append` calls when constructing aggregation results documents.
Data for the query:
products:
```
{"_id":{"$oid":"6140e6c6dfe7b9d6e597d07e"},"name":"Product A","category":1,"price":111}
{"_id":{"$oid":"6140e6c6dfe7b9d6e597d07f"},"name":"Product B","category":2,"price":222}
{"_id":{"$oid":"6140e6c6dfe7b9d6e597d080"},"name":"Product C","category":1,"price":333}
```
categories:
```
{"_id":1,"category":"Clothing","description":"All kinds of clothing"}
{"_id":2,"category":"Electronics","description":"Cool gadgets"}
```
reviews:
```
{"_id":{"$oid":"6140e7cadfe7b9d6e597d08a"},"product_id":{"$oid":"6140e6c6dfe7b9d6e597d07e"},"review":"Product A is great","rating":4}
{"_id":{"$oid":"6140e813dfe7b9d6e597d090"},"product_id":{"$oid":"6140e6c6dfe7b9d6e597d07f"},"review":"This product is horrible","rating":1}
{"_id":{"$oid":"6140e8eff4ff76778b48ba88"},"product_id":{"$oid":"6140e6c6dfe7b9d6e597d07f"},"review":"This product is not the best, but usable","rating":2}
```
