MongoDB如何对订单模型进行复杂的聚合

Question

大家好，这是我的订单模型：

const OrderSchema = new Schema({

   orderItems: {
        name: { type: String, required: true },
        qty: { type: Number, required: true },
        image: { type: String, required: false },
        price: { type: Number, required: true },
        product: {
            type: mongoose.Schema.Types.ObjectId,
            ref: 'Product',
            required: true,
        },
    },
    totalPrice: { type: Number, required: true },
    paymentMethod: { type: String, required: true },
    paymentResult: {
        status: { type: String, default: 'Waiting For Payment' }
    },
    user: { type: mongoose.Schema.Types.ObjectId, ref: 'User', required: true },
    isPaid: { type: Boolean, default: false },
    paidAt: { type: Date },
    OrderResult: {
        status: { type: String, default: 'Waiting For Payment' }
    },
    isDelivered: { type: Boolean, default: false },

},{timestamps : true})

我确实进行了此汇总以获取用户订单摘要：

        const usersOrderSummary = await Order.aggregate(
            [
               {$match:{} },
               {$group: {_id:"$user", TotalSpent: {$sum:"$totalPrice"},TotalOrders: { $sum: 1 }}},
            ]
         )

此汇总的结果是：

{
    "usersOrderSummary": [
        {
           "_id": "6216687c0e0d9122f710a1a6",
           "TotalSpent": 0.9,
           "TotalOrders": 8
        },
        {
           "_id": "628e4b96a7fd3bad9482a81c",
           "TotalSpent": 9.18,
           "TotalOrders": 53
        }
    ]  
}

我希望您的帮助做一个汇总以获得此结果：

我想做聚合以收集完成订单的总和和本完成订单中花费的金额总额

已完成订单的条件，因此我只能在 iSdelivered：true 请检查上面的模式，以便知道订单如何被认为完成

{
    "usersOrderSummary": [
        {
           "_id": "6216687c0e0d9122f710a1a6",
           "TotalSpent": 0.9,
           "TotalOrders": 8,
           "TotalCompletedOrderSpent": 0.1,
           "TotalCompletedOrders": 1,

        },
        {
           "_id": "628e4b96a7fd3bad9482a81c",
           "TotalSpent": 9.18,
           "TotalOrders": 53
           "TotalCompletedOrderSpent": 4,
           "TotalCompletedOrderSpent": 2,
        }
    ]  
}

Answer 1

使用<

db.collection.aggregate([
  {
    $match: {}
  },
  {
    $group: {
      _id: "$user",
      TotalSpent: { $sum: "$totalPrice" },
      TotalOrders: { $sum: 1 },
      TotalCompletedOrderSpent: {
        $sum: {
          $cond: {
            if: { $eq: [ "$isDelivered", true ] },
            then: "$totalPrice",
            else: 0
          }
        }
      },
      TotalCompletedOrders: {
        $sum: {
          $cond: {
            if: { $eq: [ "$isDelivered", true ] },
            then: 1,
            else: 0
          }
        }
      }
    }
  }
])

a href =“ https://www.mongodb.com/docs/manual/reference/reference/operator/aggregation/aggregation/cond/” /Mongoplayground.net/p/h5pa3c5qe2q“ rel =“ nofollow noreferrer”> mongoplayground