How can I calculate the average between fields outside a document array with those inside a documents array in MongoDB and Java?

Question

I have this document in my database:

[
  {
    "_id": {
      "$oid": "5f5f280ffa2236115655cb6a"
    },
    "Name": "Rovilio Chipman",
    "Last_season": {
      "year": "2010-2011",
      "goals": 10,
      "assists": 1
    },
    "Last_season_2": {
      "year": "2011-2012",
      "goals": 1,
      "assists": 12
    },
    "Seasons": [
      {
        "year": "2012-2013",
        "goals": 11,
        "assists": 4
      },
      {
        "year": "2013-2014",
        "goals": 6,
        "assists": 2
      },
      {
        "year": "2014-2015",
        "goals": 5,
        "assists": 5
      }
    ]
  }
]

I would like to get the average of all goals, ie the average of the goals included in "Last_season", "Last_season_2" and "Season". The result should be 33/5 = 6.6.

NB: the documents on which to make this average are of different size, ie the "Season" array can contain a different number of documents and not fixed.

How do I calculate this average in this case? How do I code it with Java Driver?

Answer 1

First you need to put all values in one array, then you can calculate the average. This could be one solution:

db.collection.aggregate([
  {
    $set: {
      AllSeasons: {
        $concatArrays: [
          "$Seasons",
          [ "$Last_season" ],
          [ "$Last_season_2" ]
        ]
      }
    }
  },
  { $set: { average: { $avg: [ "$AllSeasons.goals" ] } } },
  { $unset: "AllSeasons" }
])

Mongo Playground