Snowflake中遍历JSON数组并用object_insert修改字段值的方案

你的问题核心是直接操作了policyTerms数组的单个元素（policyTerms[0]），并将整个数组字段替换为该单个对象，导致数组结构丢失；同时你设置的是字符串'null'，而非SQL标准的空值NULL。

要保留数组结构并批量修改所有元素的bankroutingnumber为NULL，可以利用Snowflake的ARRAY_TRANSFORM函数遍历数组元素，配合OBJECT_INSERT逐层完成字段更新：

正确的UPDATE语句 #

UPDATE test
SET RECORD_CONTENT = OBJECT_INSERT(
    RECORD_CONTENT,
    'messageBody',
    OBJECT_INSERT(
        RECORD_CONTENT:messageBody,
        'policycontainer',
        OBJECT_INSERT(
            RECORD_CONTENT:messageBody:policycontainer,
            'policyTerms',
            ARRAY_TRANSFORM(
                RECORD_CONTENT:messageBody:policycontainer:policyTerms,
                x => OBJECT_INSERT(
                    x,
                    'Billing',
                    OBJECT_INSERT(
                        x:Billing,
                        'checkpayment',
                        OBJECT_INSERT(
                            x:Billing:checkpayment,
                            'bankroutingnumber',
                            NULL,
                            TRUE
                        ),
                        TRUE
                    ),
                    TRUE
                )
            ),
            TRUE
        ),
        TRUE
    ),
    TRUE
);

关键逻辑说明 #

• ARRAY_TRANSFORM(...)：遍历policyTerms数组的每一个元素x，对每个元素执行内层的字段修改操作
• 嵌套的OBJECT_INSERT：逐层深入每个数组元素的结构，将Billing.checkpayment.bankroutingnumber设置为SQL空值NULL（而非字符串）
• 外层OBJECT_INSERT：将修改后的policyTerms数组放回原层级，最终更新整个RECORD_CONTENT字段

执行后，JSON数据会保留数组结构并完成批量修改：

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

内容的提问来源于stack exchange，提问作者Madushan