Question 304302 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – Mongo sort by date unless a condition is met?

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    I think aneroid answer fits perfectly into your requeriment and I’ve upvoted it thinking should be the accepted answer.

    But when I read the question I though this is the clearly example to use $facet. So I will post the use of facet in case helps someone.

    Using $facet you can create "two ways" or "two queries" and compute them separately… which is what you want, compute true values and false values independently.

    So you can try this query:

    The trick here is create the two sorts independently each one. First true_value will be an array where only exists submitted values as true and it will be sort by time: -1.

    On the other hand is false_values which will filter by false and sort by 1.

    So concating arrays and modifying output to get the same format you can get the result.

    db.collection.aggregate([
  {
    "$facet": {
      "true_value": [
        {
          "$match": {
            "submitted": true
          }
        },
        {
          "$sort": {
            "time": -1
          }
        }
      ],
      "false_value": [
        {
          "$match": {
            "submitted": false
          }
        },
        {
          "$sort": {
            "time": 1
          }
        }
      ]
    }
  },
  {
    "$project": {
      "result": {
        "$concatArrays": [
          "$false_value",
          "$true_value"
        ]
      }
    }
  },
  {
    "$unwind": "$result"
  },
  {
    "$replaceRoot": {
      "newRoot": "$result"
    }
  }
])

    Example here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search