hive 数组json解析

hive 数组json解析

数据：’[{a:1,b:1},{a:2,b:2},{f:6,k:7}]’

sql1: select case when ss.col regexp ‘^\{’ and not ss.col regexp ‘\}KaTeX parse error: Got function '\newline' with no arguments as superscript at position 1: \̲n̲e̲w̲l̲i̲n̲e̲’ then concat(’{’,ss.col) when not ss.col regexp ‘^\{’ and not ss.col regexp ‘\}KaTeX parse error: Got function '\newline' with no arguments as superscript at position 1: \̲n̲e̲w̲l̲i̲n̲e̲’ then ss.col end from ( select split(regexp_extract(a.appinfo,’^\[(.+)\]$’,1),’\}\,\{’) as str from ( select ‘[{a:1,b:1},{a:2,b:2},{f:6,k:7}]’ as appinfo ) a ) pp lateral view explode(pp.str) ss as col ;

结果如下： {a:1,b:1} {a:2,b:2} {f:6,k:7}

操作详解： 1.regexp_extract(a.appinfo,’^\[(.+)\]$’,1) 替换掉中括号 2.split函数拆分成数组，分隔符为’\}\,\{’，其实就是},{ 3.lateral view explode 行转列 4.使用},{为分隔符，补全前后缺失的{和}

get_json_object函数： select get_json_object(’{“bssid”:“6C:59:40:21:05:C4”,“ssid”:“MERCURY_05C4”}’,’$.bssid’) as bssid from dual; 运行结果： bssid 6C:59:40:21:05:C4

json_tuple函数： select json_tuple(’{“bssid”:“6C:59:40:21:05:C4”,“ssid”:“MERCURY_05C4”}’,‘bssid’,‘ssid’) from dual; 运行结果： c1 c2 6C:59:40:21:05:C4 MERCURY_05C4