Replace string in one node of Mongodb document

Question

I need your help to write one replace query. I have connected one third party software mongodb. I am SQL guy and not able to write query of replace specific node value.

I am using Robo 3T software. Below is one record json from the document.

{
    "_id" : ObjectId("6036c343445f3640c53e0a"), 
    "shares" : [ 
        {
            "shareId" : ObjectId("6035cc6acf345f3640c53d1a"),
            "type" : "user"
        }, 
        {
            "shareId" : ObjectId("6035b536cf345f3640c53cae"),
            "type" : "group",
            "rule" : "view",
            "subscribe" : false
        }
    ],
    "tags" : [],
    "lastUsed" : ISODate("2021-03-09T16:51:30.059Z"),
    "usageCount" : 1,
    "layout" : {
       too many values with multiple node.
    },
    "instanceType" : "owner",
    "original" : null,
    "editing" : true,
    "filters" : [ 
        {
            "jaql" : {
                "table" : "Xyz",
                "column" : "title",
                **"dim" : "[dbo.ketan.Title]",**
                }
            }
            "disabled" : false
        }, 
        {
            "jaql" : {
                "table" : "abc",
                "column" : "name",
                **"dim" : "[dbo.ketan.name]",**
                "datatype" : "text",
                "merged" : true,
                "title" : "Inventor Name",
                "filter" : {
                    "explicit" : false,
                    "multiSelection" : true,
                    "all" : true
                },
                "collapsed" : false
            },
            "instanceid" : "1B1E4-F6DC-28",
            "isCascading" : false
        }, 
        
}

Here I want to replace "dim" : "[dbo.ketan.name]", to "dim" : "[kiran.name]",

Can you please help me to write query ?

This is my first post. The Writing may be not up to mark. Please let me know if you need any more details.

Thank you.

Does this answer your question? [How do you update objects in a document's array (nested updating)](https://stackoverflow.com/questions/10522347/how-do-you-update-objects-in-a-documents-array-nested-updating) — Bilal Bin Zia, Feb 01 '22 at 08:04
Thanks @BilalBinZia For pointing out link. But I want like operator without id selection. I want something like replace "dim" value dbo.ketan% with kiran. other string will be intact. — Mr. K.D., Feb 01 '22 at 19:19
I'd also like to learn how this is done. Here's a [mongoplayground.net](https://mongoplayground.net/p/1-QoQWwFUYe) configuration to get somebody started. — rickhg12hs, Feb 01 '22 at 23:28
Do you want `"[dbo.ketan.Title]"` changed to `"[kiran.Title]"` also? — rickhg12hs, Feb 03 '22 at 00:24

score 1 · Answer 1 · answered Feb 02 '22 at 11:44

1

This is how you can acheive the functionality. Although your MongoDB Version should be >= 3.6

db.collection.update(                                     //collection
  {"filters.jaql.dim": {"$regex": "dbo.ketan"}},          //update filter
  {"$set": {"filters.$[elem].jaql.dim": "[kiran.name]"}}, //update action
  {
    "arrayFilters": [
      {
        "elem.jaql.dim": {
          "$regex": "dbo.ketan"
        }
      }],
    "multi": true
  }                                               //filters for all element to be updated
)

To see this logic in action: MongoPlayground Example

answered Feb 02 '22 at 11:44

Bilal Bin Zia

596
3
12

It's close, but I think the OP also wanted the other change to be `"[kiran.Title]"`. Hopefully the OP will clarify. If `"[kiran.Title]"` is also desired, can you do a mongoplayground.net example that would do both changes? – rickhg12hs Feb 02 '22 at 20:15
Thank you. This one is close to answer. but still the collection is sorting. Can we off sorting ? Thank you so much once again! – Mr. K.D. Feb 03 '22 at 00:10
1

Thanks @rickhg12hs for sharing the website. https://mongoplayground.net/p/nxCI9mzx14- Bilal, is it possible for you to implement on this link ? I appreciate your efforts! – Mr. K.D. Feb 03 '22 at 00:15
1

Also the kiran.name will not be statics. in example "dim" : "[dbo.ketan.name]", to "dim" : "[kiran.name]", it may possible "dim" : "[dbo.ketan.address]", to "dim" : "[kiran.address]", need to replace dbo.ketan with empty string and keep all other things as it is. – Mr. K.D. Feb 03 '22 at 00:32
@rickhg12hs you can see the official documentation and update accordingly. For example if you want to keep the current field and add another, you can just use `$addToSet` instead of `$set`. Similarly you can update different update actions offered by MongoDB – Bilal Bin Zia Feb 03 '22 at 07:27
@KetanPatel I have shared mongoplayground.net link in my answer at the end. as for your other concern, i can provide help with the approach and logic. You will have to modify according to your own requirements. you will have to handle splitiing strings and implementing update logic according to yur requirement. We have used the `$regex` which works as like. you can explore this property and suit them to your needs – Bilal Bin Zia Feb 03 '22 at 07:33
Since `arrayFilters` can't be used with a pipeline, how can `jaql.dim` be modified from `"[dbo.ketan.name]"` to `"[kiran.name]"` **AND** `"[dbo.ketan.Title]"` to `"[kiran.Title]"`? – rickhg12hs Feb 05 '22 at 11:56

Replace string in one node of Mongodb document

1 Answers1