hive中解析json数组

最新推荐文章于 2024-08-15 11:29:03 发布

浅笑古今

最新推荐文章于 2024-08-15 11:29:03 发布

阅读量778

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/u012735708/article/details/108006565

版权

SQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

字段类型如下，

字段名	类型
id	String
orderinfo	String

数据示例如下，

id	orderinfo
1	[{"Name":"王朝","orderDate":"2020-08-14"},{"Name":"马汉","orderDate":"2020-08-14"}]
2	[{"Name":"张龙","orderDate":"2020-08-14"},{"Name":"赵虎","orderDate":"2020-08-14"}]

针对此类数据的思路是，

select 
    id
    ,get_json_object(concat('{',userName,'}'),'$.Name') as userName1 --方法1
    ,REGEXP_EXTRACT(userName,'"Name":"(.*)?","orderDate',1) as userName2 --方法2
from (
select '1' as id,'[{"Name":"王朝","orderDate":"2020-08-14"},{"Name":"马汉","orderDate":"2020-08-14"}]' as orderinfo
union all
select '2' as id,'[{"Name":"张龙","orderDate":"2020-08-14"},{"Name":"赵虎","orderDate":"2020-08-14"}]' as orderinfo
)
LATERAL VIEW explode(split(regexp_replace(regexp_replace(orderinfo,'\\[\\{',''),'}]',''),'},\\{'))userName as userName

处理之后数据如下，