Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MongoDB sort by only exists entry, key with value first and key with null or not exists last

I have a collection of 15000 documents. Some documents have sr_no with numeric values and other with absent of sr_no.

Now i want to get entries like all documents comes first which has sr_no with asc then all others.

I tried .find().sort({sr_no:1}) but it return all null entries first then asc with sr_no.

This question seems too close with duplicate. But slightly defer with numeric key.

I answered it with hack below.

like image 577
Nishchit Avatar asked Mar 16 '16 06:03

Nishchit


People also ask

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

By adding the boolean property hasPrice, we can specify the order. We sorted the fields that have a value first, which ensures the ones without a price (zero or null values) are sorted last.

How do I ignore NULL values in MongoDB?

Solution 1: In case preservation of all null values[] or null fields in the array itself is not necessary. Filter out the not null elements using $filter , the ( all null elements) array would be empty, filter that out from documents using $match then $sort on values .

How do I sort a case insensitive in MongoDB?

This means the only way to sort case insensitive currently is to actually create a specific "lower cased" field, copying the value (lower cased of course) of the sort field in question and sorting on that instead.


1 Answers

I used a dirty hack for this.

MongoDB doc says that they have priorities for sorting as posted below image.

enter image description here

So when i sort with asc then it sort first all null (empty key consider as null) entries then sort numeric entries.

What is hack here ?

Store sr_no : "" with empty string default.

Now it will sort first numeric values then string.

like image 197
Nishchit Avatar answered Nov 15 '22 03:11

Nishchit