原数据,表名: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值
先用正则变换成字典形式
文章来源:https://www.toymoban.com/news/detail-406361.html
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;文章来源地址https://www.toymoban.com/news/detail-406361.html
到了这里,关于hive中get_json_object函数的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!