MongoDb 聚合，每个值的所有现有值

Question

我有以下问题:
在我的 mongo db 中，我有以下结构:

{
    "instanceId": "12",
    "eventId": "0-1b",
    "activityType": "A",
    "personId": "1",
},
{
    "instanceId": "12",
    "eventId": "0-1b",
    "activityType": "B",
    "personId": "2",
},
{
    "instanceId": "13",
    "eventId": "0-1c",
    "activityType": "B",
    "personId": "2",
}
{
    "instanceId": "14",
    "eventId": "0-1d",
    "activityType": "C",
    "personId": "3",
    
}{
    "instanceId": "14",
    "eventId": "0-1d",
    "activityType": "C",
    "personId": "4",
    
}

我需要做的是计算不同 id 的人数，
和不同实例的数量，我需要得到这样的表:


类型
numberOf_Different_People
numberOf_Different_instances


事件类型 A
1
1

事件类型 B
1
2

事件类型 C
2
1


这是这个例子中的一个工作计数。
我可以分 3 个步骤完成:

获取所有事件类型:

[{ $group: { _id: "$activityType",}}]

对于每个事件类型，获取其中的不同 peopleId 的数量:

[{$match: {"activityType": "A"}}, {$group: {_id: "$personId", }}, {$count: "_"}]

对于每个事件类型获取不同实例的数量:

[{$match: {"activityType": "A"}}, {$group: { _id: "$eventId",}}, {$count: "_"}]

我怎样才能用更少的 DB 请求来做到这一点？
有没有办法在 1 个查询中返回这个完整的表？
非常感谢任何帮助

Answer 1

获取唯一计数 -Demo - https://mongoplayground.net/p/hcK8-kY9Nwa
$addToSet
$size

db.collection.aggregate([
  { $group: { _id: "$activityType", personIds: { $addToSet: "$personId" }, instanceIds: { $addToSet: "$instanceId" } }}, // group by id, $addToSet will add unique values to the array
  { $project: { _id: 0, type: "$_id", peopleCount: { $size: "$personIds" }, instanceCount: { $size: "$instanceIds" } } } // get the size of array
])

输出

[
  {
    "instanceCount": 2,
    "peopleCount": 1,
    "type": "B"
  },
  {
    "instanceCount": 1,
    "peopleCount": 2,
    "type": "C"
  },
  {
    "instanceCount": 1,
    "peopleCount": 1,
    "type": "A"
  }
]

获取总数 - 演示 - https://mongoplayground.net/p/duN8EKcdoaa
$push

db.collection.aggregate([
  { $group: { _id: "$activityType", personIds: { $push: "$personId" }, instanceIds: { $push: "$instanceId" } } },
  { $project: { _id: 0, type: "$_id", peopleCount: { $size: "$personIds" }, instanceCount: { $size: "$instanceIds" } } }
])

输出

[
  {
    "instanceCount": 2,
    "peopleCount": 2,
    "type": "B"
  },
  {
    "instanceCount": 1,
    "peopleCount": 1,
    "type": "A"
  },
  {
    "instanceCount": 2,
    "peopleCount": 2,
    "type": "C"
  }
]