Find number of objects in nested array with different query params

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);

1 answer

  • answered 2021-11-29 03:57 Yong Shun

    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

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum