db={"courses":[{"_id":{"$oid":"62aab6669b3740313d881a30"},"course_name":"Master","fees":"Rs.1000.0/-","course_specialization":[{"spec_name":"Social Work","is_activated":true}],"college_id":{"$oid":"628dfd41ef796e8f757a5c13"},"is_pg":true},{"_id":{"$oid":"62aab6669b3740313d881a38"},"college_id":{"$oid":"628dfd41ef796e8f757a5c13"},"course_name":"BBA","fees":"Rs.1000.0/-","is_pg":false,"course_specialization":null},{"_id":{"$oid":"628f3967cb69fc0789e69181"},"course_name":"BTech","fees":"Rs.1000.0/-","course_specialization":[{"spec_name":"Computer Science and Engineering","is_activated":true},{"spec_name":"Mutiple Specs","is_activated":true}],"college_id":{"$oid":"628dfd41ef796e8f757a5c13"},"is_pg":false},{"_id":{"$oid":"628f35a1cb69fc0789e6917e"},"course_name":"Bachelor","fees":"Rs.1000.0/-","course_specialization":[{"spec_name":"Social Work","is_activated":true}],"college_id":{"$oid":"628dfd41ef796e8f757a5c13"},"is_pg":false}],"studentApplicationForms":[{"_id":{"$oid":"62cd476adbc878a0490e20ee"},"spec_name1":"Social Work","spec_name2":"","spec_name3":"","student_id":{"$oid":"62cd1374dbc878a0490e20a5"},"course_id":{"$oid":"62aab6669b3740313d881a30"},"current_stage":2.5,"declaration":true,"payment_info":{"payment_id":"123458","status":"paid"},"enquiry_date":{"$date":{"$numberLong":"1657620330432"}},"last_updated_time":{"$date":{"$numberLong":"1657621796062"}}},{"_id":{"$oid":"62cd476adbc878a0490e20ef"},"spec_name1":"","spec_name2":"","spec_name3":"","student_id":{"$oid":"62cd1374dbc878a0490e20a5"},"course_id":{"$oid":"62aab6669b3740313d881a38"},"current_stage":2.5,"declaration":true,"payment_info":{"payment_id":"123458","status":"paid"},"enquiry_date":{"$date":{"$numberLong":"1657620330432"}},"last_updated_time":{"$date":{"$numberLong":"1657621796062"}}},{"_id":{"$oid":"62cdc12000b820f5ea58cc60"},"spec_name1":"Social Work","spec_name2":"","spec_name3":"","student_id":{"$oid":"62cdad90a9b64d58b15e6976"},"course_id":{"$oid":"628f35a1cb69fc0789e6917e"},"current_stage":6.25,"declaration":false,"payment_info":{"payment_id":"","status":""},"enquiry_date":{"$date":{"$numberLong":"1657651488511"}},"last_updated_time":{"$date":{"$numberLong":"1657651987155"}}}]}

db.courses.aggregate([{$match:{college_id:ObjectId("628dfd41ef796e8f757a5c13")}},{$project:{_id:1,course_name:1,course_specialization:1}},{$unwind:{path:"$course_name",includeArrayIndex:"course_index",preserveNullAndEmptyArrays:true}},{$unwind:{path:"$course_specialization",includeArrayIndex:"course_specs_index",preserveNullAndEmptyArrays:true}},{$lookup:{from:"studentApplicationForms","let":{id:"$_id",spec:"$course_specialization.spec_name"},pipeline:[{$match:{$expr:{$and:[{$eq:["$course_id","$$id"]},{$eq:["$spec_name1","$$spec"]}]}}},{$project:{student_id:1,payment_info:1,spec_name1:1,spec_name2:1,spec_name3:1}}],as:"student_application"}},{$unwind:{path:"$student_application",includeArrayIndex:"application",preserveNullAndEmptyArrays:true}},{$facet:{course:[{$group:{_id:{course_name:"$course_name",spec:"$course_specialization"},count:{$count:{}}}}],declatration:[{$group:{_id:{course_name:"$course_name",spec:"$course_specialization"},count_dec:{$sum:{$cond:["$student_application.declaration",1,0]}}}}],payment:[{$group:{_id:{course_name:"$course_name",spec:"$course_specialization"},payment:{$sum:{$eq:["$student_application.payment_info.status","captured"]}}}}]}}])