如何将Athena表中JSON格式数据转换为规整表格格式？

步骤1：合并并解析完整JSON #

先把拆分的col0和col1拼接成完整JSON字符串，再用Athena的JSON函数解析：

WITH full_json_data AS (
    SELECT 
        json_parse(concat(col0, col1)) AS parsed_json
    FROM your_table_name
)

步骤2：动态提取键值对（键值对格式） #

如果可以接受键值对形式的输出，直接展开所有键值即可：

SELECT 
    entry.key AS column_name,
    entry.value AS column_value
FROM full_json_data,
unnest(map_entries(parsed_json)) AS t(entry)

步骤3：动态转置为列形式（适配Grafana） #

Athena原生不支持完全动态的Pivot操作，若要输出列格式且避免硬编码，可通过Grafana变量实现：

1. 获取所有JSON键：

SELECT DISTINCT key FROM your_table_name, unnest(map_keys(json_parse(concat(col0, col1)))) AS t(key)

2. 在Grafana中配置变量并生成动态SQL：
   • 将上述查询结果设为Grafana变量（如json_keys）
   • 编写动态SQL，通过变量自动拼接提取语句：

   SELECT 
       ${json_keys:json}
   FROM (
       SELECT json_parse(concat(col0, col1)) AS parsed_json
       FROM your_table_name
   ) t
   

   注：Grafana变量会自动将每个键转换为json_extract_scalar(parsed_json, '$.键名') AS "键名"的格式，实现动态列提取。

替代方案：重新配置Glue Crawler #

若允许调整数据源配置，修改Glue Crawler的JSON解析设置，确保它将完整JSON对象识别为单条记录，而非拆分到多列，这样生成的表直接就是所需结构，无需额外SQL转换。

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