MongoDB: determining if there are any objects with a specific count for a particular sub-array value

Question

Essentially, I have:

• a bands collection
• each band may or may not have an array of players
• each player has an array of roles (string)

E.g.:

// a band
{
  _id: 1,
  players: [
    { 
      name: "George",
      roles: [ "GUITAR" ]
    },
    { 
      name: "John",
      roles: [ "SINGER", "GUITAR" ]
    },
    { 
      name: "Paul",
      roles: [ "SINGER", "BASS GUITAR" ]
    },
    { 
      name: "Ringo",
      roles: [ "DRUMS" ]
    },
  ]
}

I need to determine if there is any band which contains more than one member having the SINGER role.

Answer 1

This query seems to give me what I need:

db.bands.aggregate([
  {
    $addFields: {
      players: {
        $ifNull: [
          "$players",
          []
        ]
      }
    }
  },
  {
    "$match": {
      "$expr": {
        "$gt": [
          {
            "$size": {
              "$filter": {
                "input": "$players",
                "as": "player",
                "cond": {
                  "$in": [
                    "SINGER",
                    "$$player.roles"
                  ]
                }
              }
            }
          },
          1
        ]
      }
    }
  }
])

Answer 2

An alternative to the $unwind / $group solution would be $filter:

db.collection.aggregate([
  {
    $match: {
      "players.roles": "GUITAR"
    }
  },
  {
    "$set": {
      "member_cnt": {
        $size: {
          $filter: {
            input: "$players",
            cond: {
              $in: [
                "GUITAR",
                "$$this.roles"
              ]
            }
          }
        }
      }
    }
  },
  {
    $match: {
      "member_cnt": {
        $gt: 1
      }
    }
  },
  {
    "$project": {
      member_cnt: 0
    }
  }
])

It should be a bit faster as it doesn't have blocking $group stage.

Answer 3

Some easy option:

db.collection.aggregate([
 {
   $unwind: "$players"
  },
 {
   $unwind: "$players.roles"
 },
 {
   $match: {
     "players.roles": "SINGER"
  }
  },
   {
   $group: {
    _id: "$_id",
    cnt: {
    $sum: 1
    }
   }
  },
  {
    $match: {
     cnt: {
      $gt: 1
      }
    }
  }
 ])

explained:

1. unwind 1st array
2. unwind 2nd array
3. Filter only the roles SINGER
4. group by band _id and count the SINGER roles.
5. Filter only the bands with >1 SINGER

playground