MongoDB聚合查询问题：按药品名称统计销售额返回空数组

问题概述 #

使用Medicine和Order模型，需按药品名称（如Panadol、Catafast）统计对应药品的总销量与销售额。尝试通过聚合函数分组已完成订单，关联药品ID查询名称，但执行后返回空数组，确认数据存在，原实现代码如下：

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

错误原因分析 #

1. 集合名称不匹配：Mongoose模型默认生成的集合名是小写复数形式，Medicine模型对应的集合应为medicines而非medicine，$lookup的from字段错误会导致关联失败。
2. 关联字段错误：Medicine模型的主键默认是_id，而非MedicineId，订单的items.MedicineId存储的是药品的_id，因此$lookup的foreignField应设为_id。
3. 销售额计算逻辑错误：原代码用订单级别的$amount与订单项数量相乘，会导致销售额计算错误，应使用订单项的单价（如$items.price）来计算单条项的销售额。
4. $match阶段匹配失败：检查status字段的大小写或数据类型，比如实际订单的status是小写的"completed"，而代码中用了大写的"Completed"，会导致没有匹配的订单。

修正后的代码 #

const getSalesDataByMedicine = async (req, res) => {
    try {
        const medicineSales = await Orders.aggregate([
            // 调整为实际订单中status的准确取值
            {
                $match: {
                    status: "completed"
                }
            },
            // 保留空数组订单（可选，已完成订单一般无空items）
            {
                $unwind: {
                    path: "$items",
                    preserveNullAndEmptyArrays: false
                }
            },
            {
                $group: {
                    _id: "$items.MedicineId",
                    totalQuantity: { $sum: "$items.Quantity" },
                    // 使用订单项单价计算销售额，替换为实际字段名
                    totalAmount: { $sum: { $multiply: ["$items.Quantity", "$items.price"] } }
                }
            },
            {
                $lookup: {
                    from: "medicines", // 修正为Medicine模型对应的小写复数集合名
                    localField: "_id",
                    foreignField: "_id", // 关联药品主键_id
                    as: "medicineData"
                }
            },
            // 处理未匹配到药品的情况，避免过滤数据
            {
                $unwind: {
                    path: "$medicineData",
                    preserveNullAndEmptyArrays: true
                }
            },
            {
                $project: {
                    _id: 0,
                    medicineId: "$_id",
                    // 药品数据为空时显示默认值
                    medicineName: { $ifNull: ["$medicineData.name", "未知药品"] },
                    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" });
    }
};

额外排查建议 #

• 单独执行Orders.find({status: "Completed"})，确认$match阶段是否能查询到已完成订单。
• 检查items.MedicineId的类型是否为ObjectId，避免与药品集合的_id类型不匹配。
• 核实Medicine集合中是否存在与订单MedicineId对应的文档。

内容的提问来源于stack exchange，提问作者Ahmed Haitham