Mongodb aggregate lookup for many collections to one nested output

Question

I've 3 Collections:

School

{   "id" : { "$numberLong" : "100000" },         
    "name" : "School1" }

Faculty

{   "id" : { "$numberLong" : "100000" }, 
    "schoolId" : { "$numberLong" : "100000" }, 
    "name" : "Faculty1" }

Subject

{   "id" : { "$numberLong" : "100000" },       
    "name" : "Subject1" }

Assume there are many of these in each collection. I want to be able to serve an endpoint that takes in an ID and returns the full 3 layered heirarchy (School->Faculty->Subject). How would I return all this data.

Something like:

{
    id: 1,
    name: "school1", 
    faculties: [{
        id:1000, 
        name: "faculty1", 
        subjects: [
            {id: 1, name: "sub1"},
            {id: 2, name: "sub2"},
            {id: 3, name: "sub3"}
        ]
    }]
}

Kind of been answered before. [$lookup multiple levels without $unwind?](https://stackoverflow.com/a/49955049/2313887) as just one example, and with some modern twists. — Neil Lunn, May 04 '18 at 04:50

score 3 · Accepted Answer · answered May 04 '18 at 04:45

Ok after ages i actually got the solution, which is a lot simpler than the rabbit hole i'd gone down.

{ $match: {id: 100001}},
{ $lookup:
    {
        from: 'faculties',
        localField: 'id',
        foreignField: 'schoolId',
        as: 'faculties',
    }
},
{ $unwind: {
    path: "$faculties",
    preserveNullAndEmptyArrays: true
  }
},
{ $lookup:
    {
        from: 'subjects',
        localField: 'faculties.id',
        foreignField: 'facultyId',
        as: 'faculties.subjects',
    }
}

Which returns the exact output i wanted. The key is the final lookup returning as: 'faculties.subjects' which puts subjects inside faculties which is the first child of schools.

If you need further nesting, you just need to go as: faculties.subjects.students.names for instance each time you get deeper

score 2 · Answer 2 · answered May 04 '18 at 08:05

db.School.aggregate(

    // Pipeline
    [
        // Stage 1
        {
            $lookup: // Equality Match
            {
                from: "Faculty",
                localField: "id",
                foreignField: "schoolId",
                as: "faculties"
            }


        },

        // Stage 2
        {
            $unwind: {
                path: "$faculties",
                preserveNullAndEmptyArrays: false // optional
            }
        },

        // Stage 3
        {
            $lookup: // Equality Match
            {
                from: "Subject",
                localField: "faculties.id",
                foreignField: "facultyId",
                as: "faculties.subjects"
            }


        },

        // Stage 4
        {
            $group: {
                _id: {
                    id: '$id',
                    name: '$name'
                },
                faculties: {
                    $addToSet: '$faculties'
                }
            }
        },

        // Stage 5
        {
            $project: {
                id: '$_id.id',
                name: '$_id.name',
                faculties: 1
            }
        },

    ]



);

Mongodb aggregate lookup for many collections to one nested output

2 Answers2