Group data according to field value instead of field name

MongoDB Atlas MongoDB Charts
,
1

Hi everyone,

I’m pretty to new aggregation with MongoDB and I’m having some mental breakdown over something.

I’m trying to aggregate some data and I’m 99% satisfied with I’ve been able to achieve so far.
Iv’e got a field named status that can have 3 different values

  • CANCELED
  • WON
  • LOST

I’m able to group my data according to these 3 status. But what I want, is to group my data that have a status CANCELED and group the rest of the data WON & LOST together

For example, here’s what I get right now

[{
  "_id": {
    "operatorBrandId": "0742589f-6e45-4f49-9a84-cbb74eba8320",
    "status": "CANCELED"
  },
  "betAmount": 300,
  "countBets": 1
},{
  "_id": {
    "operatorBrandId": "0742589f-6e45-4f49-9a84-cbb74eba8320",
    "status": "LOST"
  },
  "betAmount": 3450600,
  "countBets": 9430
},{
  "_id": {
    "operatorBrandId": "0742589f-6e45-4f49-9a84-cbb74eba8320",
    "status": "WON"
  },
  "betAmount": 321900,
  "countBets": 691
}]

I would like to group WON & LOST together so I end up with a betAmount of 3 772 500 and a countBets of 10 121.

Here’s my pipeline

[{
 $match: {
  dateOfDemand: {
   $gte: ISODate('2022-10-01T00:00:00.000Z'),
   $lt: ISODate('2022-10-31T23:59:59.999Z')
  },
  operatorBrandId: {
   $in: [
    '0742589f-6e45-4f49-9a84-cbb74eba8320',
    '24ccd712-ad7a-47ca-b959-b64f7a5249be',
    '225fa4b2-ea62-4922-8e53-67d54d6e3a8c',
    'a3a0993a-6cca-4b97-b722-278339ba7550',
    'd3739f59-d652-45db-b2ee-13592982a1b3'
   ]
  }
 }
}, {
 $group: {
  _id: {
   operatorBrandId: '$operatorBrandId',
   status: '$status'
  },
  betAmount: {
   $sum: '$amountTotal'
  },
  countBets: {
   $sum: 1
  }
 }
}, {
 $sort: {
  '_id.operatorBrandId': 1,
  '_id.status': 1
 }
}]

Thanks for the help !

2

Hey @Steven_Koralplay, are you using Charts? (This is the Charts forum after all!). You can accomplish this by enabling the Binning feature when a string field is in a category channel.

image

If you want to see the raw pipeline that this generated from your choices, you can see that in the View Aggregation Pipeline dialog. You can see we do this by creating a new field that has different values depending on the raw value.

image
image820×903 30.9 KB

HTH
Tom

1 Like
3

Hey

Thank you for your reply.

I wasn’t aware of such feature. I haven’t seen anything like that on Compass so far. I’ll have to dig into it and see if it can helps

4

This is in Charts, not in Compass.

1 Like