Find max element inside an array

Question

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.

Fromm

One of the answers in the marked duplicates https://mcmap.net/q/1008044/-finding-highest-value-from-sub-arrays-in-documents uses $max – Estaestablish

that answer gets the maximum value of K and the maximum value of V, but they're unrelated. In my example if there was, say a height attribute for the people to give their height, it would return the greatest height and the greatest age, but they may not be related to the same person (entry in the array.) Or did I get something wrong there? – Fromm

@Estaestablish the question has even better answer. Without $unwind :-) – Hassanhassell

Answer 1

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

Deil

This has the most flexibility out of the answers so far. As I need to filter the age as well (between min and max), this works out for me. – Fromm

awesome. too good. – Anet

Answer 2

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

Secunderabad

Answer 3

You can use below aggregation

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

Hassanhassell

Answer 4

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

Schach