MongoDB aggregation filter based on max value

Question

Suppose I have a document structure where one of the fields, X, is an array av objects as shown below.

"X" : [ 
    {
      "A" : "abc",
      "B" : 123
    },
    {
      "A" : "wer",
      "B" : 124
    },
    {
      "A" : "fgh",
      "B" : 124
    }
]

How can I project only the document where field B has the highest values? And if the maximum value is shared by several documents, I just want to return one of them (not important which one). In this case the result could look like:

"X" : [ 
    {
      "A" : "wer",
      "B" : 124
    }
]

Wernfried Domscheit · Accepted Answer · 2020-02-13 13:02:31Z

2

What about this one:

db.collection.aggregate([
   {
      $set: {
         X: {
            $filter: {
               input: "$X",
               cond: { $eq: ["$$this.B", { $max: "$X.B" }] }
            }
         }
      }
   },
   { $set: { X: { $arrayElemAt: ["$X", 0] } } }
])

answered Feb 13, 2020 at 13:02

Wernfried Domscheit

60.5k10 gold badges93 silver badges132 bronze badges

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

3 Comments

CHRD Over a year ago

This produces the error "Unrecognized pipeline stage name: '$set'".

Wernfried Domscheit Over a year ago

$set is new in 4.2. Try $addField instead

CHRD Over a year ago

Yeah I figured, ended up doing that. Thanks!

Mohammad Faisal · Accepted Answer · 2020-02-15 07:56:55Z

1

You can use $reduce

  db.collection.aggregate([
      {
        "$project": {
          "X": {
            $reduce: {
              input: "$X",
              initialValue: {},
              in: {
                $cond: [ { "$gt": [ "$$this.B", "$$value.B" ]}, // Condition Check
                  "$$this",      // If condition true ($$this - Current Object)  
                  "$$value"      // If condition false $$value - Previous Returned Object
                ]
              }
            }
          }
        }
      }
    ])

Mongo Playground

edited Feb 15, 2020 at 7:56

answered Feb 13, 2020 at 12:43

Mohammad Faisal

2,4721 gold badge20 silver badges28 bronze badges

Comments

Lauren Schaefer · Accepted Answer · 2020-02-13 13:09:56Z

0

Updated answer:

Another option that results in the full object being returned at the end:


    [
    {$unwind: {
      path: "$X"
    }}, 
    {$sort: {
      "X.B": -1
    }}, 
    {$group: {
      _id: { _id: "$_id"},
      X: {
        $first: "$X"
      }
    }}]

Original answer:

You can use the $max operator (https://docs.mongodb.com/manual/reference/operator/aggregation/max/).

    [{$project: {
      X: {$max: "$X.B"}
    }}]

edited Feb 13, 2020 at 13:09

answered Feb 13, 2020 at 12:44

Lauren Schaefer

7164 silver badges9 bronze badges

4 Comments

Mohammad Faisal Over a year ago

Have a look on this Url - mongoplayground.net/p/0wu_q4kjYjo

Wernfried Domscheit Over a year ago

This sorts by A - you get {A: "wer", B: 124} because of A: "wer", not due to B: 124

CHRD Over a year ago

I think you'd want to change that to X: {$max: "$X.B"}. In any case, this will only return the maximum value. I want the whole object to be returned.

Lauren Schaefer Over a year ago

Good catch. Updated to be "$X.B". As you pointed out, that only returns the maximum value and not the entire object.

Collectives™ on Stack Overflow

MongoDB aggregation filter based on max value

3 Answers 3

3 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related