Calculate the Average Value for a Field Inside an Array with MongoDB

In this post, you will learn how to use MongoDB queries and calculate the average value for fields inside an array sub-document.

The document model

We will use a lego model for this example. The lego set document has a reviews property. Each review has a rating score. Let’s take a look at what we have stored in the Mongo database.

/* QUERY */
db.legosets.find({})

/* RESULTS*/
/* 1 */
{
    "_id" : ObjectId("5c7ba121f33c1f2dd4db2d47"),
    "name" : "Millenium Falcon",
    "estDelivery" : ISODate("2019-03-04T22:00:00.000Z"),
    "difficulty" : "HARD",
    "theme" : {
        "$ref" : "themes",
        "$id" : ObjectId("5c7ba121f33c1f2dd4db2d44")
    },
    "reviews" : [ 
        {
            "userName" : "Dan",
            "rating" : 10
        }, 
        {
            "userName" : "Hater",
            "rating" : 8
        }
    ]
}

/* 2 */
{
    "_id" : ObjectId("5c7ba121f33c1f2dd4db2d48"),
    "name" : "Sky Police Air BAse",
    "estDelivery" : ISODate("2019-03-12T22:00:00.000Z"),
    "difficulty" : "EASY",
    "theme" : {
        "$ref" : "themes",
        "$id" : ObjectId("5c7ba121f33c1f2dd4db2d45")
    },
    "reviews" : [ 
        {
            "userName" : "Anna",
            "rating" : 9
        }, 
        {
            "userName" : "Jim",
            "rating" : 5
        }
    ]
}

Using MongoDB aggregations to compute the ratings average

We can calculate the average for the rating field by using a projection. Inside the projection, we have 2 sub-functions:

  • The first one extracts the name from the lego set and projects it to a new property called legoSetName
  • The second one extracts the rating property from each review, then performs an $avg operation on them, then projects the value to a new property called avgRating

Finally, we wrap the projection function inside an aggregation, and we will get the following result.

/* QUERY */
db.legosets.aggregate([{
        $project : {
                legoSetName: "$name",
                avgRating : {$avg : "$reviews.rating"}
            }
}])

/* RESULTS*/
/* 1 */
{
    "_id" : ObjectId("5c7ba121f33c1f2dd4db2d47"),
    "legoSetName" : "Millenium Falcon",
    "avgRating" : 9.0
}

/* 2 */
{
    "_id" : ObjectId("5c7ba121f33c1f2dd4db2d48"),
    "legoSetName" : "Sky Police Air BAse",
    "avgRating" : 7.0
}