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.

One of the answers in the marked duplicates https://stackoverflow.com/a/14898039/122005 uses `$max` — chridam, Jan 21 '19 at 14:45
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? — FalcoGer, Jan 21 '19 at 14:48
@chridam the question has even better answer. Without `$unwind` **:-)** — Ashh, Jan 21 '19 at 15:56

score 8 · Accepted Answer · answered Jan 21 '19 at 15:10

8

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

answered Jan 21 '19 at 15:10

Saravana

12,647
2
39
57

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. – FalcoGer Jan 22 '19 at 07:37
awesome. too good. – Visakh Vijayan Dec 11 '21 at 10:20

score 7 · Answer 2 · answered Jan 21 '19 at 15:16

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

score 5 · Answer 3 · answered Jan 21 '19 at 15:55

You can use below aggregation

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

Find max element inside an array

3 Answers3

Linked