CODEWITHSUNDEEP
node.jsmongodbmongoosemongodb-queryaggregation-framework
altschuler
altschuler

Reputation: 3922

Count number of referenced objects in Mongo

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.

Upvotes: 1

Views: 106

Answers (1)

chridam
chridam

Reputation: 103355

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);
})

Upvotes: 1

Related Questions