字段类型如下,
字段名 | 类型 |
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
处理之后数据如下,
id | userName1 | userName2 |
1 | 王朝 | 王朝 |
1 | 马汉 | 马汉 |
2 | 张龙 | 张龙 |
2 | 赵虎 | 赵虎 |