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!

Possible duplicate of [$lookup on ObjectId's in an array](http://stackoverflow.com/questions/34967482/lookup-on-objectids-in-an-array) — Blakes Seven, Mar 15 '16 at 23:28

score 44 · Accepted Answer · answered Mar 15 '16 at 22:49

44

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.

answered Mar 15 '16 at 22:49

profesor79

9,213
3
31
52

3

incredibly helpful, thanks a lot! I've learned a lot just by going step by step using your answer. However, I've updated my question to clarify that the $items.items can contain multiple schools in that array. I tried your approach and it seems to return documents separately in the event $items.items has multiple docs. I'm assuming this would need to somehow get grouped back. Also, I noticed that if an $items doesn't have a nested $items.items that entire outer document gets skipped. Very appreciative of your help. My first few days using mongo and this scenario is teaching me a lot! Thx – Imran Mar 16 '16 at 01:53
Any update on this? I am facing the same issue you described in your comment. – Philipp Jahoda Dec 14 '16 at 15:33
1

A bit late to the party, but instead of a straight unwind, use the additional subkey preserveNullAndEmptyArrays to prevent empty elements from collapsing. $unwind: { path: "$schoolInfo", preserveNullAndEmptyArrays : true } – Andy Lorenz May 06 '21 at 16:28

score 7 · Answer 2 · edited May 23 '17 at 12:31

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: [],
},

MongoDB $lookup on nested document

2 Answers2

Linked