Return highest number in grouped data

Question

I have grouped some data by some conditions and get the expected result. However, I would like to only return the item with the highest number in each pool. If I only would like to have the highest one I could add .sort({ correctBets: -1 }).limit(1) to the end, but now I need one in each pool. And if two or more correctBets is the same in one pool I would like to return all those items. How would I do this?

const coupon = await CouponModel.aggregate([
    {
      $match: {
        eventId: { $in: eventIds },
      },
    },
    {
      $group: {
        _id: {
          userId: '$userId',
          pool: '$pool',
        },
        correctBets: {
          $sum: '$correctBets',
        },
      },
    },
  ])

This is the data I get returned from the aggregate.

[ 
  { _id: { userId: '5df344a1372f345308dac12a', pool: 'foo' },
    correctBets: 9 },
  { _id: { userId: '5dfb2db245011a390fec4f7d', pool: 'bar' },
    correctBets: 6 },
  { _id: { userId: '5df344a1372f345308dac12a', pool: 'bar' },
    correctBets: 5 },
  { _id: { userId: '5dfb2db245011a390fec4f7d', pool: 'foo' },
    correctBets: 11 } 
]

My expected result should look like this

[
  { _id: { userId: '5dfb2db245011a390fec4f7d', pool: 'foo' },
    correctBets: 11 },
  { _id: { userId: '5dfb2db245011a390fec4f7d', pool: 'bar' },
    correctBets: 6 }
]

Edit: the proposed duplicate question dont handle max values for each 'key' as in my case is two pools. Foo and bar.

Does this answer your question? [mongodb how to get max value from collections](https://stackoverflow.com/questions/32076382/mongodb-how-to-get-max-value-from-collections) — whoami - fakeFaceTrueSoul, Jan 29 '20 at 17:24
Unfortunately not as it would only return the highest of one pool. I need the highest in booth pools. The answer below did work. Thanks anyway! — GameAtrix1, Jan 30 '20 at 08:19

Valijon · Accepted Answer · 2020-01-30T09:54:01.227

1

You need to add extra stages. We need to calculate max correctBets for each pool field and store documents into temporal data field. Then we flatten data and check if any document stored inside data has the same correctBets with max value.

{
    $group: {
      _id: {
        pool: "$_id.pool"
      },
      data: {
        $push: "$$ROOT"
      },
      correctBets: {
        $max: "$correctBets"
      }
    }
},
{
    $unwind: "$data"
},
{
    $match: {
      $expr: {
        $eq: [
          "$correctBets",
          "$data.correctBets"
        ]
      }
    }
  },
{
    $replaceRoot: {
      newRoot: "$data"
    }
}

MongoPlayground

edited Jan 30 '20 at 09:54

answered Jan 29 '20 at 18:37

Valijon

12,667
4
34
67

Thanks! However, if two userIds in the same pool has the same amount of correctBets, the result includes only one of them. I would like to have both if they tie. – GameAtrix1 Jan 30 '20 at 08:32
@GameAtrix check it again please – Valijon Jan 30 '20 at 09:21
It returns all of them now in the playground? – GameAtrix1 Jan 30 '20 at 09:39
Yes, now it works as intended. Thank you very much! – GameAtrix1 Jan 30 '20 at 10:02

Return highest number in grouped data

1 Answers1