This is my mongo event data in the events collection:

[{country: "USA", Region: "CA", City: "Los Angeles"},
{country: "USA", Region: "NY", City: "New York"},
{country: "USA", Region: "NY", City: "White Plains"},
{country: "France", Region: "Ile-de-France", City: "Paris"},
{country: "France", Region: "Bretagne", City: "Brest"},
{country: "Germany", Region: "", City: "Berlin"}]

If possible I would like to show event summed by Country and region. The desired output format would be:

[
 {country: "USA", count: 3, 
  children: [{Region: "NY", count: 2}, {Region: "CA", count: 1}]},
 {country: "France", count: 2, 
  children: [{Region: "Ile-de-France", count: 1}, {Region: "Bretagne", count: 1}]},
 {country: "Germany", count: 1, 
 children: [ // Region undefined]}
]

Here is what I tried:

events.aggregate([
 {
  $group: {
   _id: '$country', count: {$sum: 1}
  }
 }, {
  $group: {
   _id: '$_id.country',
   children: {
    $push: {
     Region: '$_id.Region',
     count: {$sum: 1}
    }
   }
]

The first stage grouping and counting the country works on its own. The issue is summing the Region parameter. I even removed the $sum function from the first stage. All I am getting is: [{_id: null, children : [{count:1}, {count:1}, {count:1},{count:1},...]

Any advice would be appreciated!

CodePudding user response：

1. $group - Group by country and Region.

2. $group - Group by country.

db.collection.aggregate([
  {
    $group: {
      _id: {
        country: "$country",
        Region: "$Region"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: "$_id.country",
      children: {
        $push: {
          Region: "$_id.Region",
          count: "$count"
        }
      }
    }
  }
])

Sample Mongo Playground