多字段$lookup

Question

我有两个集合，并试图在两个不同的条件下得到总金额的总和。

我的密码是：

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 } }
      ]
    }
  }
]);

其结果是总额或应付总额，但不是两者兼而有之。如何获得司机的详细资料以及总金额和总费用？

Answer 1

不能像在这里使用pipeline一样多次在同一个对象中使用同一个字段。

相反，可以在$facet内部使用$lookup聚合一次处理多个管道。

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"
  }}
])