MongoDB $lookup on nested document

Question

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!

Answer 1

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.