根据您当前的查询，您需要进行以下更改：
1.在$group阶段将items.MedicineId转换为ObjectId。
1.外部字段是_id，_id字段来自 medicine 集合中的文档，但不是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"
    }
  },
  ...
]

字符串
请注意，您计算的每种产品的总销售额是不正确的，因为您乘以 order 中的amount，它应该乘以药品的单价。（medicine document -> price）

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

型
所以你的查询应该是：

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
同样如评论中所提到的，我认为将 medicine 集合（基础）与 order 集合结合起来可能会通过消除那些查询成本高的$uwind阶段来实现更好的性能。

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

型
$reduce的用法是扁平化items的嵌套数组，items是orders数组中每个文档中的数组字段。
Demo Solution 2 @ Mongo Playground的