126

I have a mongodb collection like:

db.kids.find()
//results
[
    {name:'tom', age:10},
    {name:'alice', age:12},
    ....
]

I need a query to get MAX 'age' from this collection like in SQL: SELECT MAX(age) FROM kids WHERE 1

Hossain Khademian
  • 3,291
  • 3
  • 12
  • 10

10 Answers10

199

As one of comments:

db.collection.find().sort({age:-1}).limit(1) // for MAX
db.collection.find().sort({age:+1}).limit(1) // for MIN

it's completely usable but i'm not sure about performance

Community
  • 1
  • 1
Hossain Khademian
  • 3,291
  • 3
  • 12
  • 10
  • 20
    In case of a large collection, it's better to define an index on `age` field. Then if you use `db.collection.find({}, {age: 1, _id:0}).sort({age:-1}).limit(1)`, you probably would have a very fast [Covered Query](https://docs.mongodb.com/v3.0/core/query-optimization/#covered-query) – Ali Dehghani Jun 02 '16 at 20:55
  • @AliDehghani Would this method works on mongo shards? – igonejack Apr 29 '19 at 09:17
  • For anyone searching for something related to the aggregation, You can use this in aggregation if you're using mongoose, as follows: `Model.aggregate([YOUR_QUERY]).sort({age:-1}).limit(1)` – Normal Nov 23 '22 at 22:55
107

The performance of the suggested answer is fine. According to the MongoDB documentation:

When a $sort immediately precedes a $limit, the optimizer can coalesce the $limit into the $sort. This allows the sort operation to only maintain the top n results as it progresses, where n is the specified limit, and MongoDB only needs to store n items in memory.

Changed in version 4.0.

So in the case of

db.collection.find().sort({age:-1}).limit(1)

we get only the highest element WITHOUT sorting the collection because of the mentioned optimization.

nevets1219
  • 7,692
  • 4
  • 32
  • 47
popolvar
  • 1,071
  • 1
  • 7
  • 2
  • 13
    that documentation link is for aggregation. Are you sure `find( ... ).sort( ... ).limit( ... )` is treated the same way as `aggregate([{$match: ... }, {$sort: ...}, {$limit: ...}])`? is there any place in the mongo docs that they mention this? – jmmut Apr 17 '19 at 13:10
  • 3
    @jmmut https://docs.mongodb.com/manual/reference/method/cursor.sort/#limit-results – Rafael Sofi-zada Feb 09 '21 at 20:04
38

what about using aggregate framework:

db.collection.aggregate({ $group : { _id: null, max: { $max : "$age" }}});
dier
  • 2,791
  • 2
  • 15
  • 8
  • 31
    This is not as efficient as the sort.limit. Still, I know deep down everyone feels weird about that sort and limit... – AFP_555 Mar 31 '17 at 03:16
  • @AFP_555 Really surprised to know that aggregate is slower than a sort-limit query. Thanks for sharing! – Nam G VU Jan 10 '18 at 02:23
  • 1
    is aggregate slower than sort-limit query? – ashusvirus Jan 20 '18 at 13:09
  • 1
    I do the simple test case. Create a collection with 1,000,000 documents {name: "player ", score: x}. The .find().sort({score:-1}).limit(1); take more time than .aggregate([{ $group : { _id: null, max: { $max : "$score" }}}]) – tuananh Jul 12 '18 at 06:24
  • 5
    @tuananh, this can happen if you don't have an index on "score". In this case sort will have to do O(n log n) operations, while aggregate will only do one scan O(n). With indexed field, sort(...).limit(1) will be very fast constant time O(1) operation. – cababunga Mar 29 '19 at 18:40
6

Folks you can see what the optimizer is doing by running a plan. The generic format of looking into a plan is from the MongoDB documentation . i.e. Cursor.plan(). If you really want to dig deeper you can do a cursor.plan(true) for more details.

Having said that if you have an index, your db.col.find().sort({"field":-1}).limit(1) will read one index entry - even if the index is default ascending and you wanted the max entry and one value from the collection.

In other words the suggestions from @yogesh is correct.

Thanks - Sumit

Andrii Abramov
  • 10,019
  • 9
  • 74
  • 96
Sumit S
  • 516
  • 5
  • 17
3
db.collection.findOne().sort({age:-1}) //get Max without need for limit(1)
Hisham
  • 1,279
  • 1
  • 17
  • 23
  • 10
    At least in Mongo 4.2, that syntax will get you a `TypeError: db.collection.findOne(...).sort is not a function`. collection.findOne() returns the document itself, so calling sort() on it seems unlikely to work. – Peter Hansen Jan 20 '20 at 04:51
2

Below query will get you max age without other fields,

db.collection.find({}, {"_id":0, "age":1}).sort({age:-1}).limit(1)

Below query will get you max age along with all the collection fields,

db.collection.find({}).sort({age:-1}).limit(1)
1

Simple Explanation, if you have mongo query Response something like below - and you want only highest value from Array-> "Date"

{
  "_id": "57ee5a708e117c754915a2a2",
  "TotalWishs": 3,
  "Events": [
    "57f805c866bf62f12edb8024"
  ],
  "wish": [
    "Cosmic Eldorado  Mountain Bikes, 26-inch (Grey/White)",
    "Asics Men's Gel-Nimbus 18 Black, Snow and Fiery Red Running Shoes - 10 UK/India (45 EU) (11 US)",
    "Suunto Digital Black Dial Unisex Watch - SS018734000"
  ],
  "Date": [
    "2017-02-13T00:00:00.000Z",
    "2017-03-05T00:00:00.000Z"
  ],
  "UserDetails": [
    {
      "createdAt": "2016-09-30T12:28:32.773Z",
      "jeenesFriends": [
        "57edf8a96ad8f6ff453a384a",
        "57ee516c8e117c754915a26b",
        "58a1644b6c91d2af783770b0",
        "57ef4631b97d81824cf54795"
      ],
      "userImage": "user_profile/Male.png",
      "email": "roopak@small-screen.com",
      "fullName": "Roopak Kapoor"
    }
  ],

},

***Then you have add

Latest_Wish_CreatedDate: { $max: "$Date"},

somthing like below-

{ 
                $project : { _id: 1,
                             TotalWishs : 1 ,
                              wish:1 ,
                               Events:1, 
                               Wish_CreatedDate:1,
                               Latest_Wish_CreatedDate: { $max: "$Date"},
                            } 
            } 

And Final Query Response will be below

{
  "_id": "57ee5a708e117c754915a2a2",
  "TotalWishs": 3,
  "Events": [
    "57f805c866bf62f12edb8024"
  ],
  "wish": [
    "Cosmic Eldorado  Mountain Bikes, 26-inch (Grey/White)",
    "Asics Men's Gel-Nimbus 18 Black, Snow and Fiery Red Running Shoes - 10 UK/India (45 EU) (11 US)",
    "Suunto Digital Black Dial Unisex Watch - SS018734000"
  ],
  "Wish_CreatedDate": [
    "2017-03-05T00:00:00.000Z",
    "2017-02-13T00:00:00.000Z"
  ],
  "UserDetails": [
    {
      "createdAt": "2016-09-30T12:28:32.773Z",
      "jeenesFriends": [
        "57edf8a96ad8f6ff453a384a",
        "57ee516c8e117c754915a26b",
        "58a1644b6c91d2af783770b0",
        "57ef4631b97d81824cf54795"
      ],
      "userImage": "user_profile/Male.png",
      "email": "roopak@small-screen.com",
      "fullName": "Roopak Kapoor"
    }
  ],
  "Latest_Wish_CreatedDate": "2017-03-05T00:00:00.000Z"
},
Shashwat Gupta
  • 5,071
  • 41
  • 33
1

For max value, we can write sql query as

select age from table_name order by age desc limit 1

same way we can write in mongodb too.

db.getCollection('collection_name').find().sort({"age" : -1}).limit(1); //max age
db.getCollection('collection_name').find().sort({"age" : 1}).limit(1); //min age
0

You can also achieve this through aggregate pipeline.

db.collection.aggregate([{$sort:{age:-1}}, {$limit:1}])
Sunny Prakash
  • 772
  • 1
  • 8
  • 13
  • 4
    This has a terrible perfomance. Getting the highest value always costs `O(n)` without indicies. This has a performance of `O(n log(n))` – sb27 Feb 23 '20 at 21:29
0

You can use

db.collection.findOne({}, null, {sort: {age: -1}})

Will return you one doc with max age

Ritankar Paul
  • 65
  • 1
  • 4