mongodb中的$unwind、$aggregation操作

Question

请检查此查询

   db.billsummaryofthedays.aggregate([
    {
        '$match': {
            'userId': ObjectId('5e43de778b57693cd46859eb'),
            'adminId': ObjectId('5e43e5cdc11f750864f46820'),
            'date': ISODate("2020-02-11T16:30:00Z"),
        }
    },
         {
           $lookup:
           {
               from: "paymentreceivables",
               let: { userId: '$userId', adminId: '$adminId' },
               pipeline: [
                   {
                       $match:
                       {
                        paymentReceivedOnDate:ISODate("2020-02-11T16:30:00Z"),
                           $expr:
                           {
                               $and:
                                   [
                                       { $eq: ["$userId", "$$userId"] },
                                       { $eq: ["$adminId", "$$adminId"] }
                                   ]
                           }
                       }
                   },
                   { $project: { amount: 1, _id: 0 } }
               ],
               as: "totalPayment"         
           }
       }, {'$unwind':'$totalPayment'},
       {   $group:
        { _id:
           { date: '$date',
           userId: '$userId',
            adminId: '$adminId' },
          totalBill:
             {
                  $sum: '$billOfTheDay'    
           },
             totalPayment:
             {
                  $sum: '$totalPayment.amount'
           }
       }
   },
       }
   }])

，这是我在shell中得到的结果

   {
    "_id" : {
            "date" : ISODate("2020-02-11T18:30:00Z"),
            "userId" : ObjectId("5e43de778b57693cd46859eb"),
            "adminId" : ObjectId("5e43e5cdc11f750864f46820")
    },
    "totalBill" : 1595.6799999999998,
    "totalPayments" : 100
   }

现在，这不是我所期望的，我想，由于{'$unwind':'$totalPayment'}，它从数组中取出所有的值，因为每个文档都被计算了2次。当我移除{'$unwind':'$totalPayment'}时，totalBill和被证明是正确的，但是totalPayment是0。我尝试过其他几种方法，但无法达到预期的结果，以下是我的收藏：

    // collection:billsummaryofthedays//
      {
        "_id" : ObjectId("5e54f784f4032c1694535c0e"),
        "userId" : ObjectId("5e43de778b57693cd46859eb"),
        "adminId" : ObjectId("5e43e5cdc11f750864f46820"),
        "date" : ISODate("2020-02-11T16:30:00Z"),
        "UID":"acex01"
        "billOfTheDay" : 468,
     }
     {
        "_id" : ObjectId("5e54f784f4032c1694535c0f"),
         "UID":"bdex02"
        "userId" : ObjectId("5e43de778b57693cd46859eb"),
        "adminId" : ObjectId("5e43e5cdc11f750864f46820"),
        "date" : ISODate("2020-02-11T16:30:00Z"),
         "billOfTheDay" : 329.84,
       }
    // collection:paymentreceivables//
     {
        "_id" : ObjectId("5e43e73169fe1e3fc07eb7c5"),
        "paymentReceivedOnDate" : ISODate("2020-02-11T16:30:00Z"),
        "adminId" : ObjectId("5e43e5cdc11f750864f46820"),
        "userId" : ObjectId("5e43de778b57693cd46859eb"),
        "amount" : 20,
       }
      {
        "_id" : ObjectId("5e43e73b69fe1e3fc07eb7c6"),
        "paymentReceivedOnDate" : ISODate("2020-02-11T16:30:00Z"),
        "adminId" : ObjectId("5e43e5cdc11f750864f46820"),
        "userId" : ObjectId("5e43de778b57693cd46859eb"),
        "amount" : 30,
       }

希望得到的结果应该是totalBill:797.83 totalPayment:50 i.e468+329.84，和totalPayment:50 i.e30+20，，但我得到的是预期结果的两倍，即使我能够正确地计算其中一个值，另一个结果。

Answer 1

由于billsummaryofthedays集合中有多个具有相同数据的文档，所以可以首先分组&然后执行$lookup --这样，两个集合之间的连接将是1-Vs-many而不是当前编写的many-Vs-many，因此您可以尝试下面的查询以获得所需的o/p和性能增益：

db.billsummaryofthedays.aggregate([
  {
    "$match": {
      "userId": ObjectId("5e43de778b57693cd46859eb"),
      "adminId": ObjectId("5e43e5cdc11f750864f46820"),
      "date": ISODate("2020-02-11T16:30:00Z"),

    }
  },
  {
    $group: {
      _id: {
        date: "$date",
        userId: "$userId",
        adminId: "$adminId"
      },
      totalBill: {
        $sum: "$billOfTheDay"
      }
    }
  },
  {
    $lookup: {
      from: "paymentreceivables",
      let: {
        userId: "$_id.userId",
        adminId: "$_id.adminId"
      },
      pipeline: [
        {
          $match: {
            paymentReceivedOnDate: ISODate("2020-02-11T16:30:00Z"),
            $expr: {
              $and: [
                {
                  $eq: [
                    "$userId",
                    "$$userId"
                  ]
                },
                {
                  $eq: [
                    "$adminId",
                    "$$adminId"
                  ]
                }
              ]
            }
          }
        },
        {
          $project: {
            amount: 1,
            _id: 0
          }
        }
      ],
      as: "totalPayment"
    }
  },
  {
    $addFields: {
      totalPayment: {
        $reduce: {
          input: "$totalPayment",
          initialValue: 0,
          in: {
            $add: [
              "$$value",
              "$$this.amount"
            ]
          }
        }
      }
    }
  }
])

测试: MongoDB-游乐场