Hello Guys This is my Order Model :

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

I did do This aggregation to get Users Orders Summary :

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

The Result Of this aggregation is an array of:

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

I want your Help to do an aggregation to get this result:

I want to to do aggregation to collect the sum of completed orders and the sum of the amount that spent in this completed order

the condtion of completed order so i can only considered an order completed when isDelivered:true please check the schema above so it would be clear to know how order considered as completed

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

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

CodePudding user response：

Use $cond

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

mongoplayground