{ _id: 1, value: '25 hr 2 mins' },
  { _id: 2, value: '25 hr 36 mins' },
  { _id: 3, value: '30 mins' },
  { _id: 4, value: '5 hr' }

I have documents with id and value as string with hours and mins. I want the result to be

{ _id: 1, value: 25.03 },
{ _id: 2, value: 25.6 },
{ _id: 3, value: 0.5 },
{ _id: 4, value: 5 }

Is there a way to extract number from string and perform to remove the hr and mins from string and then perform convertion to just hours(decimal)?

CodePudding user response：

1. $set - With regex to capture group for hour and min digits.

2. $set

   2.1. $round - Round the result 2.1.1 to 2 decimal places.

   2.1.1. $sum - Sum the results of 2.1.1.1 and 2.1.1.2.

   2.1.1.1. $toInt - Convert the first element of value array to integer.

   2.1.1.2. $divide - Divide the value (Convert the second element of value array to integer) by 60.

db.collection.aggregate([
  {
    $set: {
      value: {
        $regexFind: {
          input: "$value",
          regex: "(\\d ) hr (\\d ) mins"
        }
      }
    }
  },
  {
    $set: {
      value: {
        $round: [
          {
            $sum: [
              {
                $toInt: {
                  $arrayElemAt: [
                    "$value.captures",
                    0
                  ]
                }
              },
              {
                $divide: [
                  {
                    $toInt: {
                      $arrayElemAt: [
                        "$value.captures",
                        1
                      ]
                    }
                  },
                  60
                ]
              }
            ]
          },
          2
        ]
      }
    }
  }
])

Sample Mongo Playground