MongoDB aggregation - group by multiple keys with count for every level of grouping

Question

I have a flat document structure I'm trying to group together based on multiple keys. The structure of documents is like this

[{
  "_id": ObjectId("5f47eee763f55a095048f542"),
  "channel": "Z_C",
  "city": "A",
  "status": 0
},
.
.

Where city, channel can have different values and status can only either be 0 or 1. I'm trying to get a grouping result like this

[{
  "city": "A",
  "channels": [
  {

    "name": "Z_C",
    "counts":[
        {"status": 0,"count": 7},
        {"status": 1,"count": 2},
    ],
    "count": 9
  },
  {
    "name": "S_C",
    "counts":[
        {"status": 0, "count": 2},
        {"status": 1,"count": 9},
    ],
    "count": 11
  }
],
"count": 20
},
.
.

From the above result we can conclude that

1. City A has two channels Z_C and S_C
2. Z_C has 7 values with status of 0 and 2 values with status of 1, and total Z_C is 9
3. S_C has 2 values with status of 0 and 9 values with status of 1, and total S_C is 11
4. total count of values in city A are 20.

I don't need the result structure exactly as mentioned above as long as I can derive the 4 points from the result.

I've achieved this and here's my code on mongo playground to what I have so done far.

{
  "_id": {
    "city": "A"
  },
  "channels": [
    {
    "count": 7,
    "name": "Z_C",
    "status": 0
  },
  {
    "count": 2,
    "name": "S_C",
    "status": 0
  },
  {
    "count": 9,
    "name": "S_C",
    "status": 1
  },
  {
    "count": 2,
    "name": "Z_C",
    "status": 1
  }
],
"count": 20
}

How can I group the inner array of channels based on channel to get the expected result?

Thanks.

Answer 1

You can do with three groups,

• you are almost right in first group

db.collection.aggregate([
  {
    $group: {
      _id: {
        city: "$city",
        name: "$channel",
        status: "$status"
      },
      count: { $sum: 1 }
    }
  },

• just need to group with city and channel name because we need to prepare(construct) counts array for status

  {
    $group: {
      _id: {
        city: "$_id.city",
        name: "$_id.name"
      },
      counts: {
        $push: {
          count: "$count",
          status: "$_id.status"
        }
      },
      count: { $sum: "$count" }
    }
  },

• sort by city here

  { $sort: { "_id.city": 1 } },

• finally construct channels array

  {
    $group: {
      _id: "$_id.city",
      channels: {
        $push: {
          name: "$_id.name",
          counts: "$counts",
          count: "$count"
        }
      },
      count: { $sum: "$count" }
    }
  }
])

Playground