How to add condition in mongodb aggregate lookup?

Question

I have two collections university and college.

Under one university multiple colleges are there. I want to get the university and college detail according to below condition: 1.First get the university which have maximum no of colleges under it.

2.And then get the college with maximum number of students.

(It doesn't matter if a university have less college and more TotalStudent. Condition will be always pass point 1 first and then 2)

So far tried

University collection individual data

{
    _id: new ObjectId("62f5557f6c96453a1e972fe8"),      
    UniversityId: 1,
    Name: 'Tigor University',
    State: 'Delhi',
    PhoneNumber: 9856897895
}

College Collection individual data

{
    "_id": "62f563d16aeb1d12cddca85a",
    "ClgId": 3,
    "UniversityId": 5,
    "Name": "Renuka College Of Science",
    "Email": "[email protected]",
    "PhoneNumber": 9999663302,
    "TotalStudent": 8900
}

API

router.get('/universityDetail', async (req,res)=>{
    let universityDetail = await universityModel.aggregate([
        { $lookup:
            {
                from:'colleges',
                localField:'UniversityId',
                foreignField:'UniversityId',
                as:'collegedetail'
            }
        }
    ]);

    res.send(universityDetail);
})

Response

[
    {
        "_id": "62f5557f6c96453a1e972fe8",
        "UniversityId": 1,
        "Name": "Tigor University",
        "State": "Delhi",
        "PhoneNumber": 9856897895,
        "collegedetail": []
    },
    {
        "_id": "62f5557f6c96453a1e972fe8",
        "UniversityId": 2,
        "Name": "Royal University",
        "State": "Pune",
        "PhoneNumber": 8585858585,
        "collegedetail": []
    },
    {
        "_id": "62f5557f6c96453a1e972fe8",
        "UniversityId": 3,
        "Name": "Golaknath CH University",
        "State": "Bihar",
        "PhoneNumber": 3356898548,
        "collegedetail": [
            {
               "_id": "62f563d16aeb1d12cddca85a",
                "ClgId": 3,
                "UniversityId": 3,
                "Name": "Renuka College Of Science",
                "Email": "[email protected]",
                "PhoneNumber": 9999663302,
                "TotalStudent": 8900
            }
        ]
    },
    {
       "_id": "62f5557f6c96453a1e972fe8",
        "UniversityId": 4,
        "Name": "Sankalp B University",
        "State": "MP",
        "PhoneNumber": 9856897895,
        "collegedetail": [
            {
                "_id": "62f563d16aeb1d12cddca85a",
                "ClgId": 6,
                "UniversityId": 4,
                "Name": "Jyoti Vidya Ayurveda College",
                "Email": "[email protected]",
                "PhoneNumber": 2359568,
                "TotalStudent": 800
            },
            {
                "_id": "62f563d16aeb1d12cddca85a",
                "ClgId": 9,
                "UniversityId": 4,
                "Name": "Sino Reddy College",
                "Email": "[email protected]",
                "PhoneNumber": 9999663302,
                "TotalStudent": 1200
            }
        ]
    },
    {
        "_id": "62f5557f6c96453a1e972fe8",
        "UniversityId": 5,
        "Name": "Periya",
        "State": "Delhi",
        "PhoneNumber": 9856897895,
        "collegedetail": []
    }
]

Expected Result

{
    "_id": "62f5557f6c96453a1e972fe8",
     "UniversityId": 4,
     "Name": "Sankalp B University",
     "State": "MP",
     "PhoneNumber": 9856897895,
     "collegedetail": [
         {
             "_id": "62f563d16aeb1d12cddca85a",
             "ClgId": 9,
             "UniversityId": 4,
             "Name": "Sino Reddy College",
             "Email": "[email protected]",
             "PhoneNumber": 9999663302,
             "TotalStudent": 1200
         }
     ]
 }

Answer 1

Here i applied two query to get the result. First aggregate i got the UniversityId which have maximum colleges registered under it and have highest students. Then used the UniversityId in the second aggregate to get the result.

let university = await universityModel.aggregate([
        { $group: 
            { _id: "$UniversityId", UniversityId_TotalCount: { $sum: 1 }, 
            sum_TotalStudent:{
                $sum:{ "$toInt":"$TotalStudent" }
            } 
            } 
        },
        { $sort: {UniversityId_TotalCount:-1, sum_TotalStudent:-1} },
        {
            "$limit":1
        }
    ]);
    
    let universityObj = university[0];
    const university_id = Object.values(universityObj)[0];

    let result = await clgModel.aggregate([
        { "$match": { "UniversityId": university_id } },
        { $lookup:
            {
                from:'colleges',
                localField:'UniversityId',
                foreignField:'UniversityId',
                as:'maxStudent_college',

                "pipeline": [
                    { $sort: 
                        { "maxStudent_college.TotalStudent": -1 }
                    },
                    { $limit: 1 }
                ],
            }
        },
        { $project:
            {  _id:0, Name:1, "maxStudent_college.Name":1, "maxStudent_college.TotalStudent":1 }
        },
    ]);

    res.send(result);
    
    **Final response**
    
[
    {
        "Name": "Sankalp B University",
        "maxStudent_college": [
            {
                "Name": "Sino Reddy College",
                "TotalStudent": 1200
            }
        ]
    }
]