嵌套的JSON数组的JSON,要使用Hive来进行解析
需要解析的JSON
{
"base": {
"code": "xm",
"name": "project"
},
"list": [{
"ACode": "cp1",
"AName": "Product1",
"BList": [{
"BCode": "gn1",
"BName": "Feature1"
}, {
"BCode": "gn2",
"BName": "Feature2"
}]
}, {
"ACode": "cp2",
"AName": "Product2",
"BList": [{
"BCode": "gn1",
"BName": "Feature1"
}]
}]
}
解析出来的结果应该如下表所示
code | name | ACode | Aname | Bcode | Bname |
---|---|---|---|---|---|
xm | project | cp1 | Product1 | gn1 | Feature1 |
xm | project | cp1 | Product1 | gn2 | Feature2 |
xm | project | cp2 | Product2 | gn1 | Feature1 |
解决方案
首先使用get_json_object
方法,把需要解析的数组解析出来,然后使用regexp_replace
将}]}
,{替换成}]}||{
,然后再使用split方法对||
进行分割,分割成数组后,使用lateral view explode
方法对其进行展开成多列即可。
SELECT
code
, name
, ai.ACode
, ai.AName
, bi.BCode
, bi.BName
FROM
(
SELECT
get_json_object(t.value, '$.base.code') AS code
, get_json_object(t.value, '$.base.name') AS name
, get_json_object(t.value, '$.list') AS list
FROM
(
SELECT
'{"base":{"code":"xm","name":"project"},"list":[{"ACode":"cp1","AName":"Product1","BList":[{"BCode":"gn1","BName":"Feature1"},{"BCode":"gn2","BName":"Feature2"}]},{"ACode":"cp2","AName":"Product2","BList":[{"BCode":"gn1","BName":"Feature1"}]}]}' as value
)
t
)
t lateral view explode(split(regexp_replace(regexp_extract(list,'^\\[(.+)\\]$',1),'\\}\\]\\}\\,\\{', '\\}\\]\\}\\|\\|\\{'),'\\|\\|')) list as a
lateral view json_tuple(a,'ACode','AName','BList') ai as ACode
, AName
, BList lateral view explode(split(regexp_replace(regexp_extract(BList,'^\\[(.+)\\]$',1),'\\}\\,\\{', '\\}\\|\\|\\{'),'\\|\\|')) BList as b
lateral view json_tuple(b,'BCode','BName') bi as BCode
, BName
;
执行完成
xm project cp1 Product1 gn1 Feature1
xm project cp1 Product1 gn2 Feature2
xm project cp2 Product2 gn1 Feature1
Time taken: 0.787 seconds, Fetched: 3 row(s)