如何通过Athena查询将长JSON字符串拆分为独立列

核心逻辑 #

先将存储JSON字符串的data列解析为数组类型，再通过UNNEST函数把数组元素拆分为独立行，同时保留原表其他字段，最后从拆分出的JSON对象中提取目标字段。

具体SQL示例 #

假设原表名为your_table，除data列外还有id、record_time等字段，data列的JSON结构是包含多个对象的数组，每个对象含cpeIndex、cpeIp等字段：

SELECT
  -- 保留原表的其他字段
  t.id,
  t.record_time,
  -- 从JSON对象中提取目标字段，按需转换类型
  CAST(json_extract(item, '$.cpeIndex') AS INT) AS cpe_index,
  json_extract_scalar(item, '$.cpeIp') AS cpe_ip
  -- 可继续提取其他需要的字段
FROM
  your_table t,
  -- 解析JSON字符串为数组并展开
  UNNEST(json_parse(t.data)) AS items(item)

关键函数说明 #

• json_parse(t.data)：将字符串格式的JSON解析为Athena可处理的JSON数组类型。
• UNNEST(...)：把数组中的每个元素拆成单独一行，自动关联原表对应行的其他字段。
• json_extract_scalar：提取字段的字符串值；若字段是数值类型，用json_extract配合CAST转换类型更合适。

特殊场景处理 #

如果data列存在空数组，UNNEST会直接过滤掉对应行，若需保留这些行，改用左关联写法：

SELECT
  t.id,
  t.record_time,
  CAST(json_extract(item, '$.cpeIndex') AS INT) AS cpe_index,
  json_extract_scalar(item, '$.cpeIp') AS cpe_ip
FROM
  your_table t
LEFT JOIN UNNEST(json_parse(t.data)) AS items(item) ON true

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