Replace elements in an array field in mongodb in allrecords

Question

{
    "_id": ObjectId("60743bd0ffb98b1e7c215c1b"),
    "orderId": "787968",
    "notes":"Leave in porch"
    "lineItems": [
        {
            "lineItemId": "1741547",
            "channelLineItemId": "9741370294381",
            "skuId": "XXX-YYY-x-PR-YYY"
        },
        {
            "lineItemId": "1741549",
            "channelLineItemId": "9741370359917",
            "skuId": "XXX-YYY-x-PR-YYY"
        },
        {
            "lineItemId": "1741551",
            "channelLineItemId": "9741370425453",
            "skuId": "XXX-YYY-x-P-YYY"
        }
    ],
    "Data": {
        "email_status": "SENT",
        "sent_date": ISODate("2021-04-12T12:23:48.623Z")
    }
}

I had a Collection orderData in my mongo with the above structure.

I need to replace all -PR- and -PG- with a simple hyphen - in all records and in all lineitems. ie, it needs to loop through all the records and all the array elements. if I am using a SQL command I will be using

update line_item set sku_id = replace(sku_id , '-PR-','-');

after executing the command the XYZ-BCD-X-PR-ZAB will be XYZ-BCD-X-AB

I know how to update the elements outside to the array, if I am trying to replace command in notes I can use

db.orderData.find().forEach(function(doc) {
    doc.note : doc.note.replace('-PG-', '-');
    db.orderData.save(doc);
});

I don't know how to use the same in updating in an array

I had tried

db.orderData.find({orderId : "787968"}).forEach(function(doc) {
    doc.lineItems.$.skuId : doc.lineItems.$.skuId.replace('-PG-', '-');
    db.orderData.save(doc);
});

but it is not working.

Someone, please help me and thanks in advance.

in what fields you want to do this? only in skuId ?

Takis
– Takis

2021-10-08 00:19:36 +00:00
Commented Oct 8, 2021 at 0:19 — Takis
– Takis, Commented Oct 8, 2021 at 0:19

ray · Accepted Answer · 2022-06-27 12:16:31Z

4

You can do this through putting an aggregation pipeline in the update:

use $addFields to $map the lineItems array;
use $replaceAll in the $map to replace -PR- with -
repeat step 2 for replace -PG- with *- in another $addFields stage

db.collection.update({
  _id: ObjectId("60743bd0ffb98b1e7c215c1b")
},
[
  {
    "$addFields": {
      "lineItems": {
        $map: {
          input: "$lineItems",
          as: "l",
          in: {
            lineItemId: "$$l.lineItemId",
            channelLineItemId: "$$l.channelLineItemId",
            skuId: {
              "$replaceAll": {
                "input": "$$l.skuId",
                "find": "-PR-",
                "replacement": "-"
              }
            }
          }
        }
      }
    }
  },
  {
    "$addFields": {
      "lineItems": {
        $map: {
          input: "$lineItems",
          as: "l",
          in: {
            lineItemId: "$$l.lineItemId",
            channelLineItemId: "$$l.channelLineItemId",
            skuId: {
              "$replaceAll": {
                "input": "$$l.skuId",
                "find": "-PG-",
                "replacement": "-"
              }
            }
          }
        }
      }
    }
  }
])

Here is the Mongo playground for your reference.

Update as OP using Mongo DB 3.6

As $replaceAll is only available starting from Mongo DB 4.4, the $replaceAll could have been replaced by below workaround:

$split by -PR- or -PG- to break into array of segments
rejoin the array by $reduce and {$concat: ["$$value","-","$$this"]}
For this case, 2 extra hyphens will be introduced in the $concat. Use $substrCP to cut the 2 heading hyphens.

db.collection.aggregate([
  {
    "$addFields": {
      "lineItems": {
        $map: {
          input: "$lineItems",
          as: "l",
          in: {
            lineItemId: "$$l.lineItemId",
            channelLineItemId: "$$l.channelLineItemId",
            skuId: {
              "$reduce": {
                "input": {
                  "$split": [
                    "$$l.skuId",
                    "-PR-"
                  ]
                },
                "initialValue": "",
                "in": {
                  $concat: [
                    "$$value",
                    "-",
                    "$$this"
                  ]
                }
              }
            }
          }
        }
      }
    }
  },
  {
    "$addFields": {
      "lineItems": {
        $map: {
          input: "$lineItems",
          as: "l",
          in: {
            lineItemId: "$$l.lineItemId",
            channelLineItemId: "$$l.channelLineItemId",
            skuId: {
              "$reduce": {
                "input": {
                  "$split": [
                    "$$l.skuId",
                    "-PG-"
                  ]
                },
                "initialValue": "",
                "in": {
                  $concat: [
                    "$$value",
                    "-",
                    "$$this"
                  ]
                }
              }
            }
          }
        }
      }
    }
  },
  {
    $addFields: {
      lineItems: {
        "$map": {
          "input": "$lineItems",
          "as": "l",
          "in": {
            lineItemId: "$$l.lineItemId",
            channelLineItemId: "$$l.channelLineItemId",
            skuId: {
              "$substrCP": [
                "$$l.skuId",
                2,
                1000000// just some very big number
                ]
              }
            }
          }
        }
      }
    }
  ])

Here is the Mongo playground for your reference.

edited Jun 27, 2022 at 12:16

answered Oct 8, 2021 at 0:25

ray

15.6k12 gold badges96 silver badges104 bronze badges

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

4 Comments

Takis Over a year ago

it can become smaller with 1 $map i think like this

ray Over a year ago

Thanks @Takis_ Thats a good point. I think OP should use your concise version in his actual code. I would just keep my slightly tedious version for the demonstration purpose in SO here.

Faseela Thayattuchira Over a year ago

my mongo is version 3.6, I think replaceAll function is not available in that version. is there any function that can support that version 3.6

ray Over a year ago

I updated the answer with an alternative using $split, $concat, $reduce, $strLenCP, which should be available in MongoDB 3.6

Collectives™ on Stack Overflow

Replace elements in an array field in mongodb in allrecords

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related