I have the following MongoDB structure:
Division Collection:
{
"_id": ObjectId("5b28cab902f28e18b863bd36"),
"name": "Premier League",
...
"teams": [
ObjectId("5b28cab902f28e18b863bd01"),
ObjectId("5b28cab902f28e18b863bd02"),
ObjectId("5b28cab902f28e18b863bd03"),
...
]
...
},
...
Teams Collection:
{
"_id": ObjectId("5b28cab902f28e18b863bd01"),
"name": "Liverpool",
...
"players": [
ObjectId('5b23tmb902f28e18b863bd01'),
ObjectId('5b23tmb902f28e18b863bd02'),
ObjectId('5b23tmb902f28e18b863bd03'),
...
]
...
},
...
Players Collection:
{
"_id": ObjectId("5b2b9a8bbda339352cc39ec1"),
"name": "Mohamed Salah",
"nationality": [
ObjectId("5b23cn1902f28e18b863bd01"),
ObjectId("5b23cn2902f28e18b863bd02"),
],
...
},
...
Countries Collection:
{
"_id": ObjectId("5b23cn1902f28e18b863bd01"),
"name": "England",
...
},
{
"_id": ObjectId("5b23cn2902f28e18b863bd02"),
"name": "Egypt",
...
},
...
How to get a result, which is below, using MongoDB aggregation ($lookup
, $pipeline
, etc):
{
"divisions": [
{
"_id": ObjectId("5b28cab902f28e18b863bd36"),
"name": "Premier League",
...
"teams": [
{
"_id": ObjectId("5b28cab902f28e18b863bd01"),
"name": "Liverpool",
...
"players": [
{
"_id": ObjectId("5b23tmb902f28e18b863bd01"),
"name": "Mohamed Salah",
"nationality": [
{
"_id": ObjectId("5b23cn2902f28e18b863bd02"),
"name": "Egypt",
...
},
{
"_id": ObjectId("5b23cn1902f28e18b863bd01"),
"name": "England",
...
}
]
...
},
...
]
},
...
]
},
{
"_id": ObjectId("5b28cab902f28e18b863bd37"),
"name": "Championship",
...
},
...
]
}
I manage to make a first-level merge:
db.divisions.aggregate([
{
$lookup: {
from: 'teams',
localField: 'teams',
foreignField: '_id',
as: 'teams'
}
},
])
and then I ran into difficulties, so I would be very grateful if someone could help me with this issue.
CodePudding user response:
You need multi-level nested $lookup
with pipeline
.
db.division.aggregate([
{
$lookup: {
from: "teams",
let: {
teams: "$teams"
},
pipeline: [
{
$match: {
$expr: {
$in: [
"$_id",
"$$teams"
]
}
}
},
{
$lookup: {
from: "players",
let: {
players: "$players"
},
pipeline: [
{
$match: {
$expr: {
$in: [
"$_id",
"$$players"
]
}
}
},
{
$lookup: {
from: "countries",
localField: "nationality",
foreignField: "_id",
as: "nationality"
}
}
],
as: "players"
}
}
],
as: "teams"
}
}
])
CodePudding user response:
Maybe someone will be useful. Data also can be merged using the populate method:
db.divisions.find(_id: division_id).populate(
{
path: 'teams',
populate: {
path: 'players',
populate: {
path: 'nationality'
}
}
}
)