Andre M Andre M - 3 months ago 11
Node.js Question

Mongoose & getting average of ratings in a collection?

I have a NodeJS based application using Mongoose. I am wanting to create a response where there values are an average of ratings provided by people who have responded to a questionnaire, for each question asked.

My Schema looks as follows:

const questionnaireResultSchema = new Schema({
user: { type: ObjectId, ref: 'User' },
questionnaire: { type: ObjectId, ref: 'Questionnaire' },
rating: [{
id: Number,
question: String,
value: Number
}]
},{
timestamps: true
}).index({user: 1, questionnaire: 1}, {unique: true});


I have looked at the Mongoose aggregator operator, but I am not sure how I would apply it to my case. Pseudo code would look as follows:


  • Find all questionnaire results for questionnaire of id xyz

  • Provide a result where the ratings for each questionnaire result has be averaged



For example the response would look as follows:

[{
id: 1,
question: 'How strongly do you feel about candidate A?',
value: 12 // averaged value
},{
id: 2,
question: 'How strongly do you think we should change the sky color to green?',
value: 31 // averaged value
},{
id: 3,
question: 'How strongly do you think your answers count?',
value: 20 // averaged value
}]


I have tried:

QuestionnaireResultSchema.aggregate([{
$match: {
questionnaire: questionnaire
}},
{$project: {
scores: { $avg: '$scores'}
}}
]);


This just provides the JSON:

[{
"_id": "57bbd4b495407f6145b3ba9f",
"scores": null
}]


A sample document in a collection would like:

{
user: ObjectId("57bca30536e376c653f439bb")
questionnaire: ObjectId("37bca0feedb0bc470353ab")
scores: [{
id: 1,
question: 'How strongly do you feel about candidate A?',
value: 3
},{
id: 2,
question: 'How strongly do you think we should change the sky color to green?',
value: 4 // averaged value
},{
id: 3,
question: 'How strongly do you think your answers count?',
value: 5 // averaged value
}]
}


While I could calculate the averages myself, if Mongoose provides the functionality, I would rather leverage that.

Any help would be appreciated.

Answer

This should work for your aggregation pipeline:

[
    {
        $match: {
            questionnaire: ObjectId("37bca0feedb0bc470353ab")
        }
    },
    {
        $unwind: "$rating"
    },
    {
        $group:{
            _id:{
                "rating_id": "$rating.id",
                "question": "$rating.question",
            },
            avg_rating: {$avg:"rating.value"}
        }
    },
    {
        $project:{
            "id": "$_id.rating_id",
            "question": "$_id.question",
            "avg_rating": "$avg_rating"
        }
    }
]

Although your sample doc has "scores" instead of "rating" in which case you'd use:

[
    {
        $match: {
            questionnaire: ObjectId("237bca0feedb0bc470353aba")
        }
    },
    {
        $unwind: "$scores"
    },
    {
        $group:{
            _id:{
                "rating_id": "$scores.id",
                "question": "$scores.question",
            },
            avg_rating: {$avg:"scores.value"}
        }
    },
    {
        $project:{
            "id": "$_id.rating_id",
            "question": "$_id.question",
            "avg_rating": "$avg_rating"
        }
    }
]

Also, some of the ObjectId's that you are using are not valid. I'm assuming those are just stubbed.

Comments