Grouping of grouped results in mongoDb

Question

I apologize if the title is not precise enough, I'm not sure how to describe my problem better. I have the following data:

Category: A, Group: 1
Category: A, Group: 1
Category: A, Group: 2
Category: A, Group: 3
Category: B, Group: 5
Category: B, Group: 5

I would need to group by Category and Group count, so that the result would look like:

Category A: 3 
Category B: 1

I was able to only group by category so far

{ $group: { _id: "$category", count:{$sum:1} } }

However, I'm not sure how to count the distinct groups?

Answer 1

Including data as part of the field name (usually not recommended) makes the aggregation pipeline a bit awkward, but still doable.

Here's one way you could do it.

db.collection.aggregate([
  {
    "$group": {
      "_id": "$Category",
      "groups": {"$addToSet": "$Group"}
    }
  },
  {
    "$replaceWith": {
      "$arrayToObject": [
        [
          {
            "k": {"$concat": ["Category ","$_id"]},
            "v": {"$size": "$groups"}
          }
        ]
      ]
    }
  }
])

Try it on mongoplayground.net.