CODEWITHSUNDEEP
mongodbaggregation-framework
CHRD
CHRD

Reputation: 1957

MongoDB aggregation filter based on max value

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
    }
]

Upvotes: 1

Views: 2413

Answers (3)

Mohammad Faisal
Mohammad Faisal

Reputation: 2402

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

Upvotes: 1

Lauren Schaefer
Lauren Schaefer

Reputation: 706

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"}
    }}]

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

What about this one:

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

Upvotes: 2

Related Questions