[
  {
    _id:1 value: "8 Aug 2022, 1:13 PM"
  },
  {
    _id:2 value: "15 Aug 2022, 1:13 PM"
  }
]

Want to convert the above documents with the value field as an ISO Date

I was splitting the first part with $split with space as delimitter and then trying to rearrange the value(stuck here) and from a date from $dateFromString

My approach:

db.collection.aggregate([
  {
    "$project": {
      value: {
        "$split": [
          "$value",
          ","
        ]
      }
    }
  },
  {
    "$set": {
      "value": {
        "$arrayElemAt": [
          "$value",
          0
        ]
      }
    }
  },
  {
    "$project": {
      value: {
        "$split": [
          "$value",
          " "
        ]
      }
    }
  },
  
])

which got the result

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "value": [
      "8",
      "Aug",
      "2022"
    ]
  },
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "value": [
      "15",
      "Aug",
      "2022"
    ]
  }
]

Want this to be converted to ISO Date

CodePudding user response：

You can do this in several different ways, here is one example using $dateFromParts, The only issue is you have to manually convert months names to the months number, I did this using $switch, like so:

db.collection.aggregate([
  {
    $addFields: {
      tmpField: { // the tmpField is the same structured you constructed.
        $split: [
          {
            $arrayElemAt: [
              {
                $split: [
                  "$value",
                  ","
                ]
              },
              0
            ]
          },
          " "
        ]
      }
    }
  },
  {
    "$addFields": {
      "newField": {
        "$dateFromParts": {
          year: {
            $toInt: {
              "$arrayElemAt": [
                "$tmpField",
                2
              ]
            }
          },
          "month": {
            "$switch": {
              "branches": [
                {
                  "case": {
                    $eq: [
                      {
                        "$arrayElemAt": [
                          "$tmpField",
                          1
                        ]
                      },
                      "Jan"
                    ]
                  },
                  "then": 1
                },
                 // TODO - Add all months here.
                {
                  "case": {
                    $eq: [
                      {
                        "$arrayElemAt": [
                          "$tmpField",
                          1
                        ]
                      },
                      "Aug"
                    ]
                  },
                  "then": 8
                },
                
              ]
            }
          },
          "day": {
            $toInt: {
              "$arrayElemAt": [
                "$tmpField",
                0
              ]
            }
          }
        }
      }
    }
  },
  {
    $unset: "tmpField"
  }
])

Mongo Playground

As you can see I left a todo for you to add all the other cases to the $switch condition.

CodePudding user response：

Query

• splits the string in parts
• if pm will add 12 to the hour
• uses $dateFromParts, the year/month/hour/minute the month is taken as index of the array that has the months as strings
• i left all the fields to see how it works, you can $unset them

*i don't know if there is simpler way, but you have pm also, and also month is in string, we have $dateFromString also

PlayMongo (put the mouse at the end of each stage to see what it produces)

aggregate(
[{"$set":
   {"months":
     ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep",
      "Oct", "Nov", "Dec"],
    "parts":{"$split":["$value", " "]}}},
 {"$set":
   {"add12?":{"$eq":[{"$arrayElemAt":["$parts", 4]}, "PM"]},
    "hour":
     {"$toInt":
       {"$first":{"$split":[{"$arrayElemAt":["$parts", 3]}, ":"]}}},
    "minute":
     {"$toInt":
       {"$arrayElemAt":
         [{"$split":[{"$arrayElemAt":["$parts", 3]}, ":"]}, 1]}}}},
 {"$set":
   {"date":
     {"$dateFromParts":
       {"year":
         {"$toInt":
           {"$replaceOne":
             {"input":{"$arrayElemAt":["$parts", 2]},
              "find":",",
              "replacement":""}}},
        "month":
         {"$add":
           [{"$indexOfArray":["$months", {"$arrayElemAt":["$parts", 1]}]},
            1]},
        "day":{"$toInt":{"$arrayElemAt":["$parts", 0]}},
        "hour":{"$cond":["$add12?", {"$add":[12, "$hour"]}, "$hour"]},
        "minute":"$minute"}}}}])