Several count aggregation output in Mongo

user_15177192 0
Viewed 43

So I have a collection like this:

{
 'region': '1',
 'status': 'a',
 'sum': 100
}
{
 'region': '1',
 'status': 'b',
 'sum': 80
}
{
 'region': '2',
 'status': 'a',
 'sum': 20
}
{
 'region': '2',
 'status': 'a',
 'sum': 30
}

And the desired result must be like this:

[
  {
    'region': '1',
    'status_a': 1,
    'status_b': 1,
    'sum': 180
  },
  {
    'region': '2',
    'status_a': 2,
    'sum': 50
  }
]

Any suggestions? The number of regions as well as the number of statuses can vary and I need to always output the sum for the entire region despite statuses

P.S if you wanna be super specific with your answer, then region and status fields are ObjectId's, thanks in advice!

mongodb
1 Answers
  • $group by both region and status and count status and sum
  • $group by only region and $concat status_ and its value after converting object id to string using $toString
  • construct the array of status and statusCount in key-value format
  • $arrayToObject convert above converted array to object
  • $mergeObjects to merge required fields and status object
  • $replaceRoot to replace above merged document in root
db.collection.aggregate([
  {
    $group: {
      _id: { region: "$region", status: "$status" },
      statusCount: { $sum: 1 },
      sum: { $sum: "$sum" }
    }
  },
  {
    $group: {
      _id: "$_id.region",
      status: {
        $push: {
          k: { $concat: ["status_", { $toString: "$_id.status" }] },
          v: "$statusCount"
        }
      },
      sum: { $sum: "$sum" }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          { $arrayToObject: "$status" },
          {
            resion: "$_id",
            sum: "$sum"
          }
        ]
      }
    }
  }
])

Playground

user_89871280
Related