MongoDB aggregation filter based on max value

Related searches

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

What about this one:

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

$max (aggregation) — MongoDB Manual, Null or Missing Values�. If some, but not all, documents for the $max operation have either a null value for the� Definition¶ $max¶. Returns the maximum value. $max compares both value and type, using the specified BSON comparison order for values of different types. $max is

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

MongoDB Aggregation Query: Getting All Records having Max Value, MongoDB Aggregation Query: Getting All Records having Max Value query using MongoDB aggregate method to get all records having Max (Highest) value for specific field. MongoDB Query: Select and Filter Child Array Aggregation operations group values from multiple documents together, and can perform a variety of operations on the grouped data to return a single result. In SQL count(*) and with group by is an equivalent of MongoDB aggregation. The aggregate() Method. For the aggregation in MongoDB, you should use aggregate() method. Syntax

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

How to match and group array elements with the max value in , How to match and group array elements with the max value in MongoDB aggregation? MongoDBDatabaseBig Data Analytics. For this, use $� Keep in mind that this has the limitation of not exceeding the BSON size limit of 16MB, so is okay for small data samples, but anything producing a potentially large list ( since you cannot pre-filter array content ) would be better of processed with a separate query to find the "max" values, and another to fetch the matching documents.

$max (aggregation) — MongoDB Manual 3.4, Null or Missing Values�. If some, but not all, documents for the $max operation have either a null value for the� The query has returned both documents. If we look carefully at the document where _id field equals 1, our requirements are fulfilled in one of the subdocuments of the array, while in the document which _id field equals 2, only one condition is matched in each of the subdocuments of the array.

Finding max value in an array of sub-documents, Finding max value in an array of sub-documents db.test.aggregate([ in: {$ filter:{ For other MongoDB technical support options, see:� The Compass Filter supports using the mongo shell mode representation of the MongoDB Extended JSON BSON data types. Example The following filter returns documents where start_date is greater than than the BSON Date 2017-05-01 :

<field>: <aggregation expression> Specifies the value of the projected field. Starting in MongoDB 4.4, with the use of aggregation expressions and syntax, including the use of literals and aggregation variables, you can project new fields or project existing fields with new values. For example,

Comments
  • This produces the error "Unrecognized pipeline stage name: '$set'".
  • $set is new in 4.2. Try $addField instead
  • Yeah I figured, ended up doing that. Thanks!
  • Have a look on this Url - mongoplayground.net/p/0wu_q4kjYjo
  • This sorts by A - you get {A: "wer", B: 124} because of A: "wer", not due to B: 124
  • 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.
  • Good catch. Updated to be "$X.B". As you pointed out, that only returns the maximum value and not the entire object.