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.

Alex Blex · Accepted Answer · 2022-01-25 23:06:30Z

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.

answered Jan 25, 2022 at 23:06

Alex Blex

37.3k7 gold badges53 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eduardo Sztokbant Over a year ago

This runs much faster than the solution I posted, thank you!

R2D2 · Accepted Answer · 2022-01-25 23:02:22Z

1

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:

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

playground

answered Jan 25, 2022 at 23:02

R2D2

10.9k2 gold badges20 silver badges41 bronze badges

3 Comments

Alex Blex Over a year ago

I wouldn't double unwind whole collection. Add a copy of $match as a first stage to unwind only documents that has at least 1 singer.

R2D2 Over a year ago

I have assumed there is always at least one singer in the band :) , but indeed the filter solution seems to perform better ...

Eduardo Sztokbant Over a year ago

I really liked the output of this option as "{ _id: ..., cnt: ...}", though it seems to be too heavy for a larger collection: "MongoServerError: Exceeded memory limit for $group, but didn't allow external sort. Pass allowDiskUse:true to opt in."

Eduardo Sztokbant · Accepted Answer · 2022-01-25 23:33:58Z

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

answered Jan 25, 2022 at 23:33

Eduardo Sztokbant

7926 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related