Search code examples
mongodbmongoosemongodb-queryaggregation-framework

MongoDB Aggregation - How to convert time string to ISO format


All documents in my collection are the same as this:

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

But I need to convert the time field like this:

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

Solution

    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"
            ]
          }
        }
      }
    ])