Athena中展开Varchar类型JSON数组并实现数据扁平化

问题描述 #

表table1的values字段为Varchar类型，存储JSON数组格式数据，需要将数组中的每个JSON对象拆分为单独行，并提取entity_id等字段。尝试的SQL报错：无法将varchar转换为array(varchar)

原始表数据 #

id   values
1   [{"entity_id":9222.0,"entity_name":"A","position":1.0,"entity_price":133.23,"entity_discounted_price":285.0},{"entity_id":135455.0,"entity_name":"B","position":2.0,"entity_price":285.25},{"entity_id":9207.0,"entity_name":"C","position":3.0,"entity_price":55.0}]
2   [{"entity_id":9231.0,"entity_name":"D","position":1.0,"entity_price":130.30}]

预期结果 #

id entity_id entity_name position entity_price entity_discounted_price
1  9222      A           1        133.23       285.0
1  135455    B           2        285.25       null
1  9207      C           3        55.0         null
2  9231      D           1        130.30       null

错误SQL及报错 #

错误SQL：

select a.*
,sites.entity_id
,sites.entity_name
,sites.position
,sites.entity_price
,sites.entity_discounted_price 
from (select * from table1) a , unnest(cast(values as array(varchar))) as t(sites)

报错信息：无法将varchar转换为array(varchar)

解决方案 #

直接将Varchar转array(varchar)无效，因为目标是解析JSON数组而非字符串数组。需使用Athena的JSON函数处理：

SELECT
    a.id,
    CAST(json_extract_scalar(site, '$.entity_id') AS BIGINT) AS entity_id,
    json_extract_scalar(site, '$.entity_name') AS entity_name,
    CAST(json_extract_scalar(site, '$.position') AS INT) AS position,
    CAST(json_extract_scalar(site, '$.entity_price') AS DOUBLE) AS entity_price,
    CAST(json_extract_scalar(site, '$.entity_discounted_price') AS DOUBLE) AS entity_discounted_price
FROM table1 a
CROSS JOIN UNNEST(json_parse(a."values")) AS t(site)

关键说明 #

1. json_parse(a."values")：将Varchar类型的JSON数组字符串解析为JSON数组类型，这是正确识别JSON结构的核心步骤，替代错误的直接类型转换。
2. UNNEST(...)：将JSON数组拆分为单独行，每个数组元素对应一行数据。
3. json_extract_scalar：提取JSON对象中的标量值，配合CAST转换为对应数据类型（如数字转BIGINT/DOUBLE），确保字段类型符合业务需求。
4. 保留字处理：values是Athena保留字，需用双引号"values"包裹字段名避免语法错误。

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