如何用Athena SQL从类JSON字符串列生成多行结构化列？

问题原因 #

你遇到的16行重复是因为直接对两个数组UNNEST后关联触发了笛卡尔积（4个old元素 × 4个new元素）。要得到一一对应的4行数据，核心是保留数组元素的位置索引，通过索引关联同一位置的old和new元素。

解决方案 #

Athena基于Presto，支持json_parse解析字符串为JSON数组，同时UNNEST可搭配WITH ORDINALITY获取元素的位置序号。具体实现步骤：

1. 将字符串格式的JSON数组转为JSON数组类型
2. 对两个数组分别UNNEST并带上索引
3. 通过原表主键+索引关联两个结果集，确保同一位置的元素匹配

完整SQL示例 #

假设你的表名为your_table，且表中有唯一标识每行数据的主键original_id：

WITH old_data AS (
    SELECT
        original_id,
        json_extract_scalar(item, '$.name') AS old_name,
        json_extract_scalar(item, '$.id') AS old_id,
        json_extract_scalar(item, '$.value') AS old_value,
        idx
    FROM your_table
    CROSS JOIN UNNEST(json_parse(old_id)) WITH ORDINALITY AS t(item, idx)
),
new_data AS (
    SELECT
        original_id,
        json_extract_scalar(item, '$.name') AS new_name,
        json_extract_scalar(item, '$.id') AS new_id,
        json_extract_scalar(item, '$.value') AS new_value,
        idx
    FROM your_table
    CROSS JOIN UNNEST(json_parse(new_id)) WITH ORDINALITY AS t(item, idx)
)
SELECT
    old_name,
    old_id,
    old_value,
    new_name,
    new_id,
    new_value
FROM old_data
JOIN new_data ON old_data.original_id = new_data.original_id 
             AND old_data.idx = new_data.idx;

简化写法（用zip_with合并数组） #

如果你的Athena版本支持zip_with函数（对应Presto 312及以上），可以用更简洁的方式合并两个数组后拆分：

SELECT
    json_extract_scalar(pair.o, '$.name') AS old_name,
    json_extract_scalar(pair.o, '$.id') AS old_id,
    json_extract_scalar(pair.o, '$.value') AS old_value,
    json_extract_scalar(pair.n, '$.name') AS new_name,
    json_extract_scalar(pair.n, '$.id') AS new_id,
    json_extract_scalar(pair.n, '$.value') AS new_value
FROM your_table
CROSS JOIN UNNEST(
    zip_with(
        json_parse(old_id),
        json_parse(new_id),
        (o, n) -> row(o, n)
    )
) AS t(pair);

关键说明 #

• json_parse：将字符串类型的JSON数组转为Athena可识别的JSON数组类型
• UNNEST(...) WITH ORDINALITY：拆分数组时同步返回元素的位置索引（从1开始计数）
• 关联时必须同时使用原表主键和索引，避免不同行数据的元素错误匹配

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