MongoDB updateMany with string build of substrings-CodePudding

I run over a problem with updating many values. I'm a newbie in terms of MongoDB. I got a task to unify dates in DB. Dates are string and now they are stored in the format yyyyMMdd. I should update it for yyyy-MM-dd. I know that storing dates as a string is far from ideal, but it is caused by implementing data standards from other companies.

I try to use .forEach() but when I was building a string inside a js function StudioT3 claimed that I should use a more modern version because it can't identify a token "{" Then I try to use .updateMany() but it seems to not work at all. Sorry for not including any code but after a few hours I got nothing.

Summarising: I have got a collection of Foo dates in the format "yyyyMMdd" and I need to create a query for the migration tool(in .js) which will convert it for "yyyy-MM-dd". Also, I have done a tutorial today but no-one was detailed enough to cover it. Can you please give me a hint on how to solve it? I don't expect a full solution but I don't even know what should be the proper way to update these values.

I try with forEach(),

SyntaxError: Unexpected token (1:52)

> 1 |   let formatedDate = year   "-"   month   "-"   day;}});
    |                                                     ^
  2 |

SyntaxError: Unsyntactic continue. (3:4)

  1 | db.DummyController.find().forEach(function(myDoc) {
  2 |   {if (myDoc.submissiondate.length != 6) {
> 3 |     continue;
    |     ^
  4 |   }
  5 |

updateMany(), and a bit with aggregation. I expect a hint on how to do it, even not a full solution.

CodePudding user response：

You can use update with aggregation pipeline starting from MongoDB 4.2,

$substr to get sub string from start index and number of character
$concat to concat string returned by above operation

db.collection.updateMany(
  {}, // query
  [{
    $set: {
      date: {
        $concat: [
          { $substr: ["$date", 0, 4] },
          "-",
          { $substr: ["$date", 4, 2] },
          "-",
          { $substr: ["$date", 6, 2] }
        ]
      }
    }
  }
])

Playground

Note: please test this query in the development database before executing it in the production database!