在mongodb中将多个$ lookup与聚合一起使用 [英] Using multiple $lookup with aggregation in mongodb
本文介绍了在mongodb中将多个$ lookup与聚合一起使用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有三个收藏夹，
- _project-它包含所有项目
- _build-它包含所有内部版本，每个内部版本都必须属于一个项目
- _build.details-它包含必须属于一个广告集的广告，并且每个广告集必须属于一个广告系列，并且每个广告系列必须属于一个版本.
_project文档结构:
_project document structure:
{
"_id" : ObjectId("58d8c501be2bee2bc0b3b081"),
"CreatedBy" : ObjectId("58c801c606f72508d87421c6"),
....
....
},...
_build文档结构:
_build document structure:
{
"_id" : ObjectId("58ee6c5e06f7254454a554fe"),
"ModifiedBy" : ObjectId("58d93a98c6e7dbe94582619a"),
"CreatedBy" : ObjectId("58d93a98c6e7dbe94582619a"),
"ProgramId" : ObjectId("58c801b706f72508d87421c4"),
"ProjectId" : ObjectId("58d9411ebe2bee43ec22aff6"),
....
....
},...
_build.detials文档结构:
_build.detials document structure:
{
"_id" : ObjectId("58de834cc6e7dbe945acf890"),
"BuildId" : ObjectId("58ef4b95c6e7dbe945ba700b"),
"Values" : null,
"Headers" : null,
"Data" : {
"Campaign Name" : "Remarketing | Remarketing | Facebook | Conversions | 03-01-2017",
"Ad Set Name" : "Cancelled Orders_Greater than 50%-Cancelled Orders_Less than 50% | Desktop | Feed | Female | 21-65",
"Ad Name" : "Carousel | Draw1,Excited2,Lottery5,Beach4 | S:1814082498827964 | 03-01-2017 | 70Custom Audiences | ",
"Ad Set Run Status" : "ACTIVE",
"Ad Status" : "ACTIVE",
"Campaign Objective" : "Conversions",
"Gender" : "Female",
"Age Min" : "21",
"Age Max" : "65",
},
"Status" : false,
"CampaignName" : "Remarketing | Remarketing | Facebook | Conversions | 03-01-2017",
"AdSetName" : "Cancelled Orders_Greater than 50%-Cancelled Orders_Less than 50% | Desktop | Feed | Female | 21-65",
"AdName" : "Carousel | Draw1,Excited2,Lottery5,Beach4 | S:1814082498827964 | 03-01-2017 | 70Custom Audiences | ",
"Campaign_Status" : 1,
"Campaign_Id" : "1",
"Adset_Status" : 1,
"Adset_Id" : "123",
"Ad_Status" : 1,
"Ad_Id" : "1234"
},...
我要查询的内容:
我想从_project集合中获取所有项目，并希望针对每个项目进行构建，然后我希望针对每个项目和构建组合进行广告，广告集和广告系列及其计数.
I want to get all projects from _project collection and want builds against each of the project and then i want ads, adsets and campaigns and their count against each of the project and build combination.
想要输出类似:
{
"ProjectId" : ObjectId("58d8c501be2bee2bc0b3b081"),
"BuildId" : ObjectId("5a5ads501basdadsc0b3b346"),
"uniqueAdsCount" : "25",
"uniqueAdsetCount" : "5",
"uniqueCampaignCount" : "2",
"uniqueAdNames":[
],
"uniqueAdSetNames":[
],
"uniqueCampaignNames":[
]
},
{
next project
},
{
next project
}...
到目前为止我做了什么:
What i have done so far:
db.getCollection('_build').aggregate([
{
$lookup:{
from: "_project",
localField: "ProjectId",
foreignField: "_id",
as: "result"
}
},
{
$project:{
_id: 0,
BuildId: "$_id",
ProjectId: "$result._id"
}
},
{
$lookup:{
from: "_build.detail",
localField: "BuildId",
foreignField: "BuildId",
as: "resultS2"
}
},
{
$project:{
BuildId: "$BuildId",
ProjectId: "$ProjectId",
adsCount: {$size: "$resultS2.AdName"},
adSetCount: {$size: "$resultS2.AdSetName"},
campaignCount: {$size: "$resultS2.CampaignName"},
}
}
]);
我尝试添加一个小组舞台并使用$ addToSet，但是它给了我广告集中的重复项，我不知道我做错了什么:
I have tried adding a group stage and use $addToSet but its giving me duplicates in adsets, i don't know what i am doing wrong:
db.getCollection('_build').aggregate([
{
$lookup:{
from: "_project",
localField: "ProjectId",
foreignField: "_id",
as: "result"
}
},
{
$project:{
_id: 0,
BuildId: "$_id",
ProjectId: "$result._id"
}
},
{
$lookup:{
from: "_build.detail",
localField: "BuildId",
foreignField: "BuildId",
as: "resultS2"
}
},
{
$project:{
BuildId: "$BuildId",
ProjectId: "$ProjectId",
adsCount: {$size: "$resultS2"},
ads: "$resultS2"
}
},
{
$group:{
_id: "$BuildId",
UniqueAds: {$addToSet: "$ads.Data.Ad Name"},
UniqueAdsets: {$addToSet : "$ads.Data.Ad Set Name"}
}
}
]);
请帮助，如有任何疑问，请通知我，可能是我使用的是完全错误的方法，我是mongodb的新手.
Please help, and let me know in case of any queries, may be i am using a entirely wrong approach, i am new to mongodb.
提前谢谢！！
推荐答案
您可以使用以下汇总.
You can use the below aggregation.
您$group，在每个级别获得distinct名称值，然后在$addFields中获得$size.
You $group, get distinct name values at each level followed by $size in the $addFields.
db.getCollection('_project').aggregate([
{$lookup:{from: "_build",localField: "_id",foreignField: "ProjectId",as:"result"}},
{$unwind:"$result"},
{$project:{_id:0,ProjectId:"$_id",BuildId:"$result._id"}},
{$lookup:{from:"_build.detail",localField:"BuildId",foreignField:"BuildId",as:"resultS2"}},
{$unwind:"$resultS2"},
{$group:{
_id:{ProjectId:"$ProjectId", BuildId:"$BuildId",campaignName:"$resultS2.Data.Campaign Name",adSet:"$resultS2.Data.Ad Set Name"},
uniqueAdNames:{$addToSet:"$resultS2.Data.Ad Name"}
}
},
{$addFields:{uniqueAdsCount:{$size:"$uniqueAdNames" }}},
{$group :{
_id:{ProjectId:"$_id.ProjectId",BuildId:"$_id.BuildId",campaignName:"$_id.campaignName"},
uniqueAdNames:{$first:"$uniqueAdNames"},
uniqueAdsCount:{$first:"$uniqueAdsCount"},
uniqueAdSets:{$addToSet:"$_id.adSet"}
}
},
{$addFields:{uniqueAdsetCount:{$size:"$uniqueAdSets"}}},
{$group:{
_id:{ProjectId:"$_id.ProjectId",BuildId:"$_id.BuildId"},
uniqueAdNames:{$first:"$uniqueAdNames"},
uniqueAdsCount:{$first:"$uniqueAdsCount"},
uniqueAdSetNames:{$first:"$uniqueAdSets"},
uniqueAdsetCount:{$first:"$uniqueAdsetCount"},
uniqueCampaignNames:{$addToSet:"$_id.campaignName"}
}
},
{$addFields:{uniqueCampaignCount:{$size : "$uniqueCampaignNames"}}}
])
这篇关于在mongodb中将多个$ lookup与聚合一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
