How to sort ZERO or NULL values LAST in MongoDB

MongoDB is my go-to when it comes to document databases and recently we ran into this sort query issue that I thought would be worth sharing.

The question was quite simple:

How do you sort documents by price, but ensure the ones with zero (0) or a NULL price values are always sorted LAST in the list?

Luckily the solution is pretty straightforward.

Here's what a normal $sort operator would look like in MongoDB if we are to sort by price:

db.collection("kites")
    .find()
    .sort({ price: 1 })
    .toArray();

At first glance, your sorting options seem few and far between. However, you can specify which additional properties to sort by and their direction.

In this case, early on, we added another property to the document that allows us to tell whether or not price has a value.

We ended up using this existing additional boolean property called hasPrice.

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. Voila!

db.collection("kites")
    .find()
    .sort({ hasPrice: -1, price: 1 })
    .toArray();

Pro-Tip

In this case, the solution ended up being a one line fix by updating our $sort operator in our query. However, this could be helpful if you're using an aggregation query. Instead of adding the extra property to the document itself, you could project this property easily using an extra pipeline stage.

Some would argue, doing this in SQL would have been easier, but those are the tradeoffs when comparing patterns between SQL and NoSQL worlds.

Photo by Patrick Tomasso