Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MongoDB sorting

Tags:

mongodb

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"

like image 436
teggy Avatar asked Nov 21 '11 02:11

teggy


People also ask

Does MongoDB support sorting?

MongoDB can perform sort operations on a single-field index in ascending or descending order. In compound indexes, the sort order determines whether the index can be sorted.

What sorting algorithm does MongoDB use?

If MongoDB cannot obtain the sort order via an index scan, then MongoDB uses a top-k sort algorithm. This algorithm buffers the first k results (or last, depending on the sort order) seen so far by the underlying index or collection access.

What is the default sort order in MongoDB?

The default internal sort order (or natural order) is an undefined implementation detail.

What is sort key in MongoDB?

If the sort keys correspond to the index keys or an index prefix, MongoDB can use the index to sort the query results. A prefix of a compound index is a subset that consists of one or more keys at the start of the index key pattern. The following query and sort operations use the index prefixes to sort the results.


4 Answers

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
like image 198
Brian Hicks Avatar answered Oct 13 '22 01:10

Brian Hicks


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.

like image 32
Tyler Brock Avatar answered Oct 13 '22 02:10

Tyler Brock


Also:

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

where 1 is ascending and -1 is descending.

like image 5
Oneide Luiz Schneider Avatar answered Oct 13 '22 02:10

Oneide Luiz Schneider


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 })
like image 3
DhruvPathak Avatar answered Oct 13 '22 00:10

DhruvPathak