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;
本站所有文章、数据、图片均来自互联网,一切版权均归源网站或源作者所有。
如果侵犯了你的权益请来信告知我们删除。邮箱:licqi@yunshuaiweb.com