Keeping part of the value of a field in JSON docs

SQL++
1

Hi,

I have a doc that looks like that:

{
  "elements": [
      {
            "elementId": "CODE",
            "value": "DOC 0011111"
      },   
     {
           "elementId": "VALUE",
           "value": "default value"
      }
 ],
  "_id": "d13818f7-20de-48ae-b5c6-1a52f99b9715"
}

I would like to remove the “DOC” part from the “elementId”: “CODE” in order to only keep the numbers. How could I do that? I know I have to use an UPDATE statement to update all the elementId that corresponds to CODE but I am not sure how I can get the new value, i.e. the integer part of the code that I want to keep (in this case, 0011111).

Remark: not all documents have this doc part, and some don’t have any value at all.

Thanks for your help.

Best,

Erica

2 
UPDATE default AS d
SET e.`value` = SUBSTR(e.`value`,4) FOR e IN d.elements WHEN e.elementId = "CODE" AND e.value LIKE "DOC %" END
WHERE .....