mongodbsortingmongodb-queryaggregation-frameworkmongodb-indexes

How to ensure that $sort push missing values to the bottom regardless of ascending or descending

If I sort based on a column that has missing values, the missing values will be on top if sort is ascending.

How to ensure that missing values always get pushed to the bottom regardless of ascending or descending?

Solution

  • Slightly cheeky but you can "temporarily" assign a value that will force the item to the bottom of the list, then filter it out:

    db.foo.aggregate([
    {$addFields: {
        'val':{$ifNull: [ '$val', 99999999999999 ]}
    }}  
    ,{$sort: {'val':1}}
    ,{$match: {'val':{$ne:99999999999999}}}
]);