如何在MongoDB中对两个集合的值进行$sum

Question

我的英语不好，希望你能理解我的问题。我是新手，我尝试使用2个集合中的一些值。

我有两个收藏品：

集合1:

{
_id: "61241ad82fcb9a369e59868a",
"name": "aaaa",
"products": [{
"_id": "611803232eeaf825336e26c4",
"name": "products1",
"pricePolicy": { "costPrice": 10 }
}, 
{
"_id": "611803232eeaf825336e26c5",
"name": "products2",
"pricePolicy": { "costPrice": 10 }
}]
}, 
{
_id: "61241ad82fcb9a369e59868b",
"name": "bbb",
"products": [{
"_id": "611803232eeaf825336e26c4",
"name": "products1",
"pricePolicy": { "costPrice": 20 }
}, 
{
"_id": "611803232eeaf825336e26c5",
"name": "products2",
"pricePolicy": { "costPrice": 10 }
}]
}

和集合2:

{
"_id": "61179c8d4ef00f31df195223",
"name": "zzzz",
"listProduct": [
{
"id": "611803232eeaf825336e26c4",
"name": "products1",
"costPrice": 100,
},
{
"id": "611803232eeaf825336e26c6",
"name": "products3",
"costPrice": 10,
}
]
},

如何对两个集合中的costPrice进行$sum并按值对产品进行排序？结果如下：

{
_id: 611803232eeaf825336e26c4,
name: products1,
totalCollection1: 30,
totalCollection2: 10,
total: 40
}
{
_id: 611803232eeaf825336e26c5,
name: products2,
totalCollection1: 20,
totalCollection2: 0,
total: 20
}
{
_id: 611803232eeaf825336e26c6,
name: products3,
totalCollection1: 0,
totalCollection2: 10,
total: 10
}

Answer 1

我尝试了这个聚合查询，它按要求工作。我使用了你的问题帖子中的数据，结果在底部：

db.coll1.aggregate([
{ $unwind: "$products" },
{ $sort: { "products._id": 1 } },
{ $group: { 
_id: "$products._id", 
productName1: { $first: "$products.name" }, 
total1: { $sum: "$products.pricePolicy.costPrice" } 
} },
{ $group: { _id: null, productSummary1: { $push: "$$ROOT" } } },
{ $lookup: {
from: "coll2",
pipeline: [],
as: "products2"
}},
{ $unwind: "$products2" },
{ $unwind: "$products2.listProduct" },
{ $sort: { "products2.listProduct.id": 1 } },
{ $group: {
_id: "$products2.listProduct.id", 
productSummary1: { $first: "$productSummary1" },
productName2: { $first: "$products2.listProduct.name" }, 
total2: { $sum: "$products2.listProduct.costPrice" }
}},
{ $group: { _id: null, 
productSummary1: { $first: "$productSummary1" }, 
productSummary2: { $push: { "_id": "$_id", "productName1": "$productName2", "total2": "$total2" } }
}},
{ $project: { products: { $concatArrays: [ "$productSummary1", "$productSummary2" ] } } },
{ $unwind: "$products" },
{ $group: { 
_id: "$products._id", 
productName: { $first: "$products.productName1" },
total1: { $sum: "$products.total1"  },
total2: { $sum: "$products.total2" },
total1a: { $push: "$products.total1" },
total2a: { $push: "$products.total2" },
}},
{ $project: { productName: 1, total1: 1, total2: 1, total: { $sum: { $concatArrays: [ "$total1a", "$total2a" ] } } } },
{ $sort: { total: -1 } }
])

输出：

{
"_id" : "611803232eeaf825336e26c4",
"productName" : "products1",
"total1" : 30,
"total2" : 100,
"total" : 130
}
{
"_id" : "611803232eeaf825336e26c5",
"productName" : "products2",
"total1" : 20,
"total2" : 0,
"total" : 20
}
{
"_id" : "611803232eeaf825336e26c6",
"productName" : "products3",
"total1" : 0,
"total2" : 10,
"total" : 10
}

Answer 2

• $unwind解构products数组
• 通过null构造$group，并构造具有所需字段的products数组
• $lookup与集合2
  • $unwind解构listProduct数组
  • $project显示必填字段
• $project将两个集合数组连接到单个字段products
• $unwind解构上述products阵列
• $group乘以_id表示产品id，并获得两个集合的价格总和
• $ifNull检查值是否为空，然后返回0
• `$addFields添加新字段合计以获得两个集合价格的合计
• $sort(按降序排列)

db.col1.aggregate([
{ $unwind: "$products" },
{
$group: {
_id: null,
col1: {
$push: {
_id: "$products._id",
name: "$products.name",
totalCollection1: "$products.pricePolicy.costPrice"
}
}
}
},
{
$lookup: {
from: "col2",
pipeline: [
{ $unwind: "$listProduct" },
{
$project: {
_id: "$listProduct.id",
name: "$listProduct.name",
totalCollection2: "$listProduct.costPrice"
}
}
],
as: "col2"
}
},
{ $project: { products: { $concatArrays: ["$col1", "$col2"] } } },
{ $unwind: "$products" },
{
$group: {
_id: "$products._id",
name: { $first: "$products.name" },
totalCollection1: { $sum: { $ifNull: ["$products.totalCollection1", 0] } },
totalCollection2: { $sum: { $ifNull: ["$products.totalCollection2", 0] } }
}
},
{ $addFields: { total: { $sum: ["$totalCollection1", "$totalCollection2"] } } },
{ $sort: { total: -1 } }
])

游乐场