Database

[{"created_at":"2024-03-15T18:11:18.687","updated_at":"2024-03-15T18:11:28.945","fields":{"contract_first":[],"contract_second":[{"position":"Bob"}],"uuid":"bf031b46-ec33-4c61-ad33-a3ad2cbeb9cb"},"id":"65f46520928f6fd496ffbe6d"},{"created_at":"2024-03-15T18:11:18.687","updated_at":"2024-03-15T18:15:26.838","fields":{"contract_first":[{"position":"Anna"}],"contract_second":[],"uuid":"bf052b46-ec33-4c61-ad33-a3ad2cbeb9cb"},"id":"65f4660e928f6fd496ffbeb0"},{"created_at":"2024-03-15T18:11:18.687","updated_at":"2024-03-15T18:15:26.838","fields":{"contract_first":[{"position":"Bob"}],"contract_second":[],"uuid":"bf092b46-ec33-4c61-ad33-a3ad2cbeb9cb"},"id":"65f8660e928f6fd496ffbeb1"},{"created_at":"2024-03-15T18:11:18.687","updated_at":"2024-03-15T18:15:26.838","fields":{"contract_first":[{"position":"Ellen"}],"contract_second":[{"position":"Dave"}],"uuid":"bf092b46-ec33-4c61-ad33-a3ad2cbeb9cb"},"id":"65f8660e928f6fd496ffbeb2"},{"created_at":"2024-03-15T18:11:18.687","updated_at":"2024-03-15T18:15:26.838","fields":{"contract_first":[{"position":"Ellen"}],"contract_second":[{"position":"Charlie"}],"uuid":"bf092b46-ec33-4c61-ad33-a3ad2cbeb9cb"},"id":"65f8660e928f6fd496ffbeb3"},{"created_at":"2024-03-15T18:11:18.687","updated_at":"2024-03-15T18:15:26.838","fields":{"contract_first":[],"contract_second":[{"position":"Charlie"}],"uuid":"bf092b46-ec33-4c61-ad33-a3ad2cbeb9cb"},"id":"65f8660e928f6fd496ffbeb4"}]

Query

db.collection.aggregate([{$set:{sort_key1:{$ifNull:[{$first:"$fields.contract_first.position"},{$first:"$fields.contract_second.position"}]},sort_key2:{$first:"$fields.contract_second.position"}}},{$sort:{sort_key1:1,sort_key2:1}},{/** comment this out to see the sort_keys*/$unset:["sort_key1","sort_key2"]}])

Result