CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
FalcoGer
FalcoGer

Reputation: 2457

Find max element inside an array

REF: MongoDB Document from array with field value max

Answers in Finding highest value from sub-arrays in documents and MongoDB find by max value in array of documents suggest to use sort + limit(1), however this is really slow. Surely there is a way to use the $max operator.

Suppose one gets a document like this in an aggregate match:

{
  _id: "notImportant",
  array: [
    {
      name: "Peter",
      age: 17
    },
    {
      name: "Carl",
      age: 21
    },
    {
      name: "Ben",
      age: 15
    }
  ]
}

And you want to find the (entire, not just the one value) document where age is highest. How do you do that with the $max operator?

I tried

unwind {"$array"}
project {"_id": 0, "name": "$array.name", "age": "$array.age"}

so I get

{
  _id: null,
  name: "Peter",
  age: 17
}
{
  _id: null,
  name: "Carl",
  age: 21
}
{
  _id: null,
  name: "Ben",
  age: 15
}

Then I tried matching age:

age: {$eq: {$max: "$age"}}

, but that gives me no results.

In other words what I need to get is the name and all other fields that belong to the oldest person in the array. And there are many thousands of persons, with lots of attributes, and on top of it all it runs on a raspberry pi. And I need to do this operation on a few dozen of such arrays. So with the sorting this takes about 40 seconds all in all. So I would really like to not use sort.

Upvotes: 12

Views: 13303

Answers (4)

ray
ray

Reputation: 15217

With MongoDB v5.2+, you can use $sortArray to achieve the expected behaviour.

db.collection.aggregate([
  {
    "$match": {
      _id: "notImportant"
    }
  },
  {
    "$set": {
      "maxEntry": {
        $first: {
          $sortArray: {
            input: "$array",
            sortBy: {
              age: -1
            }
          }
        }
      }
    }
  }
])

Mongo Playground

With MongoDB v5.0+, you can first $unwind the array. Then, use $setWindowFields to compute $rank with sortBy of "array.age": -1. Select rank: 1 record (i.e. the record with max value of age) afterwards.

db.collection.aggregate([
  {
    "$match": {
      _id: "notImportant"
    }
  },
  {
    "$unwind": "$array"
  },
  {
    "$setWindowFields": {
      "sortBy": {
        "array.age": -1
      },
      "output": {
        "rank": {
          $rank: {}
        }
      }
    }
  },
  {
    "$match": {
      rank: 1
    }
  },
  {
    "$unset": "rank"
  }
])

Mongo Playground

Upvotes: 0

Ashh
Ashh

Reputation: 46441

You can use below aggregation

db.collection.aggregate([
  { "$project": {
    "max": {
      "$arrayElemAt": [
        "$array",
        {
          "$indexOfArray": [
            "$array.age",
            { "$max": "$array.age" }
          ]
        }
      ]
    }
  }}
])

Upvotes: 5

Elvis
Elvis

Reputation: 1143

If you want all the documents which have the highest value, you should use a filter. So basically, instead of using those unwind, project, etc, just use the below project stage

                $project: {
                    age: {
                        $filter: {
                            input: "$array",
                            as: "item",
                            cond: { $eq: ["$$item.age", { $max: "$array.age" }] }
                        }
                    }
                }

Upvotes: 8

Saravana
Saravana

Reputation: 12817

can you try this aggregation with $reduce

db.t63.aggregate([
    {$addFields : {array : {$reduce : {
        input : "$array", 
        initialValue : {age : 0}, 
        in : {$cond: [{$gte : ["$$this.age", "$$value.age"]},"$$this", "$$value"]}}
    }}}
])

output

{ "_id" : "notImportant", "array" : { "name" : "Carl", "age" : 21 } }

Upvotes: 8

Related Questions