Mongodb using $max with $match-CodePudding

I need to get the document (or documents) with the max value in a field, in second stage, after grouping (first, I group, then, I need to get the one with max). If I only needed one, I could use 'limit(1)', but there could be many documents tied in first place.

I need something like this:

db.sales.aggregate(
  [
    // First Stage
    {
      $group :
        {
          _id : "$item",
          totalSaleAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } }
        }
     },
     // Second Stage
     {
       $match: { "totalSaleAmount": { $max: $totalSaleAmount } }
     }
   ]
 )

This does not work, as $match does not allow use of $max, except if is included in an expression. I'm not sure how to acomplish this.

Could you help me, please?

CodePudding user response：

Query

in MongoDB 5 , we have can use $setWindowFields
we can sort for example descending of totalSaleAmount
$denseRank to add one extra field to keep the order number, dense-rank will give the same rank if field same value ($rank doesn't do this)

Playmongo

aggregate(
[{"$setWindowFields": 
   {"output": {"dense-rank": {"$denseRank": {}}},
    "sortBy": {"totalSaleAmount": -1}}},
 {"$match": {"$expr": {"$eq": ["$dense-rank", 1]}}},
 {"$unset": ["denser-rank"]}])