Convert a string pair of coordinates to geomtery data type in MongoDB

Question

I have a column in a MongoDB collection with location stored as string pair values: geometry:"POINT (72.548355 23.042458)"

I need to create a geometry field which works with MongoDB's spatial queries. Something which is in this format:

"geometry": { "type": "Point", "coordinates": [72.548355, 23.042458] },

Please help. I need to run this for billions of entries in the collection. So an optimized solution will be extremely helpful. Thanks in advance

Answer 1

db.collection.update({},
[
  {
    "$set": {
      "geometry": {
        $let: {
          vars: {
            "arr": { $split: [ "$geometry", " " ] }
          },
          in: {
            "type": { 
              $concat: [
                { $toUpper: { $substr: [ { $first: "$$arr" }, 0, 1 ] } },
                { $toLower: { $substr: [ { $first: "$$arr" }, 1, { $strLenCP: { $first: "$$arr" } } ] } }
              ] 
             },
            "coordinates": [
              {
                $toDouble:{
                  $ltrim: {
                    input: { $arrayElemAt: [ "$$arr", 1 ] },
                    chars: "("
                  }
                }
              },
              {
                $toDouble:{
                  $rtrim: {
                    input: { $arrayElemAt: [ "$$arr", 2 ] },
                    chars: ")"
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
],
{ "multi": true })

mongoplayground