MongoDB - How do I add multiple aggregations in query

Question

I have the following query:

[
  {
    "$group": {
      "_id": "$Region",
      "Total Sales": {
        "$sum": "$Sales"
      },
      "Average Sales": {
        "$avg": "$Sales"
      }
    }
  }
]

This returns the response in the following format:

[
  {
    "_id": "Canada",
    "Total Sales": 66928.17,
    "Average Sales": 174.292109375,
  }
  .....
]

How do I refactor the query to get a response in the following format:

[{
    "_id": "Canada",
    "Sales":{"Total":66928.17, "Average":174.292109375},
  }
  ......
]

So far I've tried like this but it doesn't work:

{
  "$group": {
    "_id": "$Region",
    "Sales": {
      "Total":{
          "$sum": "$Sales"
      },
      "Average":{
          "$avg": "$Sales"
      }
    }
  }
}

Yong Shun · Accepted Answer · 2022-01-05 07:32:15Z

1

Use $project to decorate the output document(s).

db.collection.aggregate([
  {
    "$group": {
      "_id": "$Region",
      "total": {
        "$sum": "$Sales"
      },
      "average": {
        "$avg": "$Sales"
      }
    }
  },
  {
    $project: {
      "Sales": {
        "Total": "$total",
        "Average": "$average"
      }
    }
  }
])

Sample Mongo PLayground

answered Jan 5, 2022 at 7:32

Yong Shun

54.5k6 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sandeep.kgp Over a year ago

is it possible to achieve this in group stage itself so that we can query for multiple keys e.g Sum of Profit etc. $group would become very messy otherwise

Yong Shun Over a year ago

No, for each field (except _id) in $group, must be accumulator. Use $project to decorate desired output.

Collectives™ on Stack Overflow

MongoDB - How do I add multiple aggregations in query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related