Presto: JSON提取多个层
Presto: JSON提取多个层
提问于 2020-01-29 07:12:06
我试图在每个服务下提取接受检查的名称和状态(多个)。我试过了
TRANSFORM(CAST(JSON_PARSE(json_format(json_extract(json_extract(services_data, '$.services'), '$.inspections'))) AS ARRAY<JSON>), x -> JSON_EXTRACT_SCALAR(x, '$.title')) AS inspection,有办法做到这一点吗？我检查了张贴在相同的其他问题，但没有看到这些复杂的层。
回答 1
Stack Overflow用户
回答已采纳
发布于 2020-01-29 23:04:26
我获取了您的数据，添加了更多用于测试的内容，并提出了以下SQL查询：
with example(record) as (
values(json '{"services":[{"category_id":1,"completed":true,"created_at":"2020-01-27T20:44:57Z","hazmats":[],"inspections":[{"created_at":"2020-01-27T20:44:57Z","detail":null,"inspection_id":10209887,"name":"Accelerator Pedal","state":"green","updated_at":"2020-01-27T20:44:57Z"},{"created_at":"2020-01-27T20:44:57Z","detail":null,"inspection_id":10209887,"name":"Accelerator Pedal 2","state":"orange","updated_at":"2020-01-27T20:44:57Z"}]},{"category_id":1,"completed":true,"created_at":"2020-01-27T20:44:57Z","hazmats":[],"inspections":[{"created_at":"2020-01-27T20:44:57Z","detail":null,"inspection_id":10209887,"name":"Some dummy name","state":"navy","updated_at":"2020-01-27T20:44:57Z"}]}]}')
)
select name, "state"
from example
cross join unnest(cast(json_extract(record, '$.services') as array<json>)) as u(service)
cross join unnest(cast(json_extract(service, '$.inspections') as array(row(name varchar, "state" varchar)))) as x(name,"state");结果如下所示：
name | state
------------------------------
Accelerator Pedal | green
Accelerator Pedal 2 | orange
Some dummy name | navy在我看来这就是你要找的东西。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59958462
复制相关文章
相似问题
腾讯云开发者
