外部字段是对象数组时的MongoDB查找 [英] MongoDB lookup when foreign field is an array of objects
本文介绍了外部字段是对象数组时的MongoDB查找的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个集合 initiatives
和 resources
:
I have two collections initiatives
and resources
:
倡议文档示例:
{
"_id" : ObjectId("5b101caddcab7850a4ba32eb"),
"name" : "AI4CSR",
"ressources" : [
{
"function" : ObjectId("5c3ddf072430c46dacd75dbb"),
"participating" : 0.1,
},
{
"function" : ObjectId("5c3ddf072430c46dacd75dbc"),
"participating" : 5,
},
{
"function" : ObjectId("5c3ddf072430c46dacd75dbb"),
"participating" : 12,
},
{
"function" : ObjectId("5c3ddf072430c46dacd75dbd"),
"participating" : 2,
},
],
}
和一个资源文档:
{
"_id" : ObjectId("5c3ddf072430c46dacd75dbc"),
"name" : "Statistician",
"type" : "FUNC",
}
所以我想返回每个 resource 以及 participating 的总和.为此,我需要加入这两个集合.
so i want to return each resource with the sum of participating is have. and to that i need to join the two collection.
db.resources.aggregate([
{
"$match": { type: "FUNC" }
},
{
"$lookup": {
"from": "initiatives",
"localField": "_id",
"foreignField": "initiatives.resources",
"as": "result"
}
},
])
但首先我需要展开外部字段数组.
but first i need to unwind the foreign field array.
预期输出示例:
{
"function" : "Data Manager"
"participation_sum": 50
}
{
"function" : "Statistician"
"participation_sum": 1.5
}
{
"function" : "Supply Manage"
"participation_sum": 0
}
推荐答案
mongodb 3.6及以上可以使用下面的聚合
You can use below aggregation with mongodb 3.6 and above
db.resources.aggregate([
{ "$match": { "type": "FUNC" } },
{ "$lookup": {
"from": "initiatives",
"let": { "id": "$_id" },
"pipeline": [
{ "$match": { "$expr": { "$in": ["$$id", "$ressources.function"] } } },
{ "$unwind": "$ressources" },
{ "$match": { "$expr": { "$eq": ["$ressources.function", "$$id"] } } },
{ "$group": {
"_id": "$ressources.function",
"participation_sum": { "$sum": "$ressources.participating" }
}}
],
"as": "result"
}}
])
这篇关于外部字段是对象数组时的MongoDB查找的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文