AWS Athena从字符串列的JSON列表提取数据时返回空值的问题解决请求

嗨，看起来你遇到的问题核心在于listItems字段存储的是JSON数组，而不是单个JSON对象，直接用json_extract_scalar去提取顶层属性自然会返回空值。咱们来一步步修正这个查询：

问题根源分析 #

你当前的查询里，json_parse(listItems)得到的是一个包含多个对象的JSON数组（比如[{"manufacture_date":"2023-01-01",...}, {...}]），而json_extract_scalar(listItems,'$.manufacture_date')是在尝试从顶层结构提取manufacture_date属性——但顶层是数组，根本没有这个属性，所以返回空值。

修正后的查询方案 #

我们需要先把JSON数组拆分成单独的行（每个数组元素对应一行），再从每个元素里提取属性。可以用UNNEST配合json_parse来实现：

WITH raw_data AS (
    SELECT
        id,
        name,
        title,
        json_parse(listItems) AS list_items_array
    FROM purchase_list
)
SELECT
    id,
    name,
    title,
    json_extract_scalar(item, '$.manufacture_date') AS manufacture_date,
    json_extract_scalar(item, '$.purchase_price') AS purchase_price
FROM raw_data
CROSS JOIN UNNEST(list_items_array) AS t(item)

代码解释 #

1. json_parse(listItems)：把字符串类型的listItems转换成Athena能识别的JSON数组
2. CROSS JOIN UNNEST(list_items_array)：将数组中的每个JSON对象拆分成独立的行，每个行对应一个对象
3. json_extract_scalar(item, '$.xxx')：现在针对每个拆分出来的单个JSON对象item提取属性，就能正确拿到对应的值了

简化版查询（可选） #

如果你的Athena环境支持，也可以省略CTE，直接在主查询里处理：

SELECT
    id,
    name,
    title,
    json_extract_scalar(item, '$.manufacture_date') AS manufacture_date,
    json_extract_scalar(item, '$.purchase_price') AS purchase_price
FROM purchase_list
CROSS JOIN UNNEST(json_parse(listItems)) AS t(item)

执行这个查询后，就能得到你期望的输出：每个数组元素对应一行数据，正确提取出manufacture_date和purchase_price的值。

备注：内容来源于stack exchange，提问作者vvazza