MongoDB complex Aggregate function

Question

I'm facing a problem with the aggregate function done on the MongoDB schema model. I have a Medicine model that has the following form.

and an Order model that has the following form.

What I want to do is filter totalsales by medicine name so for example if I have panadol, catafast, etc.

I want to know how much Panadol I have sold and so on.

I'm trying to use an aggregate function to group all completed orders and filter them then trying to lookup for the medicine name by the id in the array. However, I got an empty array and I'm pretty sure of my data. What I'm not sure about is the pipeline in the function.

const getSalesDataByMedicine = async (req, res) => {

    try {
        const medicineSales = await Orders.aggregate([
            {
                $match: {
                    status: "Completed"
                }
            },
            {
                $unwind: "$items"
            },
            {
                $group: {
                    _id: "$items.MedicineId",
                    totalQuantity: { $sum: "$items.Quantity" },
                    totalAmount: { $sum: { $multiply: ["$items.Quantity", "$amount"] } }
                }
            },
            {
                $lookup: {
                    from: "medicine", // Replace with the actual name of your Medicine model's collection
                    localField: "_id",
                    foreignField: "MedicineId", // Assuming Medicine _id is used in MedicineId field
                    as: "medicineData"
                }
            },
            {
                $unwind: "$medicineData"
            },
            {
                $project: {
                    _id: 0,
                    medicineId: "$_id",
                    medicineName: "$medicineData.name", // Adjust to your actual field name in Medicine model
                    totalQuantity: 1,
                    totalAmount: 1
                }
            }
        ]);

        return res.status(200).json(medicineSales);
    } catch (err) {
        console.error("Error fetching medicine sales data:", err);
        return res.status(500).json({ error: "Internal Server Error" });
    }


};

Thanks in advance.

Answer 1

Based on your current query, you need to make these changes:

1. Convert items.MedicineId to ObjectId in the $group stage.

2. The foreign field is _id, the _id field from the document in the medicine collection but not the MedicineId.

[
  ...,
  {
    $group: {
      _id: {
        $toObjectId: "$items.MedicineId"
      },
      ...
    }
  },
  {
    $lookup: {
      from: "medicine",
      // Replace with the actual name of your Medicine model's collection
      localField: "_id",
      foreignField: "_id",
      // Assuming Medicine _id is used in MedicineId field
      as: "medicineData"
    }
  },
  ...
]

---

Notice that your calculation for the total sales amount of each product is incorrect as you are multiplying by the amount in the order. It is supposed to be multiplied by the unit price of the medicine. (medicine document -> price)

totalAmount: { $sum: { $multiply: ["$items.Quantity", "$amount"] } }

So your query should be:

db.order.aggregate([
  {
    $match: {
      status: "Completed"
    }
  },
  {
    $unwind: "$items"
  },
  {
    $group: {
      _id: {
        $toObjectId: "$items.MedicineId"
      },
      totalQuantity: {
        $sum: "$items.Quantity"
      }
    }
  },
  {
    $lookup: {
      from: "medicine",
      // Replace with the actual name of your Medicine model's collection
      localField: "_id",
      foreignField: "_id",
      // Assuming Medicine _id is used in MedicineId field
      as: "medicineData"
    }
  },
  {
    $unwind: "$medicineData"
  },
  {
    $project: {
      _id: 0,
      medicineId: "$_id",
      medicineName: "$medicineData.name",
      // Adjust to your actual field name in Medicine model
      totalQuantity: 1,
      totalAmount: {
        $multiply: [
          "$totalQuantity",
          "$medicineData.price"
        ]
      }
    }
  }
])

Demo Solution 1 @ Mongo Playground

Also as mentioned in the comment, I think that joining the medicine collection (base) with the order collection may perform better by eliminating those $uwind stages which are costly in queries.

db.medicine.aggregate([
  {
    $lookup: {
      from: "order",
      let: {
        medicineId: {
          $toString: "$_id"
        }
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$status",
                    "Completed"
                  ]
                },
                {
                  $in: [
                    "$$medicineId",
                    "$items.MedicineId"
                  ]
                }
              ]
            }
          }
        },
        {
          $set: {
            items: {
              $filter: {
                input: "$items",
                cond: {
                  $eq: [
                    "$$this.MedicineId",
                    "$$medicineId"
                  ]
                }
              }
            }
          }
        }
      ],
      "as": "orders"
    }
  },
  {
    $match: {
      orders: {
        $ne: []
      }
    }
  },
  {
    $project: {
      _id: 0,
      medicineId: "$_id",
      medicineName: "$name",
      // Adjust to your actual field name in Medicine model
      totalQuantity: {
        $sum: {
          $reduce: {
            input: "$orders",
            initialValue: [],
            in: {
              $concatArrays: [
                "$$value",
                "$$this.items.Quantity"
              ]
            }
          }
        }
      },
      totalAmount: {
        $multiply: [
          {
            $sum: {
              $reduce: {
                input: "$orders",
                initialValue: [],
                in: {
                  $concatArrays: [
                    "$$value",
                    "$$this.items.Quantity"
                  ]
                }
              }
            }
          },
          "$price"
        ]
      }
    }
  }
])

The usage of $reduce is to flatten the nested arrays for items, which is an array field in each document in the orders array.

Demo Solution 2 @ Mongo Playground