I have documents with the fields _id, and value. The value field can have value as a string or integer value.

    {_id: 1, value: '13 hr 30mins'},
    {_id: 2, value: '25 hr'},
    {_id: 3, value: '45mins'},
    {_id: 4, value: '8hr 15 mins'},

I want to check if the value field is string and has 'hr' or 'mins' and if yes want to convert the string to value(int) in just hours or don't perform just pass all the documents to the next stage

Expected Output:
    {_id: 1, value: 13.5},
    {_id: 2, value: 25},
    {_id: 3, value: 0.75},
    {_id: 4, value: 8.25},

The problem I am facing is if the value field has no 'hr' or 'mins' terms in it should just skip the convertion. Want to apply the aggregation stage on the criteria on the value the value field has in the document.

I tried doing

{
                            $project : {
                                value : 1
                            },
                            $match : {
                                value : {
                                    $cond :[
                                        {value : {$in : [/hr/i, /mins/i] }},
                                        {
                                                $set: {
                                                  value: {
                                                    $regexFind: {
                                                      input: "$value",
                                                      regex: "(?:(\\d ) hr){0,1}\\s*(?:(\\d ) mins){0,1}"
                                                    }
                                                  }
                                                }
                                              },
                                              {
                                                $set: {
                                                  value: {
                                                    $round: [
                                                      {
                                                        $sum: [
                                                          {
                                                            $toInt: {
                                                              $arrayElemAt: [
                                                                "$value.captures",
                                                                0
                                                              ]
                                                            }
                                                          },
                                                          {
                                                            $divide: [
                                                              {
                                                                $toInt: {
                                                                  $arrayElemAt: [
                                                                    "$value.captures",
                                                                    1
                                                                  ]
                                                                }
                                                              },
                                                              60
                                                            ]
                                                          }
                                                        ]
                                                      },
                                                      2
                                                    ]
                                                  }
                                                }
                                              }
                                                
                                        {}
                                    ]
                                }
                            }
                        }

CodePudding user response：

1. $set - Create regexValue field with below regex:

^(?:(\d )\s{0,1}hr){0,1}\s*(?:(\d )\s{0,1}mins){0,1}$

^ - Start with

(?:) - Non-capturing group

(\d ) - Capturing group with one or more digits

\s{0,1} - With zero or one space only

(?:(\d )\s{0,1}hr){0,1} - With zero or one hr non-capturing group

\s* - Zero or many spaces

(?:(\d )\s{0,1}mins){0,1} - With zero or one mins non-capturing group

$ - Ends of

2. $set - Set value field.

   2.1. $cond - Check regexValue is null.

   2.1.1. If true, then perform data extraction and transformation.

   2.1.2. If false, remain the value field.

3. $unset - Remove regexValue field.

db.collection.aggregate([
  {
    $set: {
      regexValue: {
        $regexFind: {
          input: "$value",
          regex: "^(?:(\\d )\\s{0,1}hr){0,1}\\s*(?:(\\d )\\s{0,1}mins){0,1}$"
        }
      }
    }
  },
  {
    $set: {
      value: {
        "$cond": {
          if: {
            $ne: [
              "$regexValue",
              null
            ]
          },
          then: {
            $round: [
              {
                $sum: [
                  {
                    $toInt: {
                      $arrayElemAt: [
                        "$regexValue.captures",
                        0
                      ]
                    }
                  },
                  {
                    $divide: [
                      {
                        $toInt: {
                          $arrayElemAt: [
                            "$regexValue.captures",
                            1
                          ]
                        }
                      },
                      60
                    ]
                  }
                ]
              },
              2
            ]
          },
          else: "$value"
        }
      }
    }
  },
  {
    $unset: "regexValue"
  }
])

Sample Mongo Playground