如何在mongodb中使用嵌套组？
如何在mongodb中使用嵌套组？
提问于 2019-05-29 20:43:31
我有一个查询，其中我想对子文档进行分组。我试过一些例子，但它不能正常工作。
查询
db.getCollection("checklistCombination").aggregate([
{
"$lookup": {
"from": "Users",
"localField": "userId",
"foreignField": "_id",
"as": "user"
}
},
{
"$lookup": {
"from": "checklist",
"localField": "checklistId",
"foreignField": "_id",
"as": "linkChecklist"
}
},
{ "$unwind": "$linkChecklist" },
{
"$lookup": {
"from": "orderDetail",
"localField": "linkChecklist.product",
"foreignField": "productRangeId",
"as": "orderProduct"
}
},
{
"$unwind": { "path": "$orderProduct", "preserveNullAndEmptyArrays": true }
},
{
"$lookup": {
"from": "companysuppliers",
"localField": "orderProduct.supplierId",
"foreignField": "_id",
"as": "comapnySupplier"
}
},
{
"$unwind": {
"path": "$comapnySupplier",
"preserveNullAndEmptyArrays": true
}
},
{
"$lookup": {
"from": "suppliers",
"localField": "comapnySupplier.supplierId",
"foreignField": "_id",
"as": "supplier"
}
},
{ "$unwind": { "path": "$supplier", "preserveNullAndEmptyArrays": true } },
{
"$project": {
"_id": 1,
"name": 1,
"user": 1,
"linkChecklist": 1,
"orderProduct": 1,
"orderProductStatusIndex": {
"$ifNull": ["$orderProduct.statusIndex", "0"]
},
"comapnySupplier": 1,
"supplier": 1
}
},
{ "$match": { "orderProductStatusIndex": "0" } },
{
"$group": {
"_id": "$_id",
"name": { "$first": "$name" },
"supplier": {
"$push": {
"supplierId": "$supplier._id",
"supplierName": "$supplier.name",
"items": { "$sum": "$orderProduct.quantity" }
}
}
}
}
])此查询在结果下方返回
[{
"_id" : ObjectId("5cee224b97e765079c8c2839"),
"name" : "Dairy",
"supplier" : [
{
"supplierId" : ObjectId("5cee12a7a01ad50f5c2229ac"),
"supplierName" : "Bhagwandas Bherumal",
"items" : 10
}
]
},
{
"_id" : ObjectId("5cee1a19a01ad50f5c2229f2"),
"name" : "dairy/fruit",
"supplier" : [
{
"supplierId" : ObjectId("5cee12a7a01ad50f5c2229ac"),
"supplierName" : "Bhagwandas Bherumal",
"items" : 55
},
{
"supplierId" : ObjectId("5cee11f7a01ad50f5c2229a2"),
"supplierName" : "Agron India PVT. LTD",
"items" : 55
},
{
"supplierId" : ObjectId("5cee12a7a01ad50f5c2229ac"),
"supplierName" : "Bhagwandas Bherumal",
"items" : 10
}
]
}]在结果中，您可以看到Bhagwandas Bherumal在乳制品/水果(在数组索引1中)中有两个不同的结果。我想要按此字段分组并对其项目求和。
预期结果
[
{
"_id" : ObjectId("5cee224b97e765079c8c2839"),
"name" : "Dairy",
"supplier" : [
{
"supplierId" : ObjectId("5cee12a7a01ad50f5c2229ac"),
"supplierName" : "Bhagwandas Bherumal",
"items" : 10
}
]
},
{
"_id" : ObjectId("5cee1a19a01ad50f5c2229f2"),
"name" : "dairy/fruit",
"supplier" : [
{
"supplierId" : ObjectId("5cee12a7a01ad50f5c2229ac"),
"supplierName" : "Bhagwandas Bherumal",
"items" : 65
},
{
"supplierId" : ObjectId("5cee11f7a01ad50f5c2229a2"),
"supplierName" : "Agron India PVT. LTD",
"items" : 55
}
]
}]回答 1
Stack Overflow用户
回答已采纳
发布于 2019-05-30 09:08:11
希望这能解决你的问题：
db.getCollection("checklistCombination").aggregate([
{
"$lookup": {
"from": "Users",
"localField": "userId",
"foreignField": "_id",
"as": "user"
}
},
{
"$lookup": {
"from": "checklist",
"localField": "checklistId",
"foreignField": "_id",
"as": "linkChecklist"
}
},
{ "$unwind": "$linkChecklist" },
{
"$lookup": {
"from": "orderDetail",
"localField": "linkChecklist.product",
"foreignField": "productRangeId",
"as": "orderProduct"
}
},
{
"$unwind": { "path": "$orderProduct", "preserveNullAndEmptyArrays": true }
},
{
"$lookup": {
"from": "companysuppliers",
"localField": "orderProduct.supplierId",
"foreignField": "_id",
"as": "comapnySupplier"
}
},
{
"$unwind": {
"path": "$comapnySupplier",
"preserveNullAndEmptyArrays": true
}
},
{
"$lookup": {
"from": "suppliers",
"localField": "comapnySupplier.supplierId",
"foreignField": "_id",
"as": "supplier"
}
},
{ "$unwind": { "path": "$supplier", "preserveNullAndEmptyArrays": true } },
{
"$project": {
"_id": 1,
"name": 1,
"user": 1,
"linkChecklist": 1,
"orderProduct": 1,
"orderProductStatusIndex": {
"$ifNull": ["$orderProduct.statusIndex", "0"]
},
"comapnySupplier": 1,
"supplier": 1
}
},
{ "$match": { "orderProductStatusIndex": "0" } },
{ $group : {
_id : {
_id : '$_id',
name : '$name',
supplierId : "$supplier._id",
supplierName : "$supplier.name"
},
items : { "$sum": "$orderProduct.quantity"}
}
},
{
$group : {
_id : '$_id._id',
name : { "$first": "$_id.name" },
supplier : {
"$push": {
"supplierId": "$_id.supplierId",
"supplierName": "$_id.supplierName",
"items" : '$items'
}
}
}
}
])页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56360970
复制相关文章
相似问题