I have this Event array but can't figure out how to query into the 'guests' nested array and do two things.

1. count the number of guests

2. count the number of attended guests (marked 'Y')

   { _id: new ObjectId('1'),
     name: event1,
     guests: [
       {
         phone:  12222222222,
         _id: new ObjectId,
         attended: 'Y'
       },
       {
         phone:  12344466666,
         _id: new ObjectId,
         attended: 'Y'
       },
       { phone:  11234567890, 
         _id: new ObjectId,
         attended: 
       },
       {
         phone:  14443332222,
         _id: new ObjectId,
         attended: 'Y'
       },
       { phone:  19090909090, 
         _id: new ObjectId
         attended:
       }
     ],
   },
   
   { _id: new ObjectId('2'),
     name: event2,
     guests: [
       {
         phone:  11111111111,
         _id: new ObjectId,
         attended:
       },
       {
         phone:  12222222222,
         _id: new ObjectId,
         attended: 'Y'
       },
       { 
         phone:  133333333333, 
         _id: new ObjectId,
         attended: 'Y'
       }
     ],
   },
   

My code below is on its 20th iteration without getting any closer.

const event = await Event.findById(req.params.id);

CodePudding user response：

For MongoDB query, you can use $size for calculating the size of the array field and $filter to filter specific document(s) that matched the criteria in the projection.

db.collection.find({
  _id: "1"
},
{
  _id: 1,
  name: 1,
  guests: 1,
  totalGuests: {
    $size: "$guests"
  },
  attendedGuests: {
    $size: {
      "$filter": {
        input: "$guests",
        cond: {
          $eq: [
            "$$this.attended",
            "Y"
          ]
        }
      }
    }
  }
})

Sample Mongo Playground