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;