AWS Athena如何拆分含空值的JSON字符串数组并展开？

云上老周

2026-7-19

AWS Athena处理含空白值的JSON字符串拆分多行问题

问题场景

你的Athena表中有一个存储JSON的字符串列，每行可能包含多个JSON对象，同时存在代表null的空白值（如逗号之间的空内容、结尾逗号），需要将该字符串拆分为多行，且空白值要转为SQL的null。

示例数据

id	Value
1	{"col1":"abc"},{"col2":"def"}
2	{"col1":"abc"},
3	{"col1":"abc"},,{"col2":"def"}

期望输出

id	Value
1	{"col1":"abc"}
1	{"col2":"def"}
2	{"col1":"abc"}
2	null
3	{"col1":"abc"}
3	null
3	{"col2":"def"}

原SQL仅能处理无空白值的行（如ID1），因为空白值会导致JSON格式非法，JSON_PARSE无法解析。

解决方案

通过先清理字符串中的空白项，将其替换为合法的JSON null，再解析为数组并拆分：

SELECT 
    id,
    -- 将空字符串转为SQL NULL，非空则解析为JSON
    CASE WHEN s_value = '' THEN NULL ELSE JSON_PARSE(s_value) END AS value
FROM (
    SELECT 
        id,
        -- 分三步清理字符串：处理开头逗号、结尾逗号、中间连续逗号
        REGEXP_REPLACE(
            REGEXP_REPLACE(
                REGEXP_REPLACE(value, '^,', 'null,'), -- 替换开头的逗号为null,
                ',$', ',null' -- 替换结尾的逗号为,null
            ),
            ',,', ',null,' -- 替换中间的连续逗号为,null,
        ) AS cleaned_value
    FROM table_name
),
-- 将清理后的字符串转为JSON数组并展开为多行
UNNEST(CAST(JSON_PARSE('[' || cleaned_value || ']') AS ARRAY<VARCHAR>)) AS t(s_value)

逻辑说明

字符串清理：通过三次正则替换，把所有代表null的空白位置替换为null，确保最终包裹成的JSON数组格式合法：
- 开头逗号：,xxx → null,xxx
- 结尾逗号：xxx, → xxx,null
- 中间连续逗号：xxx,,xxx → xxx,null,xxx
JSON数组解析：将清理后的字符串用[]包裹，解析为JSON数组
拆分多行：用UNNEST将数组展开为多行，同时通过CASE把空字符串转为SQL标准的null