MongoDB Aggregation to Custom Sort

While the MongoDB Aggregation Pipeline can be used to aggregate your data, it is definitely not limited to that and can be used to construct more complicated queries that don’t necessarily do any data aggregation.

One of the big advantages of the Aggregation Pipeline is the availability of the Expressions that are normally not accessible through regular find() queries.

As an example, let’s assume you have a collection called ‘products’ that holds an inventory of all products at a grocery store. Each document contains information about a product such as description, brand, quantity, price, etc., as well as a field that identifies the category, for instance ‘dairy’, ‘baked goods’, ‘frozen foods’, ‘produce’, ‘meat’, ‘cleaners’, ‘personal care’ etc.

Now you need to create a report custom sorted by category. Not just an alpha-numerical sort by category name but specifically all ‘produce’ products at the top, followed by ‘dairy’, then everything else in alpha-numberical order. A reason for this order could be that this grocery store mainly focuses on dairy and produce and less on the other categories, so they want to get the results they care about the most near the top.

In a traditional relational database you could achieve this by using a CASE WHEN THEN in your ORDER BY CLAUSE, for instance:

SELECT * FROM PRODUCTS
ORDER BY
  CASE CATEGORY
  WHEN 'produce' THEN 1
  WHEN 'dairy' THEN 2
  ELSE 3 END,
  CATEGORY;

The same can be done in MongoDB with the help of the $cond conditional expression in the $project stage.

db.products.aggregate([
  {"$project":{
    "_id":false,
    "sortField":
      {"$cond":[{"$eq":["$category", "produce"]}, 1,
      {"$cond":[{"$eq":["$category", "dairy"]}, 2,
       3]} ]},
    "category":true,
    "description":true,
    "brand":true
  }},
  {"$sort":{"sortField":1, "category":1}}
])

Leave a Reply

Your email address will not be published. Required fields are marked *

*