hive中get_json_object函数

原数据，表名：explode_test，列名：sale_info。

 [{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"}，{"source":"jdmart","monthSales":7900,"userCount":2900,"score":"5.9"},{"source":"yam","monthSales":54900,"userCount":12900,"score":"4.9"}]

[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jdmart","monthSales":7900,"userCount":2900,"score":"5.9"},{"source":"yam","monthSales":54900,"userCount":12900,"score":"4.9"}]

1、get_json_object(<列名>，'$[]')，提取第几个数据

 

2、提取字典中key的值

 select 
 get_json_object(get_json_object(sale_info, '$[0]'), '$.source') as source,
 get_json_object(get_json_object(sale_info, '$[0]'), '$.monthSales') as monthSales,
 get_json_object(get_json_object(sale_info, '$[0]'), '$.userCount') as userCount,
 get_json_object(get_json_object(sale_info, '$[0]'), '$.score') as score
 from explode_test；

3、提取所有key值

 先用正则变换成字典形式

 

 select
 get_json_object(a.col, '$.source') as source,
 get_json_object(a.col, '$.monthSales') as monthSales,
 get_json_object(a.col, '$.userCount') as userCount,
 get_json_object(a.col, '$.score') as score
 from
 (select 
 explode(split(regexp_replace(regexp_replace(sale_info, '\\[|\\]', ''), '\\}\\,\\{', '\\}\\;\\{'), '\\;')) as col
 from explode_test)a;