Counting means scan all documents. That's the problem.
**Alternative solution:** [On-Demand Materialized Views](https://www.mongodb.com/blog/post/coming-in-mongodb-42--ondemand-materialized-views)
We can apply a solutiom without any `$lookup`. We reduct into single document and store into separated arrays different values to perform counting task.
**Note:** It is subject to not exceeding the maximum size (16MB) per document
db.members.aggregate([
{
$group: {
_id: null,
root: {
$push: "$$ROOT"
},
name: {
$push: "$name"
},
hobby: {
$push: "$hobby"
}
}
},
{
$unwind: "$root"
},
{
$project: {
_id: 0,
name: "$root.name",
hobby: "$root.hobby",
nameCount: {
$reduce: {
input: "$name",
initialValue: 0,
in: {
$add: [
"$$value",
{
$cond: [
{
$eq: [
"$root.name",
"$$this"
]
},
1,
0
]
}
]
}
}
},
hobbyCount: {
$reduce: {
input: "$hobby",
initialValue: 0,
in: {
$add: [
"$$value",
{
$cond: [
{
$eq: [
"$root.hobby",
"$$this"
]
},
1,
0
]
}
]
}
}
}
}
}
])
[MongoPlayground](https://mongoplayground.net/p/lseJxSkWE0c)
**How to avoid exceeding 16MB:**
We can use `_id` field if it's `ObjectId` generated by MongoDB. Or, add `date` field / auto-incremental. We can `$group` into smaller documents (<16MB), group + calculate counts and then just `$sum` results...
If **@srinivasy**'s answer meets your requierements, please grant my points him :)
If you want to get such structure:
{
count: 454,
results: [
{name: "Joe", hobby: "Food", fanClub: ["Joe", "Lyn", "Alfred"]},
{name: "Lyn", hobby: "Food", fanClub: ["Joe", "Lyn", "Alfred"]},
{name: "Rex", hobby: "Play", fanClub: ["Rex"]},
{name: "Rex", hobby: "Shop", fanClub: ["Rex", "Rita"]}
]
}
Use this query (`$reduce` is used to return single value, in you case `fanClub` as array):
db.members.aggregate([
{
$facet: {
pipe1: [
{
$count: "count"
}
],
pipe2: [
{
$skip: 0
},
{
$limit: 4
},
{
$lookup: {
from: "members",
let: {
hobby: "$hobby"
},
pipeline: [
{
$match: {
$expr: {
$eq: [
"$hobby",
"$$hobby"
]
}
}
}
],
as: "fanClub"
}
}
]
}
},
{
$unwind: "$pipe1"
},
{
$project: {
count: "$pipe1.count",
results: {
$map: {
input: "$pipe2",
as: "pipe2",
in: {
_id: "$$pipe2._id",
hobby: "$$pipe2.hobby",
name: "$$pipe2.name",
fanClub: {
$reduce: {
input: "$$pipe2.fanClub",
initialValue: [],
in: {
$concatArrays: [
"$$value",
[
"$$this.name"
]
]
}
}
}
}
}
}
}
}
])
[MongoPlayground](https://mongoplayground.net/p/tMn8AB9Nsq1)
