MongoDB Aggregation展开了多个阵列
这是我的文件的样子
{
code: "A2df",
clicks: 7,
countries: [{"country":"IN", clicks:5},{"country":"US", clicks:2}],
domains: [{"domain":"a.com", clicks:4},{"country":"b.com", clicks:3}]
},
{
code: "B3ws",
clicks: 11,
countries: [{"country":"IN", clicks:6},{"country":"ND", clicks:5}],
domains: [{"domain":"a.com", clicks:7},{"country":"c.com", clicks:4}]
},
{
code: "A2df",
clicks: 5,
countries: [{"country":"IN", clicks:2},{"country":"ND", clicks:3}],
domains: [{"domain":"a.com", clicks:1},{"country":"c.com", clicks:4}]
}...
这是我需要的：
{
code: "A2df",
clicks: 12,
countries: [
{
"country": "IN",
clicks: 7
},
{
"country": "US",
clicks: 2
},
{
"country": "ND",
clicks: 3
}
],
domains: [
{
"domain": "a.com",
clicks: 5
},
{
"country": "b.com",
clicks: 3
},
{
"country": "c.com",
clicks: 4
}
]
}
我知道如何在多个查询中做到这一点。 我可以为点击做一个群组聚合和求和，展开数组，然后对它们进行分组，然后对它们进行求和。 但我想避免向数据库发出三个请求并合并三个结果的痛苦。
在MongoDB中有没有一种方法可以在单个查询中完成。 我无法改变文件的结构。 任何帮助表示赞赏。 即使这不能在单个查询中完成，任何建议都可以减轻痛苦。 :)
这是我迄今为止试图让它在单个查询中工作的原因：
[
{
$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"
}
}
}
]
这将返回一个空数组，但如果我只是第一次放开并分组，它会为我提供所需的各国输出。 所以这就是我试图解决的问题，尝试在一个查询中获取所有内容。
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()