db={"members":[{recordNo:101,/**member id*/registerdDate:"01-Jan-2024",personName:"James"},{recordNo:102,registerdDate:"05-Feb-2024",personName:"Smith"},{recordNo:103,registerdDate:"16-Aug-2023",personName:"Alex"}],"memberActivities":[{recordNo:101,/**Reference to members.recordNo in above collection*/activityDate:"05-Jan-2024",activity:[/**Array*/{Comments:"Played Chess"},{Comments:"Played Hockey"},{Comments:"Played Cricket"}]},{recordNo:101,activityDate:"06-Jan-2024",activity:[/**Array*/{Comments:"Outing with friends"}]},{recordNo:102,activityDate:"08-Feb-2024",activity:[/**Array*/{Comments:"Played Basketball"}]},{recordNo:103,activityDate:"20-Sep-2023",activity:[/**Array*/{Comments:"Played Cricket"}]}]}

db.members.aggregate([{$match:{$expr:{$and:[/**find members registered after 1 january 2024*/{"$gte":[{"$dateFromString":{"dateString":"$registerdDate"}},ISODate("2024-01-01")]},/**find members registered before 29 february 2024*/{"$lte":[{"$dateFromString":{"dateString":"$registerdDate"}},ISODate("2024-02-29")]}]}}},/**lookup for activities*/{"$lookup":{"from":"memberActivities","localField":"recordNo","foreignField":"recordNo","as":"matching","pipeline":[/**but keep only the activity if "Played Cricket" in activity comments*/{$match:{"activity.Comments":"Played Cricket"}}]}},/**group the documents having at least one "Played cricket" and others*/{"$group":{"_id":{"PlayedCricketAtLeastOnce":{$gt:[/**if size of lookup is greater than 0, he played at least once*/{"$size":"$matching"},0]}},"count":{"$sum":1}}},/**project for better data*/{"$project":{_id:0,"PlayedCricketAtLeastOnce":"$_id.PlayedCricketAtLeastOnce",count:1}}])