在athena中从json_array中提取值

t2a7ltrp 于 2021-04-09 发布在 Hive

关注(0)|答案(1)|浏览(593)

我有json数据如下。
[{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":1},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":2},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":3},{"all":{"end":"22:00","start": "11:00"},"dayOfWeek":4},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":5},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":6},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":7}]
我需要提取 "dayOfWeek"，"start "和 "end "的值，我尝试了在presto athena中无法将varchar转换为数组的解决方案，但没有成功。
预期的结果： ```
dayOfWeek start end
1 11:00 22:00
2 11:00 22:00
3 11:00 22:00
4 11:00 22:00
5 11:00 22:00
6 11:00 22:00
7 11:00 22:00

dayOfWeek start end
1 11:00 22:00
2 11:00 22:00
3 11:00 22:00
4 11:00 22:00
5 11:00 22:00
6 11:00 22:00
7 11:00 22:00

Hive presto amazon-athena

来源：https://stackoverflow.com/questions/63053198/extract-values-from-json-array-in-athena

1条答案

按热度按时间

twh00eeo1#

你可以使用下面的查询，用0.172版本验证。

WITH data(value) AS (VALUES
 '[{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":1},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":2},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":3},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":4},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":5},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":6},{"all":{"end":"22:00","start":"11:00"},"dayOfWeek":7}]'
),
parsed(c1) AS (
  SELECT cast(json_parse(value) AS array(json)) 
  FROM data
)
SELECT 
  json_extract_scalar(json1, '$.dayOfWeek') AS "dayOfWeek"
 ,json_extract_scalar(json1, '$.all.start') AS "start"
 ,json_extract_scalar(json1, '$.all.end') AS "end"
FROM parsed, unnest(c1) as t(json1)

赞(0）回复(0）举报 2021-04-09

我来回答

在athena中从json_array中提取值

1条答案

相关问题

热门标签

最新问答