CODEWITHSUNDEEP
node.jsmongodbexpressmongoose
Gabriele Picco
Gabriele Picco

Reputation: 537

$lookup on ObjectId's in an array of objects (Mongoose)

I have this two schema:

module.exports = mongoose.model('Article', {
title : String,
text : String,
lang : { type: String, default: 'en'},
user : { type : mongoose.Schema.Types.ObjectId, ref: 'User' },
});



var userSchema = mongoose.Schema({
email        : String,
name         : String,
rating       : [{
                    _id: false,
                    articleID: {type: mongoose.Schema.Types.ObjectId, ref: 'Article'},
                    rate: Number
                }]
});
module.exports = mongoose.model('User', userSchema);

and i want to calculate the average rating of an user (the average on all rating on its articles).

I tried this:

User.aggregate([
            { $unwind: "$rating" },
            {
                "$lookup": {
                    "from": "Article",
                    "localField": "rating.articleID",
                    "foreignField": "_id",
                    "as": "article-origin"
                }
            }//,
            //{ $match: { "article-origin.user" : mongoose.Types.ObjectId(article.user) } }//,
            //{ $group : {_id : "$rating.articleID", avgRate : {  $avg : "$rating.rate" } } }
        ]).exec(function (err,result) {
            console.log(err);
            console.log(JSON.stringify(result));
        });

but without success, the lockup always return the field article-origin null.

result:{"_id":"590747e1af02570769c875dc","name":"name","email":"email","rating":{"rate":5,"articleID":"59074a357fe6a307981e7925"},"__v":0,"article-origin":[]}]

Why this is not working ?

Upvotes: 3

Views: 2218

Answers (1)

chridam
chridam

Reputation: 103455

Certainly no need for the $lookup operator since the group aggregation operation does not make use of the documents from the articles collection, it only needs a single field i.e. articleID for grouping.

There are two ways you can go about this. If your MongoDB server version is 3.4 or greater, then the $divide, $reduce and $size operators can be applied here to calculate the average without resorting to flatten the rating array first which can have some performance ramifications if the array is large.

Consider running the following pipeline:

User.aggregate([
    { "$match": { "_id" : mongoose.Types.ObjectId(article.user) } },
    {
        "$addFields": {
            "avgRate": {
                "$divide": [
                    {
                        "$reduce": {
                            "input": "$rating",
                            "initialValue": 0,
                            "in": { "$sum": ["$$value", "$$this.rate"] }
                        }
                    },
                    {
                        "$cond": [
                            { "$ne": [{ "$size": "$rating" }, 0] },
                            { "$size": "$rating" },
                            1
                        ]
                    }
                ]
            }
        }
    }
]).exec(function (err, result) {
    console.log(err);
    console.log(JSON.stringify(result));
});

If using MongoDB version 3.2 then you would need to $unwind the rating array first:

User.aggregate([
    { "$match": { "_id" : mongoose.Types.ObjectId(article.user) } },
    { "$unwind": "$rating" },
    {
        "$group": {
            "_id": "$_id",
            "avgRate": { "$avg": "$rating.rate" }
        }
    }
]).exec(function (err, result) {
    console.log(err);
    console.log(JSON.stringify(result));
});

If for some reason you need the $lookup operation, you need to reference the collection name, not the model name, thus the correct aggregate operation should be

User.aggregate([
    { "$unwind": "$rating" },
    {
        "$lookup": {
            "from": "articles", /* collection name here, not model name */
            "localField": "rating.articleID",
            "foreignField": "_id",
            "as": "article-origin"
        }
    },
    { "$match": { "article-origin.user" : mongoose.Types.ObjectId(article.user) } },
    {
        "$group": {
            "_id": "$_id",
            "avgRate": { "$avg": "$rating.rate" }
        }
    }
]).exec(function (err, result) {
    console.log(err);
    console.log(JSON.stringify(result));
});

Upvotes: 1

Related Questions