在单个mongo查询和$ project结果中两次使用$ group [英] Use $group twice with in a single mongo query and $project result

问题描述

下面是我的mongodb集合中的三个文档，

Below given are the three documents from my mongodb collection,

[
{
    "_id" : "58ad8a35ef2bf403cc4750ff1",
    "Data" : {
        "Campaign Name" : "Campaign 1",
        "Ad Set Name" : "Adset 1.1",
        "Ad Name" : "Ad 1.1.1"
    }
},
{
    "_id" : "58ad8a35ef2bf403cc4750ff2",
    "Data" : {
        "Campaign Name" : "Campaign 1",
        "Ad Set Name" : "Adset 1.1",
        "Ad Name" : "Ad 1.1.2"
     }
},
{
    "_id" : "58ad8a35ef2bf403cc4750ff3",
    "Data" : {
        "Campaign Name" : "Campaign 1",
        "Ad Set Name" : "Adset 2",
        "Ad Name" : "Ad 1.2.1"
     }
}
]

注意：

Note:

我有很多我的文档中广告系列名称字段定义的广告系列，然后每个广告系列中都有多个Adsets（广告集名称），然后我在每个广告集（广告集名称）中拥有多个广告（广告名）意味着每个广告必须属于一个广告集（广告集名称），每个广告集（广告集名称）都必须属于广告系列（广告系列名称）

I have various Campaigns defined by "Campaign Name" field in my document, and then i have multiple Adsets(Ad Set Name) in each of the campaign, and then i have multiple ads(Ad Name) inside every adset(Ad Set Name) means every ad must belongs to one adset(Ad Set Name) and every adset(Ad Set Name) must belongs to a campaign(Campaign Name)

到目前为止我已经成功完成了什么：

What I have done so far successfully:

db.adsets.aggregate([
    {
        $group:{
            _id:"$Data.Campaign Name",
            CampaignCount : {$sum:1},
            UniqueAdsets: {$addToSet : "$Data.Ad Set Name"}
        }
    },
    {
        $project:{
            _id:1,
            CampaignCount: 1,
            UniqueAdsets: 1,
            UniqueAdsetCount:{$size:"$UniqueAdsets"}
        }
    }
]);

我已经针对每个广告系列找到了独特的广告系列（广告系列名称）和adsets（广告集名称）唯一的广告系列及其数量，上面的查询给了我这个结果，

I have already found out unique campaigns(Campaign Name) and adsets(Ad Set Name) against each of the unique campaign and their count, above query gives me this result,

[
{
    "_id" : "Campaign 1",
    "CampaignCount" : 60.0,
    "UniqueAdsets" : [ 
        "Adset 1", 
        "Adset 2", 
        "Adset 3", 
        "Adset 4"
    ],
    "UniqueAdsetCount" : 4
},
{
    "_id" : "Campaign 2",
    "CampaignCount" : 60.0,
    "UniqueAdsets" : [ 
        "Adset 1", 
        "Adset 2"
    ],
    "UniqueAdsetCount" : 2
}
]

我正在尝试修改此查询，以便给我Ads（广告名称），然后在我在现有查询中找到的每个adset（广告集名称）中都包含广告。
我想要这样的东西作为输出，

I am trying to modify this query so it could given me Ads(Ad Name) and ads count inside every adset(Ad Set Name) that i found in my existing query. I want something like this as an output,

[
{
    "_id" : "Campaign 1",
    "CampaignCount" : 60.0,
    "UniqueAdsets" : [ 
        {"Adset 1":["ad1","ad2","ad3"],"adcount":3}, 
        {"Adset 2":["ad1","ad2"],"adcount":2}, 
        {"Adset 3":["ad1","ad2"],"adcount":2}, 
        {"Adset 4":["ad1","ad2"],"adcount":2}
    ],
    "UniqueAdsetCount" : 4
},
{
    "_id" : "Campaign 2",
    "CampaignCount" : 60.0,
    "UniqueAdsets" : [ 
        {"Adset 2":["ad1","ad2"],"adcount":2}, 
        {"Adset 2":["ad1","ad2"],"adcount":2}
    ],
    "UniqueAdsetCount" : 2
}
]

我现在正在尝试此操作，但它不起作用

I am trying this right now, but its not working,

db.adsets.aggregate([
    {
        $group:{
            _id:{"CampaignName":"$Data.Campaign Name"},
            CampaignCount : {$sum:1},
            UniqueAdsets: {$addToSet : "$Data.Ad Set Name"}
        }
    },
    {
        $group:{
            _id:{"AdsetName":"$Data.Ad Set Name"},
            UniqueAds: {$addToSet : "$Data.Ad Name"}
        }
    },
    {
        $project:{
            _id:0,
            CampaignName:"$_id.CampaignName",
            CampaignCount: 1,
            UniqueAdsets: 1,
            UniqueAdsetCount:{$size:"$UniqueAdsets"},
            UniqueAds:1,
            UniqueAds:{$size:"$UniqueAds"}
        }
    }
]);

推荐答案

您可以尝试以下汇总。

$ group ：按广告系列名称分组以计算广告系列，并 $ push 将所有广告集放入 Adsets 数组。

$group : Group by Campaign Name to count the campaign and $push all the adsets into Adsets array.

$ unwind ：展开广告集数组

$ group ：按 AdSetName 和 $ push 将所有广告名分组为 AdName 数组并计算广告集。

$group : Group by AdSetName and $push all the adnames into AdName array and count the adsets.

$ group ：由广告系列名称组成的最终小组，以便将所有内容恢复为所需的响应。

$group : Final Group by Campaign Name to put everything back into desired response.

$ project ：投影所有需要的字段，并为广告集添加 $ size

$project : Project all the desired fields and add the $size for adsets

db.adsets.aggregate([{
    $group: {
        _id: "$Data.Campaign Name",
        CampaignCount: {
            $sum: 1
        },
        Adsets: {
            $push: {
                AdSetName: "$Data.Ad Set Name",
                AdName: "$Data.Ad Name"
            }
        }
    }
}, {
    $unwind: "$Adsets"
}, {
    $group: {
        _id: "$Adsets.AdSetName",
        CampaignCount: {
            $first: "$CampaignCount"
        },
        CampaignName: {
            $first: "$_id"
        },
        AdsetCount: {
            $sum: 1
        },
        AdName: {
            $push: "$Adsets.AdName"
        }
    }
}, {
    $group: {
        _id: "$CampaignName",
        CampaignCount: {
            $first: "$CampaignCount"
        },
        AdSets: {
            $push: {
                AdSetName: "$_id",
                AdName: "$AdName",
                AdsetCount: "$AdsetCount"
            }
        }
    }
}, {
    $project: {
        _id: 1,
        CampaignCount: 1,
        AdSets: 1,
        AdsetCount: {
            $size: "$AdSets"
        }
    }
}]);

这篇关于在单个mongo查询和$ project结果中两次使用$ group的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！