Schema:

const orderSchema = mongoose.Schema(
{

  orderStatus: {
         type: String,
         enum: ["pending", "preparing", "completed", "declined"],
         default: "pending",
  },
   products: [
          {
            product: {
              productId: {
                type: mongoose.Schema.Types.ObjectId,
                ref: "Product",
              },
              productName: String,
              productPrice: Number,
              categoryName: String,
            },
            quantity: {
              type: Number,
              required: true,
            }
            
          },
   ],
   totalPrice: { type: Number },
   acceptDeclineTime: {
      type: Date,
      default: Date.now,
   },
}
);

I want a yearly sales report with number of orders accepted and declined, with total price for each.

I tried:

orderSchema.aggregate(
    [

     {
        $unwind: {
          path: "$products",
        },
      },

     {

     $group: {
          _id: { $year: { date: "$acceptDeclineTime", timezone: " 03:00" } },
              totalCompletedPrice: {
                $sum: {
                  $cond: [{ $eq: ["$orderStatus", "completed"] }, "$totalPrice", 0],
                },
              },
              totalDeclinedPrice: {
                $sum: {
                  $cond: [{ $eq: ["$orderStatus", "declined"] }, "$totalPrice", 0],
                },
              },
              totalItems: {
                $sum: "$products.quantity",
              },
              completedSales: {
                $sum: {
                  $cond: [{ $eq: ["$orderStatus", "completed"] }, "$products.quantity", 0],
                },
              },
              cancelledSales: {
                $sum: {
                  $cond: [{ $eq: ["$orderStatus", "declined"] }, "$products.quantity", 0],
                },
              },
              
            },
          },

]);

But the price calculation is wrong as the $unwind stage duplicates total price of products which will be problematic on the $sum operation.

CodePudding user response：

I think you have to group two times, similar to this:

orderSchema.aggregate([
   { $unwind: { path: "$products" } },
   {
      $group: {
         _id: {
            year: { $year: { date: "$acceptDeclineTime", timezone: " 03:00" } },
            orderStatus: "$orderStatus"
         },
         products: { $push: "$products" },
         totalPrice: { $sum: "$totalPrice" }
      }
   },
   {
      $group: {
         _id: "$_id.year",
         ...
      }
   }
]);

CodePudding user response：

Simple solution suggested by pugro from reddit was to divide the total price by the size of the products array before $unwind operation then when recombined it'll add up.

$addFields:{
  totalPrice:{$divide:['$totalPrice',{$size:'$products'}]}
}