将地图值取消为Athena/presto中的单独列

Question

我的问题与此有点相似(Athena/Presto-UNNEST映射到列(。但就我而言，我知道我需要什么专栏。

我的用例是这个

我有一个json blob，它包含以下结构

{
"reqId" : "1234",
"clientId" : "client",
"response" : [
{
"name" : "Susan",
"projects" : [
{
"name" : "project1",
"completed" : true
},
{
"name" : "project2",
"completed" : false
}
]
},
{
"name" : "Adams",
"projects" : [
{
"name" : "project1",
"completed" : true
},
{
"name" : "project2",
"completed" : false
}
]
}
]
}

我需要创建一个视图，它将返回类似的输出

name  |  project    |  Completed |
----------+-------------+------------+
Susan |  project1   |   true     |
Susan |  project2   |   false    |
Adams |  project1   |   true     |
Adams |  project2   |   false    |

我尝试了以下方法和其他方法。这是我能得到的最接近的

WITH dataset AS (
SELECT 'Susan' as name, transform(filter(CAST(json_extract('{
"projects": [{"name":"project1", "completed":false}, {"name":"project3", "completed":false},
{"name":"project2", "completed":true}]}', '$.projects') AS ARRAY<MAP<VARCHAR, VARCHAR>>), p -> (p['name'] != 'project1')), p -> ROW(map_values(p))) AS projects
)
SELECT * from dataset
CROSS JOIN UNNEST(projects)

这是我得到的输出

name    projects                                                        _col2
1   Susan   [{field0=[project3, false]}, {field0=[project2, true]}] {field0=[project3, false]}
2   Susan   [{field0=[project3, false]}, {field0=[project2, true]}] {field0=[project2, true]}

我基本上想将映射的键值对作为单独的列来取消测试。在presto/Athena我该怎么做？

Answer 1

您的JSON示例似乎无效，它在"name" : "Susan"和"name" : "Adams"之后缺少一个,。除此之外，您可以通过此查询实现预期输出，您需要UNNEST两次，还需要一些强制转换：

with dataset as
(
select json_parse('{"reqId" : "1234","clientId" : "client","response" : [{"name" : "Susan","projects" : [{"name" : "project1","completed" : true},{"name" : "project2","completed" : false}]},{"name" : "Adams","projects" : [{"name" : "project1","completed" : true},{"name" : "project2","completed" : false}]}]}') as json_col
)
,unnest_response as
(
select * 
from dataset
cross join UNNEST(cast(json_extract(json_col, '$.response') as array<JSON>)) as t (response)
)
select 
json_extract_scalar(response, '$.name') name,
json_extract_scalar(project, '$.name') project_name,
json_extract_scalar(project, '$.completed') project_completed
from unnest_response
cross join UNNEST(cast(json_extract(response, '$.projects') as array<JSON>)) as t (project);