Hive json数组转成多行

wachoo

已于 2022-03-18 16:18:51 修改

阅读量3.2k

点赞数 2

分类专栏： Hive 文章标签： json hive

于 2022-03-18 16:17:48 首次发布

本文链接：https://blog.csdn.net/byd_chao/article/details/123577400

版权

Hive 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

JSON格式文件：

[{"id":"1","status":"0","jd_no":"2424234333"},{"id":"10","status":"0","jd_no":"2424114333"},{"id":"3","status":"0","jd_no":"2124234333"},{"id":"4","status":"0","jd_no":"2424231233"},{"id":"5","status":"0","jd_no":"1224234333"},{"id":"7","status":"0","jd_no":"2424231233"}]

select  get_json_object(j_column,'$.id') as id,
get_json_object(j_column,'$.status') as status,
get_json_object(j_column,'$.jd_no') as jd_no

from
 
(SELECT split(regexp_replace(regexp_extract(t.json_column, '(\\[)(.*?)(\\])', 2), '\\},\\{', '\\}|\\{'), '\\|') AS json_column
FROM (
    SELECT '[{"id":"1","status":"0","jd_no":"2424234333"},{"id":"10","status":"0","jd_no":"2424114333"},{"id":"3","status":"0","jd_no":"2124234333"},{"id":"4","status":"0","jd_no":"2424231233"},{"id":"5","status":"0","jd_no":"1224234333"},{"id":"7","status":"0","jd_no":"2424231233"}]' AS json_column
) t
) a
lateral view explode(json_column) b as j_column;