MongoDB - calculating average of nested array of objects' attributes

Question

I have a database of teams with nested players as below:

{
 team_id: "eng1",
 date_founded: new Date("Oct 04, 1896"),
 league: "Premier League",
 points: 62,
 name: "Manchester United",
 players: [ { p_id: "Rooney", goal: 85, caps: 125, age: 28 },
            { p_id: "Scholes", goal: 15, caps: 225, age: 28 },
            { p_id: "Giggs", goal: 45, caps: 359, age: 38 } ]
}

I'm trying to calculate the average age of each team (the average of all players' ages), however I can't access the $player.age values correctly.

cursor = db.teams.aggregate({ 
  $group : { _id: "$name", avgAge : { 
    $avg : "$players.age" }
  }
});

This just returns the following:

{
  { "_id": "AC Milan", avgAge: 0 },
  { "_id": "Barcelona", avgAge: 0 }
  { "_id": "Real Madrid", avgAge: 0 }
  ...
}

(The players ages are all definitely there)

Any help?

Answer 1

Since the players field is an array, trying to access its member with $players.age is too confusing, mongo doesn't know which element of the array you want to access. Then come $unwind to the rescue, it will make each element of the array to become the embedded element of the field players. If you do $unwind with the document "Manchester United", you will have something like this

{ "_id" : ObjectId("5666fbbd755e59eab7a3e05e"), "team_id" : "eng1", "date_founded" : ISODate("1896-10-03T17:00:00Z"), "league" : "Premier League", "points" : 62, "name" : "Manchester United", "players" : { "p_id" : "Rooney", "goal" : 85, "caps" : 125, "age" : 28 } }
{ "_id" : ObjectId("5666fbbd755e59eab7a3e05e"), "team_id" : "eng1", "date_founded" : ISODate("1896-10-03T17:00:00Z"), "league" : "Premier League", "points" : 62, "name" : "Manchester United", "players" : { "p_id" : "Scholes", "goal" : 15, "caps" : 225, "age" : 28 } }
{ "_id" : ObjectId("5666fbbd755e59eab7a3e05e"), "team_id" : "eng1", "date_founded" : ISODate("1896-10-03T17:00:00Z"), "league" : "Premier League", "points" : 62, "name" : "Manchester United", "players" : { "p_id" : "Giggs", "goal" : 45, "caps" : 359, "age" : 38 } }`

The field players of the above document have 3 element, so you will have 3 documents with same property like the original document, but the element in the array have moved up to be come embedded document of players. Then access to the age of a player is easy with $players.age because its an embedded document.

Final query

cursor = db.teams.aggregate([
    { $unwind: "$players" },
    { $group : { _id: "$name", avgAge : {  $avg : "$players.age" } } }
]);

Answer 2

If you are trying to store the value back in the document, it can be simplified as:

db.teams.updateMany({}, [{$set: {average: {$avg: "$players.age"}}}])

I am not sure if this is a recent change, but I am running Mongo 4.4 and it works.