hive中解析json数组[转载chenzechao]

-- 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
;