Snowflake如何向表首行嵌套JSON的指定内层插入新键值对

• 表名：MY_TABLE，仅包含1个名为VALUE的列
• 需求：在JSON结构内层的VALUE对象中新增键值对"c125": "job"，最终得到两层嵌套的正确JSON结构
• 原有错误写法将新键插入到了JSON最外层，和内层对象的父级VALUE键平级，不符合要求
  错误SQL：

  SELECT object_insert(OBJECT_CONSTRUCT(*),'c125', 'job') FROM MY_TABLE;
  

  错误返回结构：

  {
    "VALUE": {
      "c1": "name",
      "c10": "age",
      "c100": "gender",
      "c101": "address",
      "c102": "status"
    },
    "c125": "job"
  }
  

错误原因是OBJECT_CONSTRUCT(*)会将表的所有列（当前场景下只有VALUE列）构造成最外层JSON对象，直接对这个最外层对象调用OBJECT_INSERT，新键自然会被加到最外层。你需要先给内层的字段对象新增键，再重新组装外层结构即可。

场景1：仅查询返回符合要求的JSON结构 #

直接用以下SQL即可，逻辑是先给VALUE列存储的内层字段对象新增c125键，再将更新后的内层对象作为外层VALUE键的值，最终返回正确的两层结构：

SELECT 
  OBJECT_INSERT(
    OBJECT_CONSTRUCT(*),
    'VALUE',
    OBJECT_INSERT(VALUE, 'c125', 'job'),
    TRUE
  ) AS correct_json
FROM MY_TABLE;

参数说明：

• 最后一个参数TRUE表示如果外层已存在VALUE键，直接用更新后的内层对象覆盖原有值，避免重复键
• 返回结果完全符合预期结构：

  {
    "VALUE": {
      "c1": "name",
      "c10": "age",
      "c100": "gender",
      "c101": "address",
      "c102": "status",
      "c125": "job"
    }
  }
  

场景2：直接更新表中存储的原始JSON数据 #

如果你的VALUE列本身存储的就是两层结构的JSON（即列内直接存储最开始描述的带外层VALUE键的JSON内容），需要永久修改表内数据，使用以下UPDATE语句：

UPDATE MY_TABLE
SET VALUE = OBJECT_INSERT(
  VALUE,
  'VALUE',
  OBJECT_INSERT(VALUE:VALUE, 'c125', 'job'),
  TRUE
);

语句逻辑和查询场景一致：先通过VALUE:VALUE定位到列内JSON的内层字段对象，给它加完新键后，覆盖外层的VALUE键对应的值即可。

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