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
