Mongo sort by date unless a condition is met?

Question

Supposed I have the following data:

Time	Submitted
1	True
2	True
3	True
4	False
5	False
6	False

My goal is to get either the the oldest unsubmitted OR the newest submitted. This is for loading user timesheets, so if they have an old unsubmitted timesheet, I want to load that by default. If not I want to show them their most current timesheet.

After the query I'd expect the data back in this order:

Time	Submitted
4	False
5	False
6	False
3	True
2	True
1	True

Notice the first 3 records are false so they sort by ascending time while the last 3 records are true so they sort by descending time.

Here is a mongoplayground sample of the data:

https://mongoplayground.net/p/yEtgMOfZ_ik

I'm guessing this can be done by projecting some value to use as the sort by, but I can't wrap my head around what that needs to be.

Answer 1

You have a sort on submitted already: false sorts before true (0 vs 1 in many languages). 👍 So that's the first key to sort on.

When submitted=false, you want a ascending order for time (4, 5, 6) and when it's true, you want the opposite.

You can create an additional key to sort on Time based on whether or not it was submitted and use negative-numbers to change the sort order when it's submitted true/false.

Here, I'm creating timeSort which will negate the time when submitted=true. So 3 becomes -3 and in ascending order, -3 < -2 < -1. Whereas 4, 5, 6 remain as is so 4 < 5 < 6 in ascending order.

db.collection.aggregate([
  {
    $set: {
      timeSort: {
        $multiply: [
          "$time",
          { $cond: [ "$submitted", -1, 1 ] }
        ]
      }
    }
  },
  {
    $sort: {
      submitted: 1,
      timeSort: 1
    }
  },
  { $unset: "timeSort" }
])

You can flip the 1 and -1 around in $cond and in timeSort in $sort, gives the same results.

[
  { "time": 4, "submitted": false },
  { "time": 5, "submitted": false },
  { "time": 6, "submitted": false },
  { "time": 3, "submitted": true },
  { "time": 2, "submitted": true },
  { "time": 1, "submitted": true }
]

Mongo Plaground