Given two collections like:
users:
name
posts:
title
user_id
How can I query for users with more than 5 posts efficiently?
I can do an aggregation with $lookup but that takes ages for large datasets.
Another option is to maintain a "postCount" on users but I would like to avoid having to maintain that.
I'm using Mongoose on Node.
An efficient aggregate query which uses $lookup and a $redact pipeline for filtering would be as follows:
User.aggregate([
{
"$lookup": {
"from": "posts",
"localField": "_id",
"foreignField": "user_id",
"as": "posts"
}
},
{
"$redact": {
"$cond": [
{ "$gt": [ { "$size": "$posts" }, 5 ] },
"$$KEEP",
"$$PRUNE"
]
}
}
]).exec(function(err, results){
if (err) throw err;
console.log(results);
})