Presto中JSON_EXTRACT提取数组所有元素报错求助

Presto的JSON_EXTRACT不支持[*]这种通配符式的JSON路径语法，这是导致你报错的核心原因。要实现提取数组中所有amount和category并生成数组列的需求，可以通过JSON类型转换+数组变换函数完成，具体方案如下：

正确SQL语句 #

SELECT
    JSON_EXTRACT_SCALAR(raw_data, '$.label') AS label,
    TRANSFORM(
        CAST(JSON_EXTRACT(raw_data, '$.lines') AS ARRAY<JSON>),
        line -> JSON_EXTRACT_SCALAR(line, '$.amount')::DOUBLE
    ) AS amount,
    TRANSFORM(
        CAST(JSON_EXTRACT(raw_data, '$.lines') AS ARRAY<JSON>),
        line -> JSON_EXTRACT_SCALAR(line, '$.category')
    ) AS category
FROM your_table;

代码说明 #

1. 提取label字段：使用JSON_EXTRACT_SCALAR直接获取字符串类型的label值，相比JSON_EXTRACT会返回不带引号的纯净字符串，更符合需求。
2. 处理lines数组：
   • 先通过CAST(JSON_EXTRACT(raw_data, '$.lines') AS ARRAY<JSON>)将JSON格式的数组转换为Presto原生数组类型，每个元素为独立的JSON对象。
   • 再用TRANSFORM函数遍历数组中的每个JSON对象，提取对应字段：
     • 针对amount，提取后通过::DOUBLE转换为数值类型（可根据实际数据调整为BIGINT等）。
     • 针对category，直接提取字符串即可。

结果验证 #

执行上述SQL后，会得到完全符合你期望的结果：

• label列值：XXX
• amount列值：[1000.0, 100.0, 10.0]（若转换为BIGINT则显示为[1000, 100, 10]）
• category列值：['A', 'B', 'C']

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