Order by category with items of category other appearing last

Working with Data
,
1

I have the following database of items that have a category:

[
  {
    _id: "ABC",
    category: "Other"
  },
  {
    _id: "DEF",
    category: "Cat"
  },
  {
    _id: "GHI",
    category: "Cat"
  },
  {
    _id: "XYZ",
    category: "Zebra"
  },
  {
    _id: "123",
    category: "Dog"
  },
  {
    _id: "456",
    category: "Other"
  }
]

I want to be able to sort the items by category, but with items of the Other category always appearing after everything else.

Below is my current query:

db.collection.aggregate([
  {
    $sort: {
      category: 1
    }
  }
])

The query has the following result:

[
  {
    "_id": "DEF",
    "category": "Cat"
  },
  {
    "_id": "GHI",
    "category": "Cat"
  },
  {
    "_id": "123",
    "category": "Dog"
  },
  {
    "_id": "ABC",
    "category": "Other"
  },
  {
    "_id": "456",
    "category": "Other"
  },
  {
    "_id": "XYZ",
    "category": "Zebra"
  }
]

What changes need to be made to the query to ensure Other always shows up last?

3

Hello @Curtis_L ,

To achieve the desired sorting where items of the “Other” category always appear after everything else, you can modify your aggregation query by using a combination of $cond and $eq operators to assign a sort value based on the category. Here’s the modified query:

db.collection.aggregate([
  {
    $addFields: {
      sortValue: {
        $cond: { 
          if: { $eq: ["$category", "Other"] }, 
          then: 2, 
          else: 1 
        }
      }
    }
  },
  {
    $sort: {
      sortValue: 1,
      category: 1
    }
  },
  {
    $project: {
      sortValue: 0
    }
  }
])

Here’s the Result:

[
  {
    "_id": "DEF",
    "category": "Cat"
  },
  {
    "_id": "GHI",
    "category": "Cat"
  },
  {
    "_id": "123",
    "category": "Dog"
  },
  {
    "_id": "XYZ",
    "category": "Zebra"
  },
  {
    "_id": "ABC",
    "category": "Other"
  },
  {
    "_id": "456",
    "category": "Other"
  }
]

Explanation of the query:

  1. $addFields: This stage adds a new field called sortValue. The value of sortValue is determined using the $cond operator, which checks if the category is “Other”. If it is, it assigns a sort value of 2; otherwise, it assigns a sort value of 1.
  2. $sort: This stage sorts the documents first by sortValue in ascending order (so that “Other” is always at the end), and then by category in ascending order.
  3. $project: This stage removes the sortValue field from the output since it was only used for sorting purposes.

With this modified query, the result will have items sorted by category, with items of the “Other” category always appearing after everything else.

Note: This query is an example based on the data provided by you. Kindly, test and modify the query based on your use case and requirements.

Regards,
Tarun

3 Likes
4

If I may add.

Since the sort is based on a computed value, then no index will be used so a memory sort will be done. If the result set is small it is not an issue. If the result set is large you may try the following alternatives:

1 - Simply do 2 queries, one with a $match that uses $ne:“Other” and then a second one with $eq:“Other”. For some data set doing 2 database access might be faster than a memory sort.

2 - Permanently update the collection with the sortValue, then have an index with sortValue:1,category:1.

1 Like