MongoDB查找和展开未给出正确的结果
我的产品模型包含不同卖方的所有产品的清单,而订购模型包含所订购产品的清单
我需要查询,以获取卖家的总收入
产品型号:
{
"_id": 1,
"ProductName": "product 1",
"Price": 100,
"SellerId": 1
},
{
"_id": 2,
"ProductName": "product 2",
"Price": 200,
"SellerId": 2
},
{
"_id": 3,
"ProductName": "product 3",
"Price": 50,
"SellerId": 1
}
订单模型:
{
"_id": 1,
"ProductId": 1,
"Price": 100,
"Quantity": 2,
"Total": 200,
"Status": true
},
{
"_id": 2,
"ProductId": 2,
"Price": 200,
"Quantity": 10,
"Total": 2000,
"Status": true
}
聚合
db.products.aggregate([
{
"$match": { "SellerId" :1 }
},
{
$lookup: {
from: 'orders',
localField: '_id',
foreignField: 'ProductId',
as: 'requests.service'
}
},
$group:{
_id: "$_id",
totalAmount: { $sum: "$Total" },
}
])
搜索特定卖方时,最终输出显示为0,无法找出解决方案。
卖方1的预期输出是200,卖方2的预期输出是2000
回答如下:您的查询必须是这样的:db.products.aggregate([
{
$lookup: {
from: "orders",
localField: "_id",
foreignField: "ProductId",
as: "requests.service"
}
},
{
$unwind: "$requests.service"
},
{
$group: {
_id: "$_id",
totalAmount: {
$sum: "$requests.service.Total"
}
}
}
])
https://mongoplayground/p/tgAoEjwP22D