如何更新Snowflake数组列中空值的dl_country字段

错误原因 #

你遇到的Invalid argument types for function 'OBJECT_INSERT'错误，核心原因是**OBJECT_INSERT函数仅用于操作单个JSON对象**，但你直接传入了数组类型的DETAILS_ARRAY字段，导致参数类型不匹配。

修正后的单条记录更新语句 #

针对你提供的单ID示例，需要先遍历数组中的每个对象，对dl_country为空的对象单独执行OBJECT_INSERT，再重组数组：

MERGE INTO tgt
USING (
  SELECT a_id, d_id, dl_country
  FROM doc_fix
  WHERE a_id = 12321 AND d_id = 88899
) src 
ON tgt.a_id = src.a_id AND tgt.d_id = src.d_id
WHEN MATCHED THEN
  UPDATE SET 
  DETAILS_ARRAY = ARRAY_TRANSFORM(
    tgt.DETAILS_ARRAY,
    obj -> CASE WHEN obj:dl_country = '' THEN OBJECT_INSERT(obj, 'dl_country', src.dl_country) ELSE obj END
  )
;

批量更新所有符合条件的记录 #

如果要批量处理所有DETAILS_ARRAY中存在dl_country为空值的记录，结合关联表doc_fix的逻辑如下：

MERGE INTO tgt
USING (
  SELECT df.a_id, df.d_id, df.dl_country
  FROM doc_fix df
  JOIN tgt t 
    ON t.a_id = df.a_id 
    AND t.d_id = df.d_id
  -- 筛选出数组中存在空dl_country的目标记录
  WHERE EXISTS (
    SELECT 1 
    FROM TABLE(FLATTEN(t.DETAILS_ARRAY)) arr_item
    WHERE arr_item.value:dl_country = ''
  )
) src 
ON tgt.a_id = src.a_id AND tgt.d_id = src.d_id
WHEN MATCHED THEN
  UPDATE SET 
  DETAILS_ARRAY = ARRAY_TRANSFORM(
    tgt.DETAILS_ARRAY,
    obj -> CASE WHEN obj:dl_country = '' THEN OBJECT_INSERT(obj, 'dl_country', src.dl_country) ELSE obj END
  )
;

关键函数说明 #

• ARRAY_TRANSFORM：遍历数组中的每个JSON对象，对符合条件的对象执行更新操作，保留其他对象不变。
• FLATTEN：将数组展开为行，用于检查目标记录的数组中是否存在dl_country为空的对象，避免不必要的更新。
• OBJECT_INSERT：仅对单个JSON对象生效，替换指定键的值（这里是将空的dl_country替换为src.dl_country的值）。

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