Sorting according to time in string in mongodb

Question

How can i sort if my time(time_required) is saved in this format ?

quiz_customer_record

{
    "_id" : ObjectId("5f16eb4a5007bd5395c76ed9"),
    "quiz_id" : "5f05bbd10cf3166085be68fc",
    "user_id" : "5f06e0ddf718c04de30ea47f",
    "name" : "ABC",
    "time_required" : "0:6 Mins",
    "questions_attempted" : 0,
    "total_quiz_questions" : 1,
    "attempt_date" : "2020-07-21T13:19:08.025Z"
},
{
    "_id" : ObjectId("5f16eb5f5007bd5395c76edb"),
    "quiz_id" : "5f05bbd10cf3166085be68fc",
    "user_id" : "5f06e0ddf718c04de30ea47f",
    "name" : "ABC",
    "time_required" : "0:8 Mins",
    "questions_attempted" : 0,
    "total_quiz_questions" : 1,
    "attempt_date" : "2020-07-21T13:19:29.377Z"
}

I want to sort it according to time_required but its in string and is in format of Mins:Seconds. Yes its a pretty messed up. But do we have a solution? I want to use mongo query for that as there are so many records and i sort of need to use limit(for pagination). That is why it is necessary for using mongo query.

Expected Result- Sort type- descending()

   {
        "_id" : ObjectId("5f16eb5f5007bd5395c76edb"),
        "quiz_id" : "5f05bbd10cf3166085be68fc",
        "user_id" : "5f06e0ddf718c04de30ea47f",
        "name" : "ABC",
        "time_required" : "0:8 Mins",
        "questions_attempted" : 0,
        "total_quiz_questions" : 1,
        "attempt_date" : "2020-07-21T13:19:29.377Z"
    },
   {
        "_id" : ObjectId("5f16eb4a5007bd5395c76ed9"),
        "quiz_id" : "5f05bbd10cf3166085be68fc",
        "user_id" : "5f06e0ddf718c04de30ea47f",
        "name" : "ABC",
        "time_required" : "0:6 Mins",
        "questions_attempted" : 0,
        "total_quiz_questions" : 1,
        "attempt_date" : "2020-07-21T13:19:08.025Z"
    }

The query i'm using is

 db.quiz_customer_record.aggregate([{ $match: { quiz_id:quiz_id}},
    {
        $sort: { attempt_date: -1 }
    },
    {
        $group: {
            _id: "$user_id",
            result1: { $first: "$attempt_date" },
            quiz_id: { $first: "$quiz_id" },
            time_required: { $first: "$time_required" },
             o_id: { $first: "$_id" }
        }
    },
    {
        $project: {
            _id: "$o_id",
            user_id: "$_id",
            quiz_id:"$quiz_id",
            time_required:"$time_required",
            result1: 1
        }
    }
]).sort({time_required:-1})

Answer 1

Answer for mongo version less than 4.2

$set was added in 4.2 version. For earlier version $addFields can be used.

db.collection.aggregate([
  {
    "$addFields": {
      "time_required_split": {
        $substr: [
          "$time_required",
          0,
          3
        ]
      }
    }
  },
  {
    "$addFields": {
      "time_required_split": {
        $split: [
          "$time_required_split",
          ":"
        ]
      }
    }
  },
  {
    "$addFields": {
      "time_seconds": {
        $sum: [
          {
            "$multiply": [
              {
                $toInt: {
                  $arrayElemAt: [
                    "$time_required_split",
                    0
                  ]
                }
              },
              60
            ]
          },
          {
            $toInt: {
              $arrayElemAt: [
                "$time_required_split",
                1
              ]
            }
          }
        ]
      }
    }
  },
  {
    "$sort": {
      time_seconds: -1
    }
  },
  {
    "$project": {
      "time_required_split": 0,
      "time_seconds": 0
    }
  }
])

Mongo Playground

Answer 2

Try this query -

db.collection.aggregate([
  {
    "$set": {
      "time_required_split": {
        $substr: [
          "$time_required",
          0,
          3
        ]
      }
    }
  },
  {
    "$set": {
      "time_required_split": {
        $split: [
          "$time_required_split",
          ":"
        ]
      }
    }
  },
  {
    "$set": {
      "time_seconds": {
        $sum: [
          {
            "$multiply": [
              {
                $toInt: {
                  $arrayElemAt: [
                    "$time_required_split",
                    0
                  ]
                }
              },
              60
            ]
          },
          {
            $toInt: {
              $arrayElemAt: [
                "$time_required_split",
                1
              ]
            }
          }
        ]
      }
    }
  },
  {
    "$sort": {
      time_seconds: -1
    }
  },
  {
    "$project": {
      "time_required_split": 0,
      "time_seconds": 0
    }
  }
])

Mongo Playground

Let me know if don't understand any stage.