Snowflake多列JSON结构转换遇重复键错误，求解决方案

针对test_json表中多列（如SCORE、V3_SCORE）存储key-value数组JSON，转换为键值对格式时的重复键问题，以下是三种实用处理方案：

核心问题原因 #

单列处理时，LATERAL FLATTEN展开单列的key-value数组，OBJECT_AGG可以正常聚合；但多列同时展开时，不同列的key可能重复，触发OBJECT_AGG的重复键报错。

方案1：为不同列的key添加列名前缀（同一层级区分） #

给每个列的key加上对应列名作为前缀，从根源避免键重复，适合需要将所有键放在同一层级的场景：

SELECT
  DAY,
  -- 合并多列的聚合结果
  OBJECT_AGG(CONCAT('SCORE_', kv1.key), kv1.value) 
  || OBJECT_AGG(CONCAT('V3_SCORE_', kv2.key), kv2.value) 
  -- 继续添加其他列，格式同上：|| OBJECT_AGG(CONCAT('COLUMN_NAME_', kvn.key), kvn.value)
  AS ALL_FLATTENED
FROM test_json,
LATERAL FLATTEN(input => PARSE_JSON(SCORE)) kv1,
LATERAL FLATTEN(input => PARSE_JSON(V3_SCORE)) kv2
-- 为其他列添加对应的LATERAL FLATTEN：, LATERAL FLATTEN(input => PARSE_JSON(OTHER_COLUMN)) kvn
GROUP BY DAY;

示例输出：

{
  "SCORE_a": 1,
  "SCORE_b": 2,
  "V3_SCORE_a": 3,
  "V3_SCORE_c": 4
}

方案2：将每列结果作为嵌套对象（保留列结构） #

把每列的聚合结果作为独立嵌套对象，外层用原列名作为key，结构清晰易维护，是5-6列场景的推荐方案：

SELECT
  DAY,
  OBJECT_CONSTRUCT(
    'SCORE', OBJECT_AGG(kv1.key, kv1.value),
    'V3_SCORE', OBJECT_AGG(kv2.key, kv2.value)
    -- 继续添加其他列：'OTHER_COLUMN', OBJECT_AGG(kvn.key, kvn.value),
  ) AS ALL_FLATTENED
FROM test_json,
LATERAL FLATTEN(input => PARSE_JSON(SCORE)) kv1,
LATERAL FLATTEN(input => PARSE_JSON(V3_SCORE)) kv2
-- 为其他列添加对应的LATERAL FLATTEN：, LATERAL FLATTEN(input => PARSE_JSON(OTHER_COLUMN)) kvn
GROUP BY DAY;

示例输出：

{
  "SCORE": {"a":1, "b":2},
  "V3_SCORE": {"a":3, "c":4}
}

方案3：允许重复键覆盖（指定优先级） #

如果业务允许重复键覆盖，且有明确的优先级（比如保留V3_SCORE的key值），可以用FULL OUTER JOIN关联同key的行，并通过OBJECT_AGG的RESOLVE UPDATE处理冲突：

SELECT
  DAY,
  OBJECT_AGG(
    COALESCE(kv2.key, kv1.key),
    COALESCE(kv2.value, kv1.value) -- 优先取V3_SCORE的值，若不存在则取SCORE的
  ) RESOLVE UPDATE AS ALL_FLATTENED
FROM test_json,
LATERAL FLATTEN(input => PARSE_JSON(SCORE)) kv1
FULL OUTER JOIN LATERAL FLATTEN(input => PARSE_JSON(V3_SCORE)) kv2
ON kv1.key = kv2.key
-- 如需添加更多列，依次LEFT JOIN/FULL JOIN对应的FLATTEN结果
GROUP BY DAY;

示例输出（V3_SCORE的a覆盖SCORE的a）：

{
  "a":3,
  "b":2,
  "c":4
}

注意事项 #

• 如果列本身是VARIANT类型，无需PARSE_JSON，直接用列名作为FLATTEN的input参数。
• 若某列的JSON为NULL或空数组，OBJECT_AGG会返回NULL，可通过IFNULL(OBJECT_AGG(...), OBJECT_CONSTRUCT())替换为空对象。

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