MongoDB: Sorting Documents by a Field but NULL First/Last

Introduction

MongoDB provides a powerful querying system that includes the ability to sort documents by the values of a specific field. When sorting, you may encounter fields that contain a null value. Handling these null values effectively is often critical to the application logic, as you may want to display these documents either at the beginning or end of your results regardless of the sort order for non-null values.

Understanding NULL Values in Sorting

In MongoDB, when you use the sort function, by default, any null values are considered to be less than all other values. If you sort in ascending order, documents with null values will appear first, while if you sort in descending order, they will appear last.

db.collection.find().sort({ field: 1 }); // Ascending, with NULLs first
db.collection.find().sort({ field: -1 }); // Descending, with NULLs last

Basic Sorting: NULLs First

Let’s start with a simple example wherein we wish nulls to appear first in our sorted data. In MongoDB, this is the default behavior when sorting in ascending order:

db.collection.find().sort({ field: 1 });

This query finds all documents in the collection and sorts them by field in ascending order, with null values appearing first. Here’s a sample output:

[
  { "_id": 1, "field": null },
  { "_id": 2, "field": "A" },
  { "_id": 3, "field": "B" }
]

Sorting with NULLs Last in Ascending Order

To sort documents by a field in ascending order but with null values last, you have to craft a more complex query. MongoDB doesn’t provide a direct way to do this, but you can achieve this by leveraging the aggregation pipeline and conditional sorting.

Here’s an example:

db.collection.aggregate([
  {
    $addFields: {
      sort_helper: {
        $cond: { if: { $eq: ['$field', null] }, then: 1, else: 0 }
      }
    }
  },
  { $sort: { sort_helper: 1, field: 1 } }
]);

This aggregation pipeline does the following:

• Adds a new field sort_helper to each document that is 1 if field is null and 0 otherwise.
• Sorts the collection on sort_helper and field, both in ascending order.

Resulting documents would be something like:

[
  { "_id": 2, "field": "A", "sort_helper": 0 },
  { "_id": 3, "field": "B", "sort_helper": 0 },
  { "_id": 1, "field": null, "sort_helper": 1 }
]

Advanced Sorting Techniques

In cases where you have multiple fields to sort by and you want to prioritize null values in some fields but not others, the sorting logic becomes more complex.

Consider a collection with documents that contain two fields field1 and field2. You want to sort by field1 in ascending order, with null values last, and then by field2 in descending order, with null values first.

Here’s an advanced example using the aggregation framework:

db.collection.aggregate([
  {
    $addFields: {
      sort_helper1: {
        $cond: {
          if: { $eq: ['$field1', null] },
          then: 1,
          else: 0
        }
      },
      sort_helper2: {
        $cond: {
          if: { $eq: ['$field2', null] },
          then: -1,
          else: 0
        }
      }
    }
  },
  {
    $sort: {
      sort_helper1: 1,
      field1: 1,
      sort_helper2: 1,
      field2: -1
    }
  }
]);

This pipeline adds two helper fields for sorting purposes and then sorts the collection in the desired complex manner. The first sorting passes ensure that null values in field1 will appear last in ascending order, and then the second sorting condition ensures that null values in field2 will appear first in descending order.

Handling NULLs Strategically

Sometimes, we might need to perform operations based on the presence or absence of certain fields. This section will show how to sort documents differently based on field existence.

Consider sorting documents in ascending order where documents that do not have the field are listed at the end:

db.collection.aggregate([
  {
    $addFields: {
      sort_helper: {
        $cond: {
          if: { $ifNull: ['$field', false] },
          then: 0,
          else: 1
        }
      }
    }
  },
  { $sort: { sort_helper: 1, field: 1 } }
]);

Unlike the earlier examples, $ifNull is used to check if field exists. Documents without this field will have a sort_helper value of 1, causing them to list at the end when sorted.

Conclusion

MongoDB offers flexible sorting options, but handling null or missing values requires strategic use of the aggregation framework. By adding custom fields and conditional logic, you can precisely control how these values are ordered within your query results.