Loop through snowflake array with object

I have JSON data in snowflake table as follows

{
  "audit": "ss",
  "gdapi": "ww",
  "lock": "aa",
  "messageBody": {
    "id": 111,
    "policycontainer": {
      "policyTerms": [
        {
          "Billing": {
            "checkpayment": {
              "bankroutingnumber": "value1"
            }
          }
        },
        {
          "Billing": {
            "checkpayment": {
              "bankroutingnumber": "value2"
            }
          }
        }
      ]
    }
  }
}

I want to change the value of bankroutingnumber to null for that I've written the following query

update test
set RECORD_CONTENT =
object_insert(RECORD_CONTENT, 'messageBody',
object_insert(parse_json(RECORD_CONTENT:messageBody), 'policycontainer',
object_insert(parse_json(RECORD_CONTENT:messageBody):policycontainer, 'policyTerms',
object_insert(parse_json(RECORD_CONTENT:messageBody):policycontainer:policyTerms[0], 'Billing',
object_insert(parse_json(RECORD_CONTENT:messageBody):policycontainer:policyTerms[0]:Billing, 'checkpayment', 
object_insert(parse_json(RECORD_CONTENT:messageBody):policycontainer:policyTerms[0]:Billing:checkpayment,'bankroutingnumber','null',true), true),true), true), true),true)

After running this the result looks like follow

{
  "audit": "ss",
  "gdapi": "ww",
  "lock": "aa",
  "messageBody": {
    "id": 111,
    "policycontainer": {
      "policyTerms": {
        "Billing": {
          "checkpayment": {
            "bankroutingnumber": "null"
          }
        }
      }
    }
  }
}

now the array is gone. I want to keep the array and loop through policyTerms and edit all bankroutingnumber to null

CodePudding user response：

In this case, you may use LATERAL FLATTEN for "RECORD_CONTENT:messageBody.policycontainer.policyTerms" and then OBJECT_AGG to make them one value again, and update the column but I think you could just use a simple regexp_replace for a "faster" query:

select parse_json( regexp_replace( RECORD_CONTENT::STRING, '"bankroutingnumber":"[^"]*"','"bankroutingnumber":"null"')) from test;

update version:

update test 
set RECORD_CONTENT = parse_json( regexp_replace( RECORD_CONTENT::STRING, '"bankroutingnumber":"[^"]*"','"bankroutingnumber":"null"'));