-- hive中解析json数组
select
t1.status
,substr(ss.col,1,10) as col
,t3.evcId
,t3.evcLicense
,t3.evcAddress
,t3.modelName
from (
select
get_json_object(json,"$.status") as status
,split(
regexp_replace(
regexp_extract(
get_json_object(json,"$.data") -- 获取data数组,格式[{json},{json}]
,'^\\[(.+)\\]$'
,1
) -- 删除字符串前后的[],格式{json},{json}
,'\\}\\,\\{'
, '\\}\\|\\|\\{'
) -- 将josn字符串中的分隔符代换成||,格式{json}||{json}
,'\\|\\|'
) as str -- 按||分隔符切割成一个hive数组
from tmp_json_test
) t1
lateral view explode(t1.str) ss as col -- 将hive数组转成行
lateral view json_tuple(ss.col,'evcId','evcLicense','evcAddress','modelName') t3 as evcId,evcLicense,evcAddress,modelName
;
取JSON数组中的各个元素的每个字段
-- hive中解析json数组
select
t1.id
,t1.house_type_img_width
,t1.house_type_img_height
-- ,ss.col as json
,t3.build_id
,t3.build_name
,t3.type
,get_json_object(t3.area,'$.[0]') as area
from (
select
id
,house_type_img_width
,house_type_img_height
,split(
regexp_replace(
regexp_extract(
hot_area
,'^\\[(.+)\\]$'
,1
) -- 删除字符串前后的[],格式{json},{json}
,'\\}\\,\\{'
, '\\}\\|\\|\\{'
) -- 将josn字符串中的分隔符代换成||,格式{json}||{json}
,'\\|\\|'
) as str -- 按||分隔符切割成一个hive数组
from ods_com_t_project_property
where db_name = 'DB_NAME'
and hot_area like '%building%'
) t1
lateral view explode(t1.str) t2 as col -- 将hive数组转成行
lateral view json_tuple(t2.col,'build_id','build_name','type','area') t3 as build_id,build_name,type,area
;
hot_area字段数据
[
{
"build_id": "54ee2ecd-28b6-4399-8d16-c981fadf1fa3",
"build_name": "一千期-1住宅",
"type": "building",
"area": [
[
453.61,
418.35
]
]
},
{
"build_id": "cb682d57-5ef3-44d1-b664-0a8bc77501da",
"build_name": "一千期-2住宅",
"type": "building",
"area": [
[
686.82,
339.42
]
]
},
{
"build_id": "105dc866-aa3f-448b-99d1-bac88451fbd2",
"build_name": "一千期-3住宅",
"type": "building",
"area": [
[
906.18,
246.85
]
]
},
{
"build_id": "121e508a-56c7-49e6-8a8b-04ed62f81c05",
"build_name": "一千期-4住宅",
"type": "building",
"area": [
[
1130.15,
173.59
]
]
},
{
"build_id": "c955a601-35c0-45a4-b28e-0a8a24804f20",
"build_name": "一千期-5住宅",
"type": "building",
"area": [
[
557.52,
662.89
]
]
},
{
"build_id": "c2309075-8402-4c4a-8531-5f9ad4460950",
"build_name": "一千期-6住宅",
"type": "building",
"area": [
[
917.72,
544.71
]
]
},
{
"build_id": "6afa1932-1ec8-411c-9909-db48adeccb3e",
"build_name": "一千期-7住宅",
"type": "building",
"area": [
[
1125.53,
466.42
]
]
}
]
RESULT:
+------------+----------------------+-----------------------+------------+------------+------------+------------+
| id | house_type_img_width | house_type_img_height | build_id | build_name | type | area |
+------------+----------------------+-----------------------+------------+------------+------------+------------+
| 11111111-36bc-2161-6591-11111111111 | 2309 | 1732 | 54ee2ecd-28b6-4399-8d16-c981fadf1fa3 | 一千期-1住宅 | building | [453.61,418.35] |
| 11111111-36bc-2161-6591-11111111111 | 2309 | 1732 | cb682d57-5ef3-44d1-b664-0a8bc77501da | 一千期-2住宅 | building | [686.82,339.42] |
| 11111111-36bc-2161-6591-11111111111 | 2309 | 1732 | 105dc866-aa3f-448b-99d1-bac88451fbd2 | 一千期-3住宅 | building | [906.18,246.85] |
| 11111111-36bc-2161-6591-11111111111 | 2309 | 1732 | 121e508a-56c7-49e6-8a8b-04ed62f81c05 | 一千期-4住宅 | building | [1130.15,173.59] |
| 11111111-36bc-2161-6591-11111111111 | 2309 | 1732 | c955a601-35c0-45a4-b28e-0a8a24804f20 | 一千期-5住宅 | building | [557.52,662.89] |
| 11111111-36bc-2161-6591-11111111111 | 2309 | 1732 | c2309075-8402-4c4a-8531-5f9ad4460950 | 一千期-6住宅 | building | [917.72,544.71] |
| 11111111-36bc-2161-6591-11111111111 | 2309 | 1732 | 6afa1932-1ec8-411c-9909-db48adeccb3e | 一千期-7住宅 | building | [1125.53,466.42] |
+------------+----------------------+-----------------------+------------+------------+------------+------------+
