{
"_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