db={"courses":[{"_id":{"$oid":"628e03d94e22276b98231407"},"course_name":"BSc","course_description":"BSc","duration":"3 Years","fees":"Rs.1000.0/-","is_activated":true,"banner_image_url":null,"course_specialization":[{"spec_name":"Physician Assistant","is_activated":true},{"spec_name":"Medical Lab Technology","is_activated":true},{"spec_name":"Imaging Technology","is_activated":true},{"spec_name":"Anesthesiology and Operation Technology","is_activated":true},{"spec_name":"Renal Dialysis Technology","is_activated":true},{"spec_name":"Respiratory Technology","is_activated":true},{"spec_name":"Emergency Medical Technology","is_activated":true},{"spec_name":"Optometry Technology","is_activated":true},{"spec_name":"Biomedical Sciences","is_activated":true},{"spec_name":"Genetics and Molecular Biology","is_activated":true},{"spec_name":"Health Psychology","is_activated":true}],"college_id":{"$oid":"628dfd41ef796e8f757a5c13"},"is_pg":false},{"_id":{"$oid":"628f3514cb69fc0789e6917d"},"course_name":"MPH","course_description":"Master Of Public Health","duration":"2 Years","fees":"Rs.1000.0/-","is_activated":true,"banner_image_url":null,"course_specialization":[{"spec_name":"Master Of Public Helth ","is_activated":true}],"college_id":{"$oid":"628dfd41ef796e8f757a5c13"},"is_pg":true},{"_id":{"$oid":"628f35a1cb69fc0789e6917e"},"course_name":"MSc","course_description":"MSc","duration":"2 Years","fees":"Rs.1000.0/-","is_activated":true,"banner_image_url":"null","course_specialization":[{"spec_name":"Clinical Psychology","is_activated":true},{"spec_name":"Health Informatics and Analytics","is_activated":true}],"college_id":{"$oid":"628dfd41ef796e8f757a5c13"},"is_pg":true},{"_id":{"$oid":"628f36a6cb69fc0789e69180"},"course_name":"MBA","course_description":"MBA","duration":"2 Years","fees":"Rs.1000.0/-","is_activated":true,"banner_image_url":null,"course_specialization":[{"spec_name":"General","is_activated":true},{"spec_name":"Hospital & Healthcare Management","is_activated":true}],"college_id":{"$oid":"628dfd41ef796e8f757a5c13"},"is_pg":true},{"_id":{"$oid":"628f39c0cb69fc0789e69183"},"course_name":"Master","course_description":"Master","duration":"2 Years","fees":"Rs.1000.0/-","is_activated":true,"banner_image_url":null,"course_specialization":[{"spec_name":"Social Work","is_activated":true}],"college_id":{"$oid":"628dfd41ef796e8f757a5c13"},"is_pg":true},{"_id":{"$oid":"62aab6669b3740313d881a30"},"college_id":{"$oid":"628dfd41ef796e8f757a5c13"},"course_name":"BBA","course_description":"BBA ","duration":"3 Years","fees":"Rs.1000.0/-","is_activated":true,"banner_image_url":"none","is_pg":false,"course_specialization":null},{"_id":{"$oid":"628f3967cb69fc0789e69181"},"course_name":"BTech","course_description":"BTech","duration":"4 Years","fees":"Rs.1000.0/-","is_activated":true,"banner_image_url":null,"course_specialization":[{"spec_name":"Computer Science and Engineering","is_activated":true}],"college_id":{"$oid":"628dfd41ef796e8f757a5c13"},"is_pg":false},{"_id":{"$oid":"628f39a4cb69fc0789e69182"},"course_name":"Bachelor","course_description":"Bachelor of Social Work","duration":"3 Years","fees":"Rs.1000.0/-","is_activated":true,"banner_image_url":null,"course_specialization":[{"spec_name":"Social Work","is_activated":true}],"college_id":{"$oid":"628dfd41ef796e8f757a5c13"},"is_pg":false}],"studentApplicationForms":[{"_id":{"$oid":"62cea6fd8fa02c4daa0d42de"},"spec_name1":"Emergency Medical Technology","spec_name2":"","spec_name3":"","student_id":{"$oid":"62cea6fd8fa02c4daa0d42dd"},"course_id":{"$oid":"628e03d94e22276b98231407"},"college_id":{"$oid":"628dfd41ef796e8f757a5c13"},"current_stage":5,"declaration":false,"payment_initiated":false,"payment_info":{"payment_id":"","status":""},"enquiry_date":{"$date":{"$numberLong":"1657710333632"}},"last_updated_time":{"$date":{"$numberLong":"1657710440038"}},"allocate_to_counselor":{"counselor_id":{"$oid":"62bf143c41782e1c3f308344"},"counselor_name":"vakado jaysko","last_update":{"$date":{"$numberLong":"1657710334048"}}}},{"_id":{"$oid":"62ceb0008fa02c4daa0d42e4"},"spec_name1":"Renal Dialysis Technology","spec_name2":"","spec_name3":"","student_id":{"$oid":"62ceafff8fa02c4daa0d42e3"},"course_id":{"$oid":"628e03d94e22276b98231407"},"college_id":{"$oid":"628dfd41ef796e8f757a5c13"},"current_stage":5,"declaration":false,"payment_initiated":false,"payment_info":{"payment_id":"","status":""},"enquiry_date":{"$date":{"$numberLong":"1657712639817"}},"last_updated_time":{"$date":{"$numberLong":"1657712728006"}},"allocate_to_counselor":{"counselor_id":{"$oid":"62bfcb0fd7a6a3c9a4e6d32b"},"counselor_name":"Rohan Agrawal","last_update":{"$date":{"$numberLong":"1657712640504"}}}},{"_id":{"$oid":"62ceb772f64432659f5766f7"},"spec_name1":"Optometry Technology","spec_name2":"","spec_name3":"","student_id":{"$oid":"62ceb772f64432659f5766f6"},"course_id":{"$oid":"628e03d94e22276b98231407"},"college_id":{"$oid":"628dfd41ef796e8f757a5c13"},"current_stage":5,"declaration":false,"payment_initiated":false,"payment_info":{"payment_id":"","status":""},"enquiry_date":{"$date":{"$numberLong":"1657714546536"}},"last_updated_time":{"$date":{"$numberLong":"1657715026507"}},"allocate_to_counselor":{"counselor_id":{"$oid":"62bfd13a5ce8a398ad101bd7"},"counselor_name":"viru chaudhary","last_update":{"$date":{"$numberLong":"1657714546852"}}}},{"_id":{"$oid":"62ceb7aaf64432659f5766fb"},"spec_name1":"Social Work","spec_name2":"","spec_name3":"","student_id":{"$oid":"62ce4bea02b81fa74ac241cd"},"course_id":{"$oid":"628f39c0cb69fc0789e69183"},"college_id":{"$oid":"62ceb7aaf64432659f5766fa"},"current_stage":10,"declaration":true,"payment_initiated":true,"payment_info":{"payment_id":"pay_JssVwtD4P8NAIL","status":"captured"},"enquiry_date":{"$date":{"$numberLong":"1657714602407"}},"last_updated_time":{"$date":{"$numberLong":"1657721634664"}},"allocate_to_counselor":{"counselor_id":{"$oid":"62bf143c41782e1c3f308344"},"counselor_name":"vakado jaysko","last_update":{"$date":{"$numberLong":"1657714603163"}}}},{"_id":{"$oid":"62ceb97df64432659f576704"},"spec_name1":"Social Work","spec_name2":"","spec_name3":"","student_id":{"$oid":"62ce4bea02b81fa74ac241cd"},"course_id":{"$oid":"628f39a4cb69fc0789e69182"},"college_id":{"$oid":"62ceb97bf64432659f576703"},"current_stage":2.5,"declaration":false,"payment_initiated":false,"payment_info":{"payment_id":"","status":""},"enquiry_date":{"$date":{"$numberLong":"1657715067732"}},"last_updated_time":{"$date":{"$numberLong":"1657715067732"}},"allocate_to_counselor":{"counselor_id":{"$oid":"62bfcb0fd7a6a3c9a4e6d32b"},"counselor_name":"Rohan Agrawal","last_update":{"$date":{"$numberLong":"1657715069778"}}}},{"_id":{"$oid":"62cec6b2f64432659f576744"},"spec_name1":"Emergency Medical Technology","spec_name2":"","spec_name3":"","student_id":{"$oid":"62cec6b2f64432659f576743"},"course_id":{"$oid":"628e03d94e22276b98231407"},"college_id":{"$oid":"628dfd41ef796e8f757a5c13"},"current_stage":5,"declaration":false,"payment_initiated":false,"payment_info":{"payment_id":"1","status":"paid"},"enquiry_date":{"$date":{"$numberLong":"1657718450094"}},"last_updated_time":{"$date":{"$numberLong":"1657718569200"}},"allocate_to_counselor":{"counselor_id":{"$oid":"62bfd13a5ce8a398ad101bd7"},"counselor_name":"viru chaudhary","last_update":{"$date":{"$numberLong":"1657718450462"}}}}]}

db.courses.aggregate([{$project:{_id:1,course_name:1,course_specialization:1}},{$unwind:{path:"$course_specialization",preserveNullAndEmptyArrays:true}},{$lookup:{from:"studentApplicationForms","let":{courseId:"$_id",spec:{$ifNull:["$course_specialization.spec_name",""]}},pipeline:[{$match:{$expr:{$and:[{$eq:["$spec_name1","$$spec"]},{$eq:["$$courseId","$course_id"]}]}}},{$project:{student_id:1,payment_info:1,spec_name1:1,spec_name2:1,spec_name3:1,declaration:1,}},{$group:{_id:null,count:{$sum:1},declatration:{$sum:{$cond:["$declaration",1,0]}},paid:{$sum:{$cond:[{$eq:["$payment_info.status","paid"]},1,0]}},}}],as:"student_application"}},{$project:{_id:{coursename:"$course_name",spec:"$course_specialization.spec_name",Application_count:{$ifNull:[{$first:"$student_application.count"},0]},Declaration_count:{$ifNull:[{$first:"$student_application.declatration"},0]},Paid_Application_Count:{$ifNull:[{$first:"$student_application.paid"},0]},}}}])