Sort timestamp column different depending on value of another column

I have a set of documents that has a timestamp and a status for if its been submitted. I want to show the oldest unsubmitted first, followed by your most recent submitted.

Here is a mongoplayground with sample data:

Input:

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

Expected Output:

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

The issue here is the sorting of my timestamp flips from asc to desc if its been submitted. I cant figure out how to do that.

I thought about using $group and then trying to $sort the groups different, not sure if thats possible but I also read sort order isnt preserved in grouping so I don’t think thats viable.

Trying with facet I can seeminly group these properly, but having issues setting the root after unioning the two sets. Unwind doesn’t keep order, but I’d want the result array to be the root:

Also is unionWith guaranteed to keep the order? Or is there still a simpler way that I’m not considering?

I have a quite simple approach but with the caveat that no index can be used since the $sort is a computed value.

The computed value is either time directly or -1 * time. You use submitted to multiple by -1 the submitted one because you want then in reverse order. See

If the result set is small the fact that it cannot use an index should not be problematic. If the result is big it might be more efficient to do 2 queries.