For long time MongoDB didn’t have any means to join collections, but since version 3.2 there is a way to do simple left outer join of two collections.
MongoDB collections to join
Let’s say that we’ve got the following two MongoDB collections on which we would like to perform JOIN (like in SQL).
db.highscores.find()
{ "_id" : 1, "name" : "QES", "score" : 600 }
{ "_id" : 2, "name" : "FPG", "score" : 214 }
{ "_id" : 3, "name" : "RHS", "score" : 453 }
db.players.find()
{ "_id" : 5, "name" : "QES", "email" : "qes@example.com" }
{ "_id" : 6, "name" : "FPG", "email" : "fpg@example.com" }
{ "_id" : 7, "name" : "RHS", "email" : "rhs@example.com" }
In our case we’ll use the name field as the join field.
MongoDB join collections
Since MongoDB we can use $lookup aggregation operator to perform left outer join on a single field. The syntax is simple and boils down to providing:
- collection to join using from field,
- localField, which should be used in equality match,
- foreignField, to indicate the field from the other collection,
- what should be the name of joined documents inside the result.
Let’s see an example. We are looking for a player with the name “QES” and want to join his data from the players collection:
db.highscores.aggregate([
{$match: {name: "QES"}},
{$lookup: {
from: "players",
localField: "name",
foreignField: "name",
as: "player"
}}
])
The above aggregation pipeline joins highscores and players collections and produces the following document:
{
"_id" : 1,
"name" : "QES",
"score" : 600,
"player" : [
{ "_id" : 5, "name" : "QES", "email" : "qes@example.com" }
]
}
Notice that the joined data from players collection is inside an array. This is because more than one document may match. When there would be no matching documents, the array would be empty.
