CODEWITHSUNDEEP
mongodbaggregation-framework
bluepuma77
bluepuma77

Reputation: 521

How to $count and $group within MongoDB aggregation?

I would like to count the status and group them by country.

Data:

[
    { id: 100, status: 'ordered', country: 'US', items: [] },
    { id: 101, status: 'ordered', country: 'UK', items: [] },
    { id: 102, status: 'shipped', country: 'UK', items: [] },
]

Desired aggregation outcome:

[
    { _id: 'US', status: { ordered: 1} },
    { _id: 'UK', status: { ordered: 1, shipped: 1 } }
]

I can $count and $group, but I am not sure how to put this together. Any hint is appreciated.

Thanks, bluepuama

Upvotes: 1

Views: 174

Answers (2)

Tom Slabbaert
Tom Slabbaert

Reputation: 22316

You can do it with a single $group stage like so:

db.collection.aggregate([
  {
    $group: {
      _id: "$country",
      "shipped": {
        $sum: {
          $cond: [
            {
              $eq: [
                "$status",
                "ordered"
              ]
            },
            0,
            1
          ]
        }
      },
      "ordered": {
        $sum: {
          $cond: [
            {
              $eq: [
                "$status",
                "shipped"
              ]
            },
            0,
            1
          ]
        }
      }
    }
  },
  {
    $project: {
      _id: 1,
      status: {
        shipped: "$shipped",
        ordered: "$ordered"
      }
    }
  }
])

Mongo Playground

Upvotes: 0

turivishal
turivishal

Reputation: 36144

  • $group by country and status, and count total
  • $group by only country and construct array of status and count in key-value format
  • $set to update status field to object using $arrayToObject
db.collection.aggregate([
  {
    $group: {
      _id: { country: "$country", status: "$status" },
      count: { $sum: 1 }
    }
  },
  {
    $group: {
      _id: "$_id.country",
      status: { $push: { k: "$_id.status", v: "$count" } }
    }
  },
  { $set: { status: { $arrayToObject: "$status" } } }
])

Playground

Upvotes: 1

Related Questions