MongoDB custom sort with multiple key value pairs

user_6020930 0
Viewed 196

List of documents in a collection :-

{ _id: '1', task: 'task1', status: 'pending', dayAged: '3' },
{ _id: '2', task: 'task2', status: 'waiting', dayAged: '1' },
{ _id: '3', task: 'task3', status: 'complete', dayAged: '3' }
{ _id: '4', task: 'task4', status: 'waiting', dayAged: '3' },
{ _id: '5', task: 'task5', status: 'pending', dayAged: '2' },
{ _id: '6', task: 'task6', status: 'pending', dayAged: '1' },

How can we sort above data based on status and day aged which should be if the status is pending then according to day aged of that status should be listed eg:-
status waiting should be first then dayAged after that pending then dayAged so that i can sort ascending and descending order of dayAged.

{ _id: '4', task: 'task4', status: 'waiting', dayAged: '3' },
{ _id: '2', task: 'task2', status: 'waiting', dayAged: '1' }, 
{ _id: '1', task: 'task1', status: 'pending', dayAged: '3' },
{ _id: '5', task: 'task5', status: 'pending', dayAged: '2' },
{ _id: '6', task: 'task6', status: 'pending', dayAged: '1' },    
{ _id: '3', task: 'task3', status: 'complete', dayAged: '3' }

Output should be like this..

pend.  3
pend.  2
pend.  1 
wait.  23
wait.  21
wait.  13
done.  8
done.  6
done.  3
aggregation-framework mongodb-query mongodb aggregate sorting
1 Answers

You can use $addFields along with $switch to assign "weight" for every status. Once it's done you can $sort by this new field and remove it afterwards using $project:

db.collection.aggregate([
    {
        $addFields: {
            statusWeight: {
                $switch: {
                    branches: [
                        { case: { $eq: [ "$status", "waiting" ] }, then: 0 },
                        { case: { $eq: [ "$status", "pending" ] }, then: 1 }
                    ],
                    default: 2
                }
            }
        }
    },
    {
        $sort: {
            statusWeight: 1,
            dayAged: -1
        }
    },
    {
        $project: {
            statusWeight: 0
        }
    }
])

Mongo Playground

user_62389770
Related