How to bulk replace substring in mongoDB array field with keyword?

Question

{
    "_id": ObjectId("5f08e58ae1f788a8beb30519"),
    "__v": NumberInt("0"),
    "dimensions": {
        "height": NumberInt("720"),
        "width": NumberInt("1080")
    },
    "likes": NumberInt("164"),
    "src": [
        "http://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFfUrBufC7T3R48ehjSrVSQ7JbfkgFybzjLs4tNMXhX7YmuWicPe2NUiaKQ/0",
        "http://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFfkopmyeak2h1oGPkiaHcgcg4DX5swbBHRg6TWibl2ocvpgIaAng0koWMQ/0",
        "http://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFfkxTnwtOurQQkajmtlQzIokIM0Ms6qyYh2FCWaCnZqmxOAyEYZyUgdA/0",
        "http://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFflamticYWfNkheTMtzcz3wSGib01EsurUA3Royy1A1P0W8muEicMcbbegw/0",
        "http://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFfRc3IBJE1amD5BPoELdYjoIvkQ2iaHiaUu0HexWD8niaAJq6fTFIqDgibOA/0"
    ],
    "recommended": true
}

Let's say I have above data structure in mongoDB in a collection "discoveries" and I want to replace http:// in src to https:// like such:

{
    "_id": ObjectId("5f08e58ae1f788a8beb30519"),
    "__v": NumberInt("0"),
    "dimensions": {
        "height": NumberInt("720"),
        "width": NumberInt("1080")
    },
    "likes": NumberInt("164"),
    "src": [
        "https://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFfUrBufC7T3R48ehjSrVSQ7JbfkgFybzjLs4tNMXhX7YmuWicPe2NUiaKQ/0",
        "https://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFfkopmyeak2h1oGPkiaHcgcg4DX5swbBHRg6TWibl2ocvpgIaAng0koWMQ/0",
        "https://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFfkxTnwtOurQQkajmtlQzIokIM0Ms6qyYh2FCWaCnZqmxOAyEYZyUgdA/0",
        "https://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFflamticYWfNkheTMtzcz3wSGib01EsurUA3Royy1A1P0W8muEicMcbbegw/0",
        "https://mmbiz.qpic.cn/mmbiz_jpg/VZYVS8SibfiaibUE9qr4M2nGG50icSCuiaGFfRc3IBJE1amD5BPoELdYjoIvkQ2iaHiaUu0HexWD8niaAJq6fTFIqDgibOA/0"
    ],
    "recommended": true
}

Is there anyway I can do this without going through each record and then update them one by one? (I have millions of records it would take massive among of time, also it would seem inefficient...)

I tried:

db.getCollection("discoveries").updateMany(
  { src: { $exists: true } },
  [{
    $set: { src: {
      $replaceOne: { input: "src.$[]", find: "http://", replacement: "https://" }
    }}
  }]
)

It returns:

[Error] Error: collection.updateOne requires update operator

I think this is exactly what you are looking for: stackoverflow.com/a/56556298/8296184 — virgiliogm
– virgiliogm, Commented Jul 11, 2020 at 7:43
@VirgilioGM there is a little bit of problem with this solution, src is an array rather than a string, so $replaceOne doesn't seem to do it... — Aero Wang
– Aero Wang, Commented Jul 11, 2020 at 7:58
I don't have my computer to try it now, sorry, but I thought it would work using 'src.$[]' as the field to be updated — virgiliogm
– virgiliogm, Commented Jul 11, 2020 at 8:46
@VirgilioGM thx, see my updated question. it returns: collection.updateOne requires update operator — Aero Wang
– Aero Wang, Commented Jul 11, 2020 at 15:10

ambianBeing · Accepted Answer · 2020-07-12 16:37:15Z

0

This solution is more of (ask specific than generic IMO) takes an assumption that all strings in src originally starts with http and are needed to replaced with https.

Starting in MongoDB 4.2, you can use the aggregation pipeline for update operations.

Idea: Is to use an aggregation pipe using $map where all urls in src splitted at 4th index forward joined with https using $concat and $substr. And update src property using $set.

db.collection.updateMany({ src: { $exists: true } }, [
  {
    $set: {
      src: {
        $map: {
          input: "$src",
          as: "u",
          in: {
            $concat: [
              "https",
              {
                $substr: ["$$u", 4, -1],
              },
            ],
          },
        },
      },
    },
  },
]);

NOTE: $split can also be used to tweak this, instead of $substr.

EDIT/UPDATE: Based on comments that some urls could already be starting with https.

db.collection.updateMany({ src: { $exists: true } }, [
  {
    $set: {
      src: {
        $map: {
          input: "$src",
          as: "u",
          in: {
            $cond: {
              if: {
                $eq: [
                  {
                    $size: {
                      $split: ["$$u", "http:"],
                    },
                  },
                  2,
                ],
              },
              then: {
                $concat: [
                  "https:",
                  {
                    $arrayElemAt: [
                      {
                        $split: ["$$u", "http:"],
                      },
                      1,
                    ],
                  },
                ],
              },
              else: "$$u",
            },
          },
        },
      },
    },
  },
]);

edited Jul 12, 2020 at 16:37

answered Jul 11, 2020 at 16:39

ambianBeing

3,5492 gold badges17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aero Wang Over a year ago

an assumption that all strings in src originally starts with http and are needed to replaced with https... that's not exactly how it is in reality

ambianBeing Over a year ago

@AeroWang Ermm.. made this query purely based on the example in the question. So u'r saying some urls in src may already be starting with https?

Aero Wang Over a year ago

u'r saying some urls in src may already be starting with https - yes

ambianBeing Over a year ago

@AeroWang Updated the response! the solution turned out be less than optimal but works. I don't have mongo 4.4 to test out $replaceOne which could greatly work here. Hope someone posts a better solution. You might wanna look at Another way which I would have gone for in case of huge data.

Collectives™ on Stack Overflow

How to bulk replace substring in mongoDB array field with keyword?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related