MongoDB聚合展开多个数组 [英] MongoDB Aggregation unwind more than one array

问题描述

这是我的文档的样子

{
  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
    }
  ]
}

我知道如何在多个查询中执行此操作.我可以对点击进行分组汇总和求和，展开数组，然后对它们进行分组，然后对它们求和.但是我想跳过对数据库发出三个请求然后合并三个结果的麻烦.

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聚合展开多个数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！