Reputation: 81
I have two collections, and trying to get the sum of total amounts with two different conditions.
Heere is my code:
db.drivers.aggregate([
{
$lookup: {
from: "bookings",
let: { driver_id: "$_id" },
pipeline: [
{ $match: { $expr: { $eq: ["$driverId", "$$driver_id"] } } },
{
$group: {
_id: "$driverId",
totalAmount: { $sum: "$driverAmount" }
}
}
],
as: "bookingdata",
pipeline: [
{
$match: {
$expr: {
$and: [
{ $eq: ["$driverId", "$$driver_id"] },
{ $eq: ["$payment.settled", false] }
]
}
}
},
{
$group: {
_id: "$driverId",
totaldues: { $sum: "$driverAmount" }
},
bookingdata: {
$push: "$totaldues"
}
},
{ $project: { _id: 0, totalAmount: 1, totaldues: 1 } }
]
}
}
]);
Its resulting either total amount or total dues, but not both. How to get driver details along with the total amount and total dues?
Upvotes: 2
Views: 910
Reputation: 46491
You cannot use same field multiple times in the same object as you have used pipeline
here.
Instead use $facet
aggregation inside the $lookup
to process multiple pipelines at once.
db.drivers.aggregate([
{ "$lookup": {
"from": "bookings",
"let": { "driver_id": "$_id" },
"pipeline": [
{ "$facet": {
"totaldues": [
{ "$match": {
"$expr": {
"$and": [
{ "$eq": ["$driverId", "$$driver_id"] },
{ "$eq": ["$payment.settled", false] }
]
}
}},
{ "$group": {
"_id": "$driverId",
"totaldues": { "$sum": "$driverAmount" },
"bookingdata": { "$push": "$totaldues" }
}},
{ "$project": { "_id": 0, "totalAmount": 1, "totaldues": 1 } }
],
"totalAmount": [
{ "$match": { "$expr": { "$eq": ["$driverId", "$$driver_id"] } }},
{ "$group": {
"_id": "$driverId",
"totalAmount": { "$sum": "$driverAmount" }
}}
]
}}
],
"as": "bookingdata"
}}
])
Upvotes: 1