MongoDB Aggregation Framework complex query for parent child relationship

Question

I am new to mongodb so I hope this does not come-off as a very basic question. I've tried to apply what I've found but somehow I am missing some part and need help:

I have two parent-child collections in the following format:

Collection Name : members (Parent Records)

{
    {
        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"
    }
}

Collection Name : memberActivities (Child Records)

{
    {
        recordNo: 101, //Reference to members.recordNo in above collection
        activityDate: '05-Jan-2024',
        activity:[ //Array
            0:{ Comments: "Played Chess" }
            1:{ Comments: "Played Hockey" }
            2:{ Comments: "Played Cricket" }
        ]       
    },
    {
        recordNo: 101,
        activityDate: '06-Jan-2024',
        activity:[ //Array
            0:{ Comments: "Outing with friends" }
        ]       
    },
    {
        recordNo: 102,
        activityDate: '08-Feb-2024',
        activity:[ //Array
            0:{ Comments: "Played Basketball" }
        ]       
    },
    {
        recordNo: 103,
        activityDate: '20-Sep-2023',
        activity:[ //Array
            0:{ Comments: "Played Cricket" }
        ]       
    }
}

How should I form the "Aggregation Query" in MongoDB to get following output:

1. Get "count" of all the "members" who is registered between '01-Jan-2024' to '29-Feb-2024' and played "Cricket" atleast once.
2. Get "count" of all the "members" who is registered between '01-Jan-2024' to '29-Feb-2024' and never played "Cricket".

I am executing script in MongoDB compass for my queries.

Answer 1

First of all, if you want to filter by dates, you should set those fields as date types, not strings. Examples.

Then you can use $lookup to join the two collections, and $match to filter within the array to include or exclude the activities.

Example include

Example exclude

Answer 2

Made this solution on mongoplayground : https://mongoplayground.net/p/Q6p8JzfxVLG

It looks like this :

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
    }
  }
])

1. First $match the document to filter the regiisterdDate you want.
2. Then $lookup for activities where Cricket has been played.
3. Finally $group the output to count the number of members that match your conditions.
4. Optionnally $project because we don't like nested _id with only one object.