一.get_json_object和json_tuple的区别
get_json_object:可以解析多个层级的JSON,并把单个字段返回字符串。
json_tuple:可以解析第一层的JSON,可以同时解析多个字段返回字符串。
方法:
get_json_object(json,'$.jsonColumn[.jsonColumn]') as columnName --[可选项]解析第二层,第三层......
注:参数1:json字符串,参数2:json中的对象,"$"为json变量的标识,"."、"[]"用来获取对象或对象数组
json_tumple(json,'jsonColumn') as columnName
1.数据样例:
{
"store":
{
"fruit":[{"weight":8,"type":"apple"}, {"weight":9,"type":"pear"}],
"bicycle":{"price":19.95,"color":"red"}
},
"email":"amy@only_for_json_udf_test.net",
"owner":"amy"
}
1.1使用get_json_object:
获取单层
get_json_object(json,'$.owner')
>>>
amy
获取多层
get_json_object(json,'$.store.fruit')
>>>
{"weight":8,"type":"apple"}, {"weight":9,"type":"pear"}
获取数组数组值
get_json_object(json,'$.store.fruit[0]')
>>>
{"weight":8,"type":"apple"}
1.2使用json_tuple
只能获取第一层数据
json_tuple(json,'store')
>>>
{"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],"bicycle":{"price":19.95,"color":"red"}}