MongoDB sorting

Question

I want implement a "bump" feature for topics. Once a topic is bumped, it will have a new "bump_date" field. I want to sort it so that when there is a "bump_date" field, it will be sorted as if it was the "created" field. Here's an example of my db.topics:

{
    "text" : "test 1",
    "created" : "Sun Nov 20 2011 02:03:28 GMT-0800 (PST)"
},
{
    "text" : "test 2",
    "created" : "Sun Nov 18 2011 02:03:28 GMT-0800 (PST)"
},
{
    "text" : "test 3",
    "created" : "Sun Nov 17 2011 02:03:28 GMT-0800 (PST)",
    "bump_date: : "Sun Nov 19 2011 02:03:28 GMT-0800 (PST)"
}

I want the sort to return in the order of "test 1", "test 3", "test 2"

It might help for MongoDB sorting http://www.code-sample.com/2016/07/mongodb-sort-by-date.html — Anil Singh, Jul 11 '16 at 12:09

score 89 · Accepted Answer · answered Nov 21 '11 at 03:03

89

Sorting in MongoDB is done like so:

db.collection.find({ ... spec ... }).sort({ key: 1 })

where 1 is ascending and -1 is descending.

In your specific example: db.topics.find().sort({ bump_date: 1 }), although it might be better to call it something like "updated_at".

You'll also definitely want to put an index on your "bump_date" field.

sorting: http://www.mongodb.org/display/DOCS/Sorting+and+Natural+Order
indexes: http://www.mongodb.org/display/DOCS/Indexes

answered Nov 21 '11 at 03:03

Brian Hicks

6,213
8
51
77

2

For this to work as expected, I think OP will need to set `bump_date` to `created` when saving the document for the first time. Then you always sort by `bump_date` and index on it, as you suggest. – dcrosta Nov 21 '11 at 03:13
1

That's true, and probably should be created so the collection can be eventually compacted. Or, [sparse indexes](http://www.mongodb.org/display/DOCS/Indexes#Indexes-SparseIndexes). – Brian Hicks Nov 21 '11 at 03:17

Oneide Luiz Schneider · Answer 2 · 2018-01-09T00:38:58.350

5

Also:

db.collection.find( { $query: {}, $orderby: { column : -1 } } )

where 1 is ascending and -1 is descending.

edited Jan 09 '18 at 00:38

answered Jun 06 '15 at 04:05

Oneide Luiz Schneider

59
1
4

You answer me 3 years later, of course it changed... and now again... – Oneide Luiz Schneider Mar 01 '23 at 16:29

score 5 · Answer 3 · answered Nov 21 '11 at 17:00

As Brian Hicks suggested, creating an additional updated_at field is the way to go. This way, when a document is created you can have created_at and updated_at initially be the same.

{
     "created_at": xxx,
     "updated_at": xxx
}

If you then "bump" the updated_at field by setting it to the current time when there is a a bump event you can sort on the updated_at field to achieve the ordering you desire.

score 3 · Answer 4 · edited May 23 '17 at 11:54

Currently it is not possible in mongodb to do a sort based on user defined criteria over multiple columns.eg. here the function would have been to return bump_date if it is set,else return created

Either you will have to use a server-side or client-side code as mentioned here :

Mongo complex sorting?

or if you want to stay with basic quering and sorting, you shall :

create a key bump_date equivalent to created whenever a new record is created. This will not be a data overhead, as you can expect every topic of yours to be bumped once in a while in future,hence bump_date field will eventually be added. So add it from the start itself.
Whenever the article is bumped,update the field bump_date .

Your example documents will look like this with this change :

{
    "text" : "test 1",
    "created" : "Sun Nov 20 2011 02:03:28 GMT-0800 (PST)",
    "bump_date" : "Sun Nov 20 2011 02:03:28 GMT-0800 (PST)"
},
{
    "text" : "test 2",
    "created" : "Sun Nov 18 2011 02:03:28 GMT-0800 (PST)",
    "bump_date" : "Sun Nov 18 2011 02:03:28 GMT-0800 (PST)" 
},
{
    "text" : "test 3",
    "created" : "Sun Nov 17 2011 02:03:28 GMT-0800 (PST)",
    "bump_date: : "Sun Nov 19 2011 02:03:28 GMT-0800 (PST)"
}

You shall ensureIndex on bump_date field. Now you can query the required data easily.

db.topics.find().sort({ bump_date: 1 })

why ensureIndex should be applied to bump_date? – Alexander Beletsky May 02 '14 at 12:04 — Alexander Beletsky, May 02 '14 at 12:04

MongoDB sorting

4 Answers4

Linked