这是我的文档的样子
{ code: "A2df", clicks: 7, countries: [{"country":"IN", clicks:5},{"country":"US", clicks:2}], domains: [{"domain":"a", clicks:4},{"country":"b", clicks:3}] }, { code: "B3ws", clicks: 11, countries: [{"country":"IN", clicks:6},{"country":"ND", clicks:5}], domains: [{"domain":"a", clicks:7},{"country":"c", clicks:4}] }, { code: "A2df", clicks: 5, countries: [{"country":"IN", clicks:2},{"country":"ND", clicks:3}], domains: [{"domain":"a", clicks:1},{"country":"c", clicks:4}] }...这就是我需要的:
{ code: "A2df", clicks: 12, countries: [ { "country": "IN", clicks: 7 }, { "country": "US", clicks: 2 }, { "country": "ND", clicks: 3 } ], domains: [ { "domain": "a", clicks: 5 }, { "country": "b", clicks: 3 }, { "country": "c", clicks: 4 } ] }我知道如何在多个查询中执行此操作.我可以对点击进行分组汇总和求和,展开数组,然后对它们进行分组,然后对它们求和.但是我想跳过对数据库发出三个请求然后合并三个结果的麻烦.
I know how to do this in multiple queries. I can do a group aggregation and sum for clicks, unwind the arrays and then group them and then sum them. But I want to skip the pain of making three requests to the database and then of merging the three results.
MongoDB中是否有一种方法可以在单个查询中完成所有这些操作.我无法更改文档的结构.任何帮助表示赞赏.即使无法在单个查询中完成此操作,也应通过任何建议来减轻痛苦. :)
Is there a way in MongoDB all of this can be done in a single query. I can't change the structure of the documents. Any help is appreciated. Even if this can't be done in a single query, any suggestions are appreciated to reduce the pain. :)
这是到目前为止我为使它在单个查询中起作用而尝试的方法:
This is what I have tried so far to get it work in a single query:
[ { $match: { date: { $gte: fromDate, $lt: toDate }, brand_id: brandId, type: "item" } }, { $unwind: "$countries" }, { $group: { _id: { code: "$code", }, clicks: { $sum: "$countries.clicks" }, countries: { $push: "$countries" } } }, { $unwind: "$domains" }, { $group: { _id: { code: "$code", }, clicks: { $sum: "$domains.clicks" }, domains: { $push: "$domains" } } } ]这将返回一个空数组,但是如果我只是第一次展开并分组,它将为我提供国家/地区所需的输出.这就是我要解决的问题,尝试在一个查询中获取所有内容.
This returns an empty array, but if I just do the first unwind and group it gives me the required output for countries. So that's what I am trying to solve, try and get everything in one query.
推荐答案db.test.aggregate([ // Summarize clicks and collect countries and domains for each code { $group: { _id: "$code", clicks: { $sum: "$clicks" }, countries: { $push: "$countries" }, domains: { $push: "$domains" }, } }, // Unwind array of array of sub-countries { $unwind: "$countries" }, // Unwind each array of sub-countries { $unwind: "$countries" }, // Summarize clicks for each sub-country { $group: { _id: { code: "$_id", country: "$countries.country"}, clicks: { $min: "$clicks" }, // Keep clicks which we've summarized into the 1st $group countryClick: { $sum: "$countries.clicks" }, domains: { $first: "$domains" }, // Keep domains } }, // Collect sub-countries into embedded document { $group: { _id: "$_id.code", clicks: { $min: "$clicks" }, // Keep clicks which we've summarized into the 1st $group countries: { $push: { country: "$_id.country", clicks: "$countryClick" } }, domains: { $first: "$domains" }, // Keep sub-domains } }, // Unwind array of array of sub-domains { $unwind: "$domains" }, // Unwind each array of sub-domains { $unwind: "$domains" }, // Summarize clicks for each sub-domain { $group: { _id: { code: "$_id", domain: "$domains.domain", country: "$domains.country" }, clicks: { $min: "$clicks" }, // Keep clicks which we've summarized into the 1st $group domainClick: { $sum: "$domains.clicks" }, countries: { $first: "$countries" }, // Keep sub-countries } }, // Collect sub-domains into embedded document { $group: { _id: "$_id.code", clicks: { $min: "$clicks" }, // Keep clicks which we've summarized into the 1st $group domains: { $push: { domain: "$_id.domain", country: "$_id.country", clicks: "$domainClick" } }, countries: { $first: "$countries" }, // Keep sub-countries } }, ]).pretty()
更多推荐
MongoDB聚合展开多个数组
发布评论