Modify a value using query console

N1QL
, ,
1

I’m trying to find a way to modify a field value in a deeply nested json document using queries from web console. My document is something like this.

{
  "docid": bdadc59c9db9461fbebd8ae21f5a80b0,
  "pid": 123456,
  "sid": 1,
  "items": [{
    "flag": false,
    "data": {
      "next": "1",
      "cause": ""
    },
    "tz": "-0400",
    "text": "This is my super secret id GVID 1234 keep it safe",
    "ts": "1606971971700"
  }, {
    "flag": false,
    "tz": "-0400",
    "ack": false,
    "text": "Taken care of.  Thank you",
    "ts": "1606972104492"
  }],
  "_id": "bdadc59c9db9461fbebd8ae21f5a80b0.1"
}

I’m able to search for GVID xxxx using select c._id from messages as m where any item in m.items satisfies regex_contains(item.text, "\\bGVID\\s*\\b\\d{3,4}\\b*") END;. This query returns ids of those documents.

What I’m trying to achieve is search and modify the text This is my super secret id GVID 1234 keep it safe to This is my super secret id GVID xxxx keep it safe in a single statement. Is it possible to do from WebConsole? I tried a numer of ARRAY_* operations and I couldn’t get them correct. Appreciate some help from N1QL experts in this community.

2 
UPDATE  messages AS m
SET item.text =   REGEXP_REPLACE(item.text, "\\bGVID\\s*\\b\\d{3,4}\\b*", "xxxx") 
          FOR  item IN m.items WHEN regex_contains(item.text, "\\bGVID\\s*\\b\\d{3,4}\\b*") END
WHERE  ANY item IN m.items  SATISFIES regex_contains(item.text, "\\bGVID\\s*\\b\\d{3,4}\\b*") END;

WHERE clause eliminates the documents that not qualified.
In SET, FOR iterates over each ARRAY element. WHEN clause eliminates ARRAY element that doesn’t qualify and remaining array elements text field is modified.
Finally document is sent to data node.

3

I really appreciate the quick help. This worked. I had to remove the ‘;’ from first line.

1 Like