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

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

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数组，只是一个字符串
["网红打卡地","看青山游绿水"]

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

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'),
   '(\\[|\\]|")',''),
",")

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

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

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

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)