Sort objects on first letter of a string and date in MongoDB-CodePudding

I have a database of objects that contain codes like 'A-123' and 'S-123'. And I need to sort this so that the objects with a code that starts with 'S-' should come first. Everything should then be sorted by date as well.

So far I've only found how to select objects that contain a string that starts with a particular letter, but I have not been able to sort on that while sorting on the date as well.

{code: {$regex: /^S-./}}

CodePudding user response：

For any custom sort the approach is always similar, create a temporary field with a value to represent the custom order, then sort on that field.

like so:

db.collection.aggregate([
  {
    $addFields: {
      customSortField: {
        $cond: [
          {
            "$regexMatch": {
              "input": "$code",
              "regex": "^S-"
            }
          },
          0,
          1
        ]
      }
    }
  },
  {
    $sort: {
      customSortField: 1,
      code: 1
    }
  },
  {
    $unset: "customSortField"
  }
])

Mongo Playground