Mongodb aggregate three collections

Question

Learning MongoDB for the past two days and I am trying to aggregate three collections but unable to achieve it

Below are the four collection maintaining in the database

university

{
   "_id" : "5834ecf7432d92675bde9d82",
   "name": "NIFT"
}

college

{
   "_id" : "5834ecf7432d92675bde9d83",
   "name": "NIFT Hyderabad",
   "university_id":"5834ecf7432d92675bde9d82"
}

departments

{
   "_id" : "5834ecf7432d92675bde9d84",
   "department_name": "Fashion Technology",
   "college_id" : "5834ecf7432d92675bde9d83" 
},
{
   "_id" : "5834ecf7432d92675bde9d85",
   "department_name": "Merchandising",
   "college_id" : "5834ecf7432d92675bde9d83"
}

Sections

{
   "_id" : "5834ecf7432d92675bde9d86",
   "section_name": "A",
   "students" : "56",
   "department_id":"5834ecf7432d92675bde9d84"
},
{
   "_id" : "5834ecf7432d92675bde9d87",
   "section_name": "B",
   "students" : "60",
   "department_id":"5834ecf7432d92675bde9d84"
},
{
   "_id" : "5834ecf7432d92675bde9d86",
   "section_name": "A",
   "students" : "55",
   "department_id":"5834ecf7432d92675bde9d85"
},
{
   "_id" : "5834ecf7432d92675bde9d87",
   "section_name": "B",
   "students" : "44",
   "department_id":"5834ecf7432d92675bde9d85"
}

Here I am trying to achieve the output in the below format

Expected Output

[{
   "_id": "5834ecf7432d92675bde9d83",
   "name": "NIFT Hyderabad",
   "university_id": "5834ecf7432d92675bde9d82",
   "departments": [{
        "_id": "5834ecf7432d92675bde9d84",
        "department_name": "CSE",
        "college_id": "5834ecf7432d92675bde9d83",
        "sections": [{
            "_id": "5834ecf7432d92675bde9d86",
            "section_name": "A",
            "students": "56",
            "department_id": "5834ecf7432d92675bde9d84"
        }, {
            "_id": "5834ecf7432d92675bde9d87",
            "section_name": "B",
            "students": "60",
            "department_id": "5834ecf7432d92675bde9d84"
        }]
    },
    {
        "_id": "5834ecf7432d92675bde9d85",
        "department_name": "Mechanical",
        "college_id": "5834ecf7432d92675bde9d83",
        "sections": [{
                "_id": "5834ecf7432d92675bde9d86",
                "section_name": "A",
                "students": "55",
                "department_id": "5834ecf7432d92675bde9d85"
            },
            {
                "_id": "5834ecf7432d92675bde9d87",
                "section_name": "B",
                "students": "44",
                "department_id": "5834ecf7432d92675bde9d85"
            }
        ]
    }
  ]
}]

But, I am getting department and sections in separate arrays for college but not able to get like in the above format

Query

db.college.aggregate([
            {"$match": { "university_id": "5834ecf7432d92675bde9d82" } },
            {"$lookup": {
            "localField": "_id",
            "from": "departments",
            "foreignField": "college_id",
            "as": "departments"
        }},
        {"$unwind":"$departments"},
        {$group : {_id : "$_id", departments : {$push : "$departments" }}},
        {"$lookup": {
        "localField": "departments._id",
        "from": "sections",
        "foreignField": "department_id",
        "as": "sections"}
        }
        ])

Can any one help me to solve this issue, it will be very helpful for me.

Answer 1

You can try below aggregation query.

The below query pushes the sections into department when they are joined and $group to push department to create the final structure.

db.college.aggregate([
  {
    "$match": {
      "university_id": "5834ecf7432d92675bde9d82"
    }
  },
  {
    "$lookup": {
      "localField": "_id",
      "from": "departments",
      "foreignField": "college_id",
      "as": "departments"
    }
  },
  {
   "$unwind": {
     "path": "$departments",
     "preserveNullAndEmptyArrays": true
    }
  },
  {
    "$lookup": {
      "localField": "departments._id",
      "from": "sections",
      "foreignField": "department_id",
      "as": "departments.sections"
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "name": {
        "$first": "$name"
      },
      "university_id": {
        "$first": "$university_id"
      },
      "departments": {
        "$push": "$departments"
      }
    }
  }
])