可以使用Presto中的json_extract_scalar和json_parse函数来解析JSON字符串并展开对象列表。首先,需要将JSON字符串解析为JSON对象,然后使用json_extract_scalar函数提取需要的字段。最后,使用UNNEST函数将提取的列表展开。
示例代码如下:
SELECT
json_extract_scalar(json_parse(json_string), '$.id') AS id,
json_extract_scalar(nested_object, '$.name') AS name,
json_extract_scalar(nested_object, '$.age') AS age
FROM (
SELECT
json_extract_scalar(json_parse(json_string), '$.list') AS nested_array
FROM table
) a
CROSS JOIN UNNEST(CAST(JSON_PARSE(nested_array) AS ARRAY