I am trying to obtain the average and sum of totals of a collection in MongoDB, for this I do a $lookup which returns the information correctly but when I want to group it and obtain the sum of totals as well as the average, those two properties always return them as null
This is my MongoDB Query:
db.clients.aggregate(
[
{
$lookup: { // DATA OK
from: 'sales',
localField: '_id',
foreignField: 'clientId',
as: 'ventaPorCliente'
}
},
{
$group: { // total_average and sum null
_id: "$idClient",
username: { $first: "$name" },
total_average: { $avg: 'ventaPorCliente.total'},
sum: { $sum: 'ventaPorCliente.total'},
count: { $sum: 1 }
}
},
]
)
Response:
[
{
"_id": "1",
"username": "Peishion",
"total_average": null,
"sum": 0,
"count": 1
},
{
"_id": "1010",
"username": "BENJAMIN",
"total_average": null,
"sum": 0,
"count": 1
}
]
How can i access to ventaporCliente.total?
Thanks.
CodePudding user response:
You missed the $
sign, also unwind the ventaPorCliente
array, as $lookup pushes the matching objects in an array. Try this:
db.clients.aggregate(
[
{
$lookup: {
from: 'sales',
localField: '_id',
foreignField: 'clientId',
as: 'ventaPorCliente'
}
},
{
$unwind: "$ventaPorCliente"
}
{
$group: {
_id: "$idClient",
username: { $first: "$name" },
total_average: { $avg: '$ventaPorCliente.total'},
sum: { $sum: '$ventaPorCliente.total'},
count: { $sum: 1 }
}
},
]
)