How to replace substring in MongoDB

Question

I have a lot of mongodb documents in a collection of the form:

{
    "_id" : ObjectId("5d02d179a2d0dead77a4c9c5"),
    "distributed" : false,
    "winner" : false,
    "number" : "855254",
    "dateCreated" : "13-06-2019 19:43:05",
    "date" : ""
}

/* 2 */
{
    "_id" : ObjectId("5d02d179a2d0dead77a4c9c7"),
    "distributed" : false,
    "winner" : false,
    "number" : "263141",
    "dateCreated" : "13-06-2019 19:43:05",
    "date" : ""
}

I want to replace all the data in the "number" field for:

{
    "_id" : ObjectId("5d02d179a2d0dead77a4c9c5"),
    "distributed" : false,
    "winner" : false,
    "number" : "8-55254",
    "dateCreated" : "13-06-2019 19:43:05",
    "date" : ""
}

/* 2 */
{
    "_id" : ObjectId("5d02d179a2d0dead77a4c9c7"),
    "distributed" : false,
    "winner" : false,
    "number" : "2-63141",
    "dateCreated" : "13-06-2019 19:43:05",
    "date" : ""
}

I've tried it in some ways but no success.

Answer 1

Starting Mongo 4.2, you can take advantage of the fact that db.collection.update() now accepts an aggregation pipeline, which allows updating a field based on its current value:

// { number: "855254" }
db.collection.update(
  {},
  [{ $set: {
    number: { $concat: [
      { $substr: [ "$number", 0, 1 ] },
      "-",
      { $substr: [ "$number", 1, -1 ] }
    ]}
  }}],
  { multi: true }
)
// { number: "8-55254" }

• The first part {} is the match query, filtering which documents to update (in this case all documents). If you want to be safe, you can filter on documents whose number value size is larger than 1.

• The second part [{ $set: { number: { $concat: [ ... }] is the update aggregation pipeline:

  • Note the squared brackets signifying the use of an aggregation pipeline.
  • $set (alias of $addFields) is a new aggregation operator which in this case replaces the field's value.
  • $substr is used twice: once to get the first character and another time to get the tail of the string (using -1 as a third parameter of $substr means getting the rest of the string).
  • And $concat joins the two parts with "-".

• Don't forget { multi: true }, otherwise only the first matching document will be updated.