I have two collection one is invoice and another one is payment
invoice collection looks like this
{
_id: "123",
client: "ABC"
}
payment collection looks like
{
_id: "456",
invoiceId: "123",
amount: 100
},
{
_id: "789",
invoiceId: "123",
amount: 50
}
i want output like
{
_id: "123",
client: "ABC",
amount: 150
}
You can use the following aggregation so that you don't need to project the items manually:
db.invoices.aggregate([
{
$lookup: {
from: "payments",
localField: "_id",
foreignField: "invoiceId",
as: "payments"
}
},
{
$addFields: {
amount: {
$sum: "$payments.amount"
}
}
},
{
$project: {
payments: 0
}
}
])
You can achieve it with $lookup, $project and $sum like this:
db.invoice.aggregate([
{
//you can remove this stage if you don't need to filter your data
"$match": {
"_id": "123"
}
},
{
"$lookup": {
"from": "payment",
"as": "payments",
"localField": "_id",
"foreignField": "invoiceId"
}
},
{
"$project": {
"client": 1,
"amount": {
"$sum": "$payments.amount"
}
}
}
])
