Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I sort into that nulls are last ordered in mongodb?

Tags:

mongodb

I excute this query in the mongo shell

db.getCollection('list').find({}).sort({next_time: 1})

the result is

next_time
--
null
null
null
2015-02-21 00:00:00
2015-03-25 00:00:00
2015-08-29 00:00:00

I hope to make the result like this

next_time
--
2015-02-21 01:00:00
2015-03-25 01:00:00
2015-08-29 01:00:00
null
null
null

The different is that 'null's are last ordered in the list. How can I do for this?

like image 365
Toby Seo Avatar asked Jun 27 '15 02:06

Toby Seo


People also ask

How are nulls sorted by default in the order by clause?

If you specify the ORDER BY clause, NULL values by default are ordered as less than values that are not NULL. Using the ASC order, a NULL value comes before any non-NULL value; using DESC order, the NULL comes last.

How do I sort orders in MongoDB?

To sort documents in MongoDB, you need to use sort() method. The method accepts a document containing a list of fields along with their sorting order. To specify sorting order 1 and -1 are used. 1 is used for ascending order while -1 is used for descending order.

What is the use of sort () in MongoDB?

MongoDB – sort() Method The sort() method specifies the order in which the query returns the matching documents from the given collection. You must apply this method to the cursor before retrieving any documents from the database.


1 Answers

Perhaps you can use aggregation and an artificially high end date:

c = db.foo.aggregate([
{$project: {
            next_time: 1,
            nlt: { $ifNull: [ "$next_time", new ISODate("9000-01-01") ] }
  }     
}
,
{$sort: { "nlt": 1}}
                  ]);
c.forEach(function(r) { printjson(r); });

Alternatively, if the majority of the material has nulls and you don't want to deal with those docs at all, then filter them out and just $sort the remainder:

db.foo.aggregate([
{$match: {"nt": {$exists: true}}}
,
{$sort: { "nt": 1}}
                 ]);
like image 125
Buzz Moschetti Avatar answered Oct 07 '22 08:10

Buzz Moschetti