How to get a rating average in MongoDB based on duplicate ids?

To get a rating average in MongoDB based on duplicate ids, use the $avg operator with $group. The $ifNull operator handles null rating values by treating them as zero in the calculation.

Syntax

db.collection.aggregate([
    {
        "$group": {
            "_id": "$fieldToGroupBy",
            "averageField": { "$avg": { "$ifNull": ["$ratingField", 0] } }
        }
    }
]);

Sample Data

db.demo606.insertMany([
    {id: 1, rating: 5},
    {id: 1, rating: 4},
    {id: 2, rating: 3},
    {id: 1, rating: null},
    {id: 2, rating: null},
    {id: 2, rating: 3}
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("..."),
        ObjectId("..."),
        ObjectId("..."),
        ObjectId("..."),
        ObjectId("..."),
        ObjectId("...")
    ]
}

Display all documents from the collection ?

db.demo606.find();

{ "_id" : ObjectId("5e972dfbf57d0dc0b182d623"), "id" : 1, "rating" : 5 }
{ "_id" : ObjectId("5e972dfef57d0dc0b182d624"), "id" : 1, "rating" : 4 }
{ "_id" : ObjectId("5e972e09f57d0dc0b182d625"), "id" : 2, "rating" : 3 }
{ "_id" : ObjectId("5e972e0ef57d0dc0b182d626"), "id" : 1, "rating" : null }
{ "_id" : ObjectId("5e972e15f57d0dc0b182d627"), "id" : 2, "rating" : null }
{ "_id" : ObjectId("5e972e1bf57d0dc0b182d628"), "id" : 2, "rating" : 3 }

Example

Calculate the average rating for each id, treating null values as 0 ?

db.demo606.aggregate([
    {
        "$group": {
            "_id": "$id",
            "AverageRating": { "$avg": { "$ifNull": ["$rating", 0] } }
        }
    }
]);

{ "_id" : 2, "AverageRating" : 2 }
{ "_id" : 1, "AverageRating" : 3 }

How It Works

• $group groups documents by the id field
• $avg calculates the average of rating values within each group
• $ifNull replaces null ratings with 0 before averaging

For id 1: (5 + 4 + 0) / 3 = 3
For id 2: (3 + 0 + 3) / 3 = 2

Conclusion

Use $group with $avg to calculate rating averages for duplicate ids. The $ifNull operator ensures null values are properly handled in the calculation.