I'm new to mongo and struggling mightily with the following. I have 2 collections structured as below. For the life of me, I can't figure out how to do a $lookup on the school collection. Reading other posts, I'm definitely using ObjectId for the reference as well as the foreign field.
Below is my structure:
Alumni:
{
"_id": "john",
"items": [
{
"name": "John",
"items": [
{
"school": ObjectId("56de35ab520fc05b2fa3d5e4"),
"grad": true
},
{
"school": ObjectId("56de35ab520fc05b2fa00000"),
"grad": false
}
]
},
{
"name": "Johnny"
// notice no nested items, this doc should still be included in result
},
{
"name": "Jon",
"items": [
{
"school": ObjectId("56de35ab520fc05b2fa11111"),
"grad": false
}
]
}
]
}
Schools
{
_id: ObjectId("56de35ab520fc05b2fa3d5e4"),
name: "Some University",
street: "ABC Boulevard"
}
What I'm looking to get:
{
"_id": "john",
"items": [
{
"name": "John",
"items": [
{
"school": ObjectId("56de35ab520fc05b2fa3d5e4"),
"grad": true,
"schoolInfo": {
_id: ObjectId("56de35ab520fc05b2fa3d5e4"),
name: "Some University",
street: "ABC Boulevard"
}
},
{
"school": ObjectId("56de35ab520fc05b2fa00000"),
"grad": true,
"schoolInfo": {
_id: ObjectId("56de35ab520fc05b2fa00000"),
name: "Another University",
street: "123 Boulevard"
}
}
]
},
{
name: "Johnny"
},
{
"name": "Jon",
"items": [
{
"school": ObjectId("56de35ab520fc05b2fa11111"),
"grad": true,
"schoolInfo": {
_id: ObjectId("56de35ab520fc05b2fa11111"),
name: "Some University",
street: "ABC Boulevard"
}
}
]
}
]
}
The query I've tried to no avail:
db.alumni.aggregate([
{$match: {_id: 'john'}},
{$lookup: {
from: 'schools',
localField: 'items.items.school',
foreignField: '_id',
as: 'schoolInfo'}}
])
Any help would be greatly appreciated!