MongoDB sort the result after limit

Question

is there a way to sort the documents returned from limit? example:

//returns 10 documents:
db.users.find()
.sort({last_online_timestamp:-1})
.limit(10) 

//returns 10 another documents
db.users.find()
.sort({last_online_timestamp:-1})
.limit(10)
.sort({messages_count:1});

what I want is to get 10 last logged in users and then sort them by messages count.

db.user.find().sort({last_online_timestamp:-1}).limit(10).pretty(); — RïshïKêsh Kümar, Apr 18 '17 at 12:48
db.user.find().sort({last_online_timestamp: -1}).limit(10, callback); — RïshïKêsh Kümar, Apr 18 '17 at 12:52
@RïshïKêshKümar If you are suggesting a solution, you should post it as a full Answer, not as a comment. — Vince Bowdren, Apr 18 '17 at 14:27
@Alexander Tyuryaev : It is not possible to use sort() twice on a find() query; this has been addressed before, for example http://stackoverflow.com/a/43250870/174843 or http://stackoverflow.com/a/14425636/174843 or http://stackoverflow.com/q/8474353/174843. Instead you need to use an aggregation, as suggested by Jeff J. — Vince Bowdren, Apr 18 '17 at 14:29

Jeff J · Accepted Answer · 2017-04-18T13:36:32.037

7

You can use an aggregate such as

db.users.aggregate([
    {"$sort": {"last_online_timestamp":1}},
    {"$limit": 10},
    {"$sort": {"messages_count": 1}}
])

This will go through stages where the the documents in the collection will be:

Sorted by the last_online_timestamp field in ascending order
Limited to 10 documents
Those 10 documents will be sorted by the messages_count field in ascending order

You may want to change {"$sort": {"last_online_timestamp":1} to {"$sort": {"last_online_timestamp":-1} depending on the actual values of last_online_timestamp

For more information on Mongo aggregation, see https://docs.mongodb.com/manual/aggregation/.

edited Apr 18 '17 at 13:36

answered Apr 18 '17 at 13:00

Jeff J

553
2
9

3

I don't think `db.users.find().sort({last_online_timestamp:-1}).limit(10).sort({messages_count:1})` will work. As per docs, sort is always applied before limit irrespective of the order you write it in. – chimp Apr 18 '17 at 13:17
@demolisher You are correct - the syntax was accepted but the results were not what was expected. I have updated the answer to include only the aggregate. Thanks! – Jeff J Apr 18 '17 at 13:37
the syntax of "aggregate" is difficult to read. that one is better: db.users.aggregate().sort({"last_online_timestamp":1}).limit(10).sort({"messages_count": 1}); – neoexpert Apr 18 '17 at 14:11

MongoDB sort the result after limit

1 Answers1