in this case there is required a nice play with $unwind and $project in aggregation framework
please see below:
db.alumni.aggregate([
{$match: {_id: 'john'}},
{$unwind:"$items"},
{$unwind:"$items.items"},
{$lookup: {
from: 'schools',
localField: 'items.items.school',
foreignField: '_id',
as: 'schoolInfo'}},
{$unwind:"$schoolInfo"},
{$project:{
"_id":1,
"items":[{
"name":"$items.name",
"items":[{
"school":"$schoolInfo._id" ,
"grad":"$items.items.grad" ,
"schoolInfo":"$schoolInfo"
}]
}]
}}
]).pretty()
to see how it works – try removing aggregation stages from query and check document structure.
Would that handle better the multiple schools in the $items.items
array?
db.alumni.aggregate([
{$match: {_id: 'john'}},
{$unwind:"$items"},
{$unwind:"$items.items"},
{$lookup: {
from: 'schools',
localField: 'items.items.school',
foreignField: '_id',
as: 'schoolInfo'}},
{$unwind:"$schoolInfo"},
{$group:{
_id: {
_id: '$_id',
name: '$items.name',
},
items: {
$push: {
'grad': '$items.items.grad',
'school': '$schoolInfo._id'
'schoolInfo': '$schoolInfo'
}
}
}},
{$group:{
_id: '$_id._id',
items: {
$push: {
'name': '$_id.name',
'items': '$items'
}
}
}}
]).pretty()
I didn’t address the case for missing $items.items
but you can look at $unwind empty array
Additionally, it’d be better to leave an empty array instead of nothing when there’s no entry, ie have
{
name: "Johnny",
items: [],
},