MongoDb aggregation, all existing values for each value

Question

I have the following problem:

In my mongo db i have the following structure:

{
    "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",
    
}

What I need to do, is to count number of persons with different id, and number of different instances , I need to get table like this:

Type	numberOf_Different_People	numberOf_Different_instances
ActivityType A	1	1
ActivityType B	1	2
ActivityType C	2	1

This is a working counting from this example.

I can do it in 3 steps :

Get all activityTypes :
```
[{ $group: { _id: "$activityType",}}]
```

For each activityType get number of different peopleIds in it :

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

For each activityType get number of different instances:

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

How can I do it with fewer requests to DB?

Is there any way to return this full table in 1 query?

Very appreciate any help

user_2224265 · Answer

Get unique count -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
])

Output

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

Get total count - Demo - 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" } } }
])

Output

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

MongoDb aggregation, all existing values for each value

1 Answers