I have a collection that looks like this:

{
    id: 1
    user: 1
    gameCategory: aaa/traditional
    gameName: Artifact
},
{
    id: 2
    user: 1
    gameCategory: web3
    gameName: Axie Infinity
},
{
    id: 3
    user: 2
    gameCategory: aaa/traditional
    gameName: League of Legends
},
...

A single document indicates a user playing a game. How would I find the group of users who:

• only play "aaa/traditional" games
• only play "web3" games
• play both types of games

My idea is to run the $group stage with user & gameCategory, and use $match or something to filter out users who also play other categories of games

CodePudding user response：

EDIT: There are many options here. This is a simple one:

1. We use the $group to store for each user its gameCategory, but also to check if they play on another game, hence fill other. This allows us to store on groupType only gameCategory that is a key to a group
2. If groupType contains only one key, this is the group key, otherwise it is 'both'
3. This is true only if other key is empty.
4. $group by groupType and aggregate the users of the group

db.collection.aggregate([
  {
    $group: {
      _id: "$user",
      other: {
        $addToSet: {
          $cond: [
            {$and: [{$ne: ["$gameCategory", "web3"]},
                    {$ne: ["$gameCategory", "aaa/traditional"]}]
            }, "true", "$$REMOVE"]
        }
      },
      groupType: {
        $addToSet: {
          $cond: [
            {$or: [{$eq: ["$gameCategory", "web3"]},
                   {$eq: ["$gameCategory", "aaa/traditional"]}]
            }, "$gameCategory", "$$REMOVE"]
         }
      }
    }
  },
  {
    $project: {
      other: {$size: "$other"},
      groupType: {
        $cond: [{$eq: [{$size: "$groupType"}, 1]}, {$arrayElemAt: ["$groupType", 0]},
          "both"
        ]
      }
    }
  },
  {$project: {groupType: {$cond: [{$eq: ["$other", 1]}, "other",  "$groupType"]}}},
  {$group: { _id: "$groupType",  users: {$push: "$_id"}}}
])

Playground