π Lookups, AKA Left Outer Join
Using documents, we usually model 1:1 and 1:many relationships by embedding documents inside other documents, and even using arrays. For instance, an author can have many aliases, and they live inside an array in the authors
collection.
But other times, we need to use references to those documents instead of embedding them. For instance, an author has an array of the books they have written, but instead of moving the book documents inside an array inside author (which will be tricky for books with multiple authors), we embed the books _id
instead.
So how can we get the author and all the books they have written embedded in the array? By using $lookup
! That will do a left outer join and return author docs containing book docs inside.
π Run this aggregation and look at the results:
- Atlas UI
- MongoDB Shell
[
{$lookup: {
from: "books",
localField: "books",
foreignField: "_id",
as: "booksWritten"
}
},
{$project: {_id: 0}}
]
db.authors.aggregate([
{$lookup: {
from: "books",
localField: "books",
foreignField: "_id",
as: "booksWritten"
}
},
{$project: {_id: 0}}
])
The syntax for this version of $lookup
is:
{
$lookup:
{
from: <collection to join>,
localField: <field from the input documents>,
foreignField: <field from the documents of the "from" collection>,
as: <output array field>
}
}
Lookups from a previous stageβ
We can do a $lookup on the result of another pipeline, not only joining with a collection. For instance, we want to remove some noise from the books before joining, so we use $project
to exclude a couple of arrays.
- Atlas UI
- MongoDB Shell
[
{$lookup: {
from: "books",
localField: "books",
foreignField: "_id",
pipeline: [
{$project: {title: 1, synopsis: 1}}
],
as: "booksWritten"
}
}
]
db.authors.aggregate([
{$lookup: {
from: "books",
localField: "books",
foreignField: "_id",
pipeline: [
{$project: {title: 1, synopsis: 1}}
],
as: "booksWritten"
}
}
])
The nice part is that we can extract that pipeline and test it/tweak it. (This will only work in the MongoDB Shell.)
- MongoDB Shell
let justShowTitleSynopsis = [
{$project: {title: 1, synopsis: 1}},
]
db.authors.aggregate([
{$lookup: {
from: "books",
localField: "books",
foreignField: "_id",
pipeline:
justShowTitleSynopsis,
as: "booksWritten"
}
}
])