MongoDB.live, free & fully virtual. June 9th - 10th. Register Now MongoDB.live, free & fully virtual. June 9th - 10th. Register Now

Aggregate and unwinding

{
    "_id": "5e7b0e1c2ff059dbea2bf2d3"
    "name": "Phyllis Larson",
    "gender": "female",
    "loans": [
      {
        "id": 1,
        "balance": 1989.31
      },
      {
        "id": 2,
        "balance": 482.08
      },
      {
        "id": 3,
        "balance": 1142.74
      }
    ]
  },
{
    "_id": "5e7b0e1c3018e1b7cc97b7fe",
    "name": "Mason Walker",
    "gender": "male",
    "loans": [
      {
        "id": 1,
        "balance": 2335.99
      },
      {
        "id": 2,
        "balance": 3943.21
      },
      {
        "id": 3,
        "balance": 1156.3
      }
    ]
  },

In my data I have a lot of documents like these, what I need is a average spending on the basis of gender. When I unwind the loans to add up the individual balance for each person more than one document is created with all balances. While using the following query I get the average but the this is less than expected, because for people having more than 1 balance,

db.cd.aggregate([ 
   { $unwind : "$loans" },
   { $group : { _id : "$gender", balance : { $avg : "$loans.balance" } } } 
])

After unwinding more than one document will be created there by changing average. For instance if we have 6 males with 3 loans of each $1000 and 4 females with 2 loans each $2500, the total loans with 6 males is $18000 and that with females is $20000. The average balance of males is $3000 and that of females is $5000. With the above aggregation we will get average loan of males as $1000 because after unwinding there will be 18 records of male and 8 records of females with average of $2500. What is the approach to get correct average even after unwinding?

Thanks
Arun

The correct approach is to find the total loan balance for each individual, and then find the average by gender:

db.collection.aggregate( [
  { 
      $addFields: { 
          total_balance: {
              $reduce: {
                  input: "$loans" ,
                  initialValue: 0,
                  in: { 
                      $add: [ "$$value", "$$this.balance" ] 
                  }
              }
          }
      }
  },
  { 
      $group: {
           _id: "$gender",
           avg_balance: { $avg: "$total_balance" }
      }
  }
] ).pretty()