All document in my collection are same as this:

{
  "_id": {
    "$oid": "6396c58284bfad036f960288"
  },
  "title": "This is a nice title.",
  "time": "3266 sec"
}

But I need to convert time field like this:

{
  "_id": {
    "$oid": "6396c58284bfad036f960288"
  },
  "title": "This is a nice title.",
  "time": "PT3266S"
}

CodePudding user response：

1. $set - Set the time field. With $regexFind to capture the matched group.

2. $set - Set the time field. With $concat to concat string with "PT", the first element of time.captures array and "S".

db.collection.aggregate([
  {
    $set: {
      time: {
        $regexFind: {
          input: "$time",
          regex: "(\\d ) sec"
        }
      }
    }
  },
  {
    $set: {
      time: {
        $concat: [
          "PT",
          {
            $arrayElemAt: [
              "$time.captures",
              0
            ]
          },
          "S"
        ]
      }
    }
  }
])

Demo @ Mongo Playground

Or you can combine both $set stages into one:

db.collection.aggregate([
  {
    $set: {
      time: {
        $concat: [
          "PT",
          {
            $arrayElemAt: [
              {
                $getField: {
                  field: "captures",
                  input: {
                    $regexFind: {
                      input: "$time",
                      regex: "(\\d ) sec"
                    }
                  }
                }
              },
              0
            ]
          },
          "S"
        ]
      }
    }
  }
])