MongoDB Aggregation unwind more than one array

Question

This is what my documents look like

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

This is what I need:

{
  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.

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.

Please write your queries. As you can unwind more than one arrays in single aggregation pipeline. first unwind->group->count, then repeat it for another array in same pipeline. — عثمان غني
– عثمان غني, Commented Mar 22, 2015 at 14:03
okay let me try, I will then post the query as well as the output.. — Sambhav Sharma
– Sambhav Sharma, Commented Mar 22, 2015 at 14:04
I have added the query, my code doesn't work. Can you please suggest the correct way to do it? — Sambhav Sharma
– Sambhav Sharma, Commented Mar 22, 2015 at 14:44
I don't think there's a sane and totally correct way to do this with one pipeline. The issue is maintaining useful domains information as you group on (code, country), which spans different domains values. Use multiple aggregations. Also, make your data model sensible by counting all clicks on the appropriate code document, instead of having it split between multiple for no apparent reason - that's the root of the problem. — wdberkeley
– wdberkeley, Commented Mar 23, 2015 at 15:43
Well, that is a use case. I need total clicks, clicks by country and click by domain. This is an apparent reason for us. — Sambhav Sharma
– Sambhav Sharma, Commented Mar 23, 2015 at 17:50

Oleg · Accepted Answer · 2015-05-27 10:15:46Z

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()

Collectives™ on Stack Overflow

MongoDB Aggregation unwind more than one array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related