【转载】hive 中统计某字段json数组中每个value出现的次数

痛定思痛。 2022-11-25 10:04 259阅读 0赞

有这样一个json串

{ "viewdata":[{ "city_id":"59","position_id":0,"qd_title":"网红打卡地","list_id":35},{ "city_id":"59","position_id":1,"qd_title":"看青山游绿水","list_id":37}]}

需要将json数组里的qd\_title都提取出来转换成hive中的array数组。下面介绍两种方法

## 法一get\_json\_object+正则 ##

1.首先可以使用get\_json\_object函数，提取出数组，但是这个返回的是一个字符串

select get_json_object('{"viewdata":[{"city_id":"39","position_id":0,"qd_title":"网红打卡地","list_id":135}, {"city_id":"39","position_id":1,"qd_title":"看青山游绿水","list_id":327}]}',
    '$.viewdata[*].qd_title')
    
    -- 返回,注意这不是一个array数组，只是一个字符串
    ["网红打卡地","看青山游绿水"]

2.将字符串中的\[ \] "都去掉，形成一个,分割的字符串

regexp_replace('${刚刚得到的字符串}','(\\[|\\]|")','')

3.使用字符串分割函数split

select 
    split(
       regexp_replace(
          get_json_object('{"viewdata":[{"city_id":"39","position_id":0,"qd_title":"网红打 卡地","list_id":135}, {"city_id":"39","position_id":1,"qd_title":"看青山游绿水","list_id":327}]}',
          '$.viewdata[*].qd_title'),
       '(\\[|\\]|")',''),
    ",")

4.整体使用LATERAL VIEW 打平数组进行统计

SELECT qdtitle,COUNT(DISTINCT uuid) uv
      FROM ba_travel.bas_log_sdk_mt_mv a LATERAL VIEW explode(split(regexp_replace(get_json_object(a.event_attribute['custom'],'$.viewdata[*].qd_title'),'(\\[|\\]|")',''),",")) b AS qdtitle
     GROUP BY qdtitle

法二 正则匹配  
1.观察json数组中每一个元素都是由\{\}保卫，由,分割，所以可以使用\`\`\}，\`\`\`对字符串进行拆分

-- event_attribute['custom'] 对应的就是上面的json字符串
    split(event_attribute['custom'],'"}')

2.对分割出来的每一个元素进行正则匹配，提取出qd\_title对应的value

-- qd_titles 为上面分割出数组的一个元素
    regexp_extract(qd_titles,'qd_title...([^"]+)',1)

3.整体使用later view 将数组打平

SELECT regexp_extract(qd_titles,'qd_title...([^"]+)',1) title,
           COUNT(DISTINCT uuid) uv
      FROM ba_travel.bas_log_sdk_mt_mv a LATERAL VIEW  explode(split(event_attribute['custom'],'"}')) b as qd_titles 
     GROUP BY regexp_extract(qd_titles,'qd_title...([^"]+)',1)

原文链接：https://cloud.tencent.com/developer/article/1336159