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-./}}
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"
}
])