Snowflake中嵌套JSON属性的更新方法求助

你遇到的问题是因为直接将OBJECT_INSERT返回的更新后嵌套对象赋值给了整个VAL字段，导致原JSON的其他顶层属性丢失。以下是两种正确的更新方式，可保留完整JSON结构：

方法一：嵌套使用OBJECT_INSERT #

通过嵌套调用OBJECT_INSERT，先更新contacts子对象，再将其替换回原顶层对象中：

UPDATE TEST
SET VAL = OBJECT_INSERT(VAL, 'contacts', OBJECT_INSERT(VAL:contacts, 'email', 'TEST', true), true);

执行后查询结果：

{
  "id": "1",
  "name": {
    "first_name": "ABC",
    "last_name": "XYZ"
  },
  "city_lived": ["CITY1", "CITY2"],
  "contacts": {
    "phone": [
      {"mob": "45345345"},
      {"tel": "4363636"}
    ],
    "email": "TEST"
  }
}

方法二：使用对象合并运算符|| #

利用Snowflake的Variant对象合并特性，更简洁地完成嵌套属性更新：

UPDATE TEST
SET VAL = VAL || {'contacts': VAL:contacts || {'email': 'TEST'}};

这里||会将右侧对象的属性覆盖左侧对象的对应属性，先更新contacts下的email，再将更新后的contacts合并回原顶层对象。

关于JPATH赋值报错的说明 #

Snowflake不支持在UPDATE的SET子句左侧直接使用JPATH（如VAL:contacts.email = 'TEST'）修改嵌套属性，必须通过修改整个Variant字段并重新赋值的方式实现嵌套属性更新。

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