Snowflake中ARRAYS_TO_OBJECT函数重复键的SQL处理方案问询

要实现合并可能包含重复键的两个数组（键数组+值数组），且重复键保留最后出现的对应值，可以用以下两种可行方案：

方案一：通过展开数组+去重组装 #

利用FLATTEN展开数组，关联对应位置的键值对，再通过窗口函数保留每个键的最后一个值，最后用OBJECT_AGG组装成对象。

示例代码 #

WITH input_data AS (
  SELECT 
    ARRAY_CONSTRUCT('a', 'b', 'a', 'c') AS key_arr,
    ARRAY_CONSTRUCT(1, 2, 3, 4) AS value_arr
)
SELECT
  OBJECT_AGG(key_val, value_val) AS final_object
FROM (
  SELECT
    f1.value::STRING AS key_val,
    f2.value AS value_val,
    -- 按键分组，保留最后出现的元素（索引越大越晚出现）
    ROW_NUMBER() OVER (PARTITION BY f1.value::STRING ORDER BY f1.index DESC) AS rn
  FROM input_data,
    LATERAL FLATTEN(input_data.key_arr) f1,
    LATERAL FLATTEN(input_data.value_arr, OFFSET => f1.index) f2
)
WHERE rn = 1;

逻辑说明 #

1. FLATTEN展开键数组，同时通过OFFSET => f1.index关联值数组中对应位置的元素，确保键和值一一对应。
2. 用ROW_NUMBER()窗口函数按键分组，按元素出现的索引倒序排序，标记每个键的最后一个出现项（rn=1）。
3. 过滤出每个键的最后一个值，再用OBJECT_AGG将键值对组装成最终对象。

方案二：利用PARSE_JSON的重复键处理特性 #

先将键值对拼接成JSON格式的字符串，再通过PARSE_JSON(expr, 'd')解析，该模式会自动保留重复键的最后一个值。

示例代码 #

WITH input_data AS (
  SELECT 
    ARRAY_CONSTRUCT('a', 'b', 'a', 'c') AS key_arr,
    ARRAY_CONSTRUCT(1, 2, 3, 4) AS value_arr
)
SELECT
  PARSE_JSON(
    '{' || LISTAGG(
      '"' || REPLACE(f1.value::STRING, '"', '\\"') || '":' || TO_JSON(f2.value), 
      ','
    ) WITHIN GROUP (ORDER BY f1.index) || '}', 
    'd'
  ) AS final_object
FROM input_data,
  LATERAL FLATTEN(input_data.key_arr) f1,
  LATERAL FLATTEN(input_data.value_arr, OFFSET => f1.index) f2;

逻辑说明 #

1. 同样通过FLATTEN关联键值对，将每个键转义双引号后拼接成JSON键格式，值用TO_JSON处理成合法的JSON值格式。
2. 用LISTAGG按元素出现顺序拼接成完整的JSON字符串。
3. 调用PARSE_JSON并指定'd'参数，解析时自动覆盖重复键，保留最后出现的值。

注意事项 #

• 如果键包含特殊字符（如双引号），必须用REPLACE转义，避免JSON解析失败。
• 此方案代码更简洁，但处理超大规模数组时，字符串拼接可能存在性能瓶颈，此时方案一更优。

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