How to join multiple collections in MongoDB 3.4?

Question

please help me to join multiple collections in MongoDB 3.4

school_collection:

{
    "_id" : ObjectId("5f9934b952255731e8dbfa17"),
    class:[]
}

class_collection:

{
    "_id" : ObjectId("5f9a4398e146b6327457967b"),
    "school_id" : ObjectId("5f9934b952255731e8dbfa17"),
    "name" : "",
    "description" : ""
    students:[]
}

students_collection:

{
    "_id" : ObjectId("5f9a4398e146b6327457967c"),
    "class_id" : ObjectId("5f9a4398e146b6327457967b"),
    "name" : "",
    "description" : ""
}

expected result:

{
    "_id" : "5f9934b952255731e8dbfa17",
        class: [
            {
                "_id": "5f9a4398e146b6327457967b",
                "name": "",
                "description": ""
         students: [
                    {
                      "_id" : "5f9a4398e146b6327457967c",
                      "name" : "",
                      "description" : ""
                    }
              ]
            }
        ]
}

I was able to get collection up to class by using $lookup, but how can I get students details array inside each class?

dbConnection.db().collection(db_collections.SCHOOL).aggregate([
{
    $lookup: {
        from: db_collections.CLASS,
        localField: "_id",
        foreignField: "class_id",
        as: "class"
    }
}])

Answer 1

MongoDB 3.4

• $lookup join class collection
• $lookup join students collection
• $addFields to update class field, $map to iterate loop of class array and get students from students array, $filter to get matching students and return in students field

db.school.aggregate([
  {
    $lookup: {
      from: "class",
      localField: "_id",
      foreignField: "school_id",
      as: "class"
    }
  },
  {
    $lookup: {
      from: "students",
      localField: "class._id",
      foreignField: "class_id",
      as: "students"
    }
  },
  {
    $addFields: {
      class: {
        $map: {
          input: "$class",
          as: "c",
          in: {
            _id: "$$c._id",
            school_id: "$$c.school_id",
            name: "$$c.name",
            description: "$$c.description",
            students: {
              $filter: {
                input: "$students",
                cond: { $eq: ["$$c._id", "$$this.class_id"] }
              }
            }
          }
        }
      }
    }
  },
  { $project: { students: 0 } }
])

Playground