如何在Amazon Athena中拆分JSON的键值对？

Athena 基于 Presto 引擎，UNNEST 本身不支持直接处理 JSON 类型，你只需要先将提取出的 JSON 对象转换为 MAP 类型，再配合 UNNEST 拆解键值对即可实现需求。

修改后的查询语句如下：

WITH dataset AS 
    (SELECT 'engineering' AS department, '{"number_of_assets": {"computer": "95"}}' AS assets )
SELECT 
  department,
  asset_type,
  asset_count
FROM 
    (SELECT 
        department,
        -- 将提取到的JSON对象转换为键值都为字符串的MAP类型
        CAST(
          json_extract(assets, '$.number_of_assets') AS MAP(VARCHAR, VARCHAR)
        ) AS asset_map
    FROM dataset)
-- 拆解MAP结构，得到拆分后的键、值两列
CROSS JOIN UNNEST(asset_map) AS t(asset_type, asset_count)

逻辑说明 #

• CAST(json_extract(...) AS MAP(VARCHAR, VARCHAR))：将JSON对象格式的查询结果，转换为UNNEST可识别的MAP结构
• CROSS JOIN UNNEST(asset_map)：遍历MAP中的每一组键值对，拆分为独立的行，自动生成asset_type（键）、asset_count（值）两列
• 若number_of_assets下存在多组键值对，该语句会自动将所有键值对拆分为多行，符合通用拆分需求

运行上述语句即可得到你期望的查询结果。

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