解析json array presto 语法

示例：
{data=[{"name":"col1","min":0,"max":32,"avg":29},
{"name":"col2","min":1,"max":35,"avg":21},
{"name":"col3","min":4,"max":56,"avg":34}]}

sql:

with mydata as (
select '{"data":[{"name":"col1","min":0,"max":32,"avg":29},
{"name":"col2","min":1,"max":35,"avg":21},
{"name":"col3","min":4,"max":56,"avg":34}]}' json
)

select max(case when x.name = 'col1' then x.min end) min_col1,
max(case when x.name = 'col3' then x.avg end) avg_col3
from mydata
CROSS JOIN
UNNEST(
CAST(
JSON_EXTRACT(json,'$.data')
as ARRAY(ROW(name VARCHAR, min INTEGER, max INTEGER, avg INTEGER))
)
) as x(name, min, max, avg) --column aliases

示例：
[{"name":"col1","min":0,"max":32,"avg":29},
{"name":"col2","min":1,"max":35,"avg":21},
{"name":"col3","min":4,"max":56,"avg":34}]

sql:

with mydata as (
select '[{"name":"col1","min":0,"max":32,"avg":29},
{"name":"col2","min":1,"max":35,"avg":21},
{"name":"col3","min":4,"max":56,"avg":34}]' json
)

select *
from mydata
CROSS JOIN
UNNEST(
CAST(
json_parse(json)
as ARRAY(ROW(name VARCHAR, min INTEGER, max INTEGER, avg INTEGER))
)
) as x(name, min, max, avg) --column aliases

转：https://www.5axxw.com/questions/content/5pdlaf