数仓高频操作JSON篇
时间:2022-09-25 04:30:00
前言
JSON它在实际开发中得到了广泛的应用。百度可以直接找到常规的分析操作,但总有一些求让人疯狂,今天JSON梳理一些操作,方便遇到后在这里查询。
GET_JSON_OBJECT
命令格式
string get_json_object(string <json>, string <path>)
需要注意的是,第二个参数实际上是path,实际上是以下用途,很多人对那个$.a云里雾里,这里写什么?我更喜欢找到原来的来源。其实是在Hive对此进行了说明,链接如下:
get_json_object A limited version of JSONPath is supported: $ : Root object 美元符号代表根对象 . : Child operator 点号代表子节点的操作 [] : Subscript operator for array 数组下标操作符中括号,[0],[1]..这种 * : Wildcard for []实际上是指数组操作的通配符[*],这就是所有元素。
总体来说其实里面的规则不多,就是支持$.[]*当然,这些操作可以满足我们的大部分需求。让我们测试官方网站给出的例子:
{
"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" }
SELECT get_json_object(json, '$.owner') FROM src_json; 结果: amy SELECT get_json_object(json, '$.store.fruit[0]') FROM src_json;
结果:
{
"weight":8,"type":"apple"}
SELECT get_json_object(json, '$.non_exist_key') FROM src_json;
结果:
NULL
数组操作
select GET_JSON_OBJECT(json, '$.store.fruit[*]') AS fruits from src_json
结果:
[{
"weight": 8,
"type": "apple"
}, {
"weight": 9,
"type": "pear"
}]
FROM_JSON
命令格式
from_json(<jsonStr>, <schema>)
根据JSON字符串jsonStr和schema信息,返回ARRAY、MAP或STRUCT类型。当我们需要进行一些复杂的操作的时候,需要对把JSON加工成对象形式,可以进一步提取信息。
举例说明:在上面一步操作上,我们已经fruit信息进行提前,但是有个情况,我们其实看到结果就是一行数据,我们希望每一个水果的内容都是一行信息,完整sql如下:
select fruit['type'] as type,fruit['weight'] as weight from (
select explode(from_json(GET_JSON_OBJECT(json, '$.store.fruit[*]'),'array)) AS fruit from values (' { \"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\" }') src_json(json)
)
结果如下:
type | weight |
---|---|
apple | 8 |
pear | 9 |
from_json函数实现了把原有的json数组进行映射array
map_keys 获取key
这个udf不复杂,就是提取key,只不过一时半会想不到,提取key的场景少很多,但是需要的时候就头大。
select map_keys(from_json(json,'map' ))
from values('{"a":1,"b":2}') src_json(json)
结果:[a, b]
可以看到,如果需要变成一行行数据的话也需要通过explode展开:
select explode(map_keys(from_json(json,'map' ))) as key
from values('{"a":1,"b":2}') src_json(json)
key |
---|
a |
b |
集合操作
有了前面的基础,可以通过json提取之后配合一些集合的操作,例如,前面的fruit我需要提取type='apple’怎么去做,方法一:通过最后来一个where条件,也可以,方法二就是通过filter集合操作来运算:
select fruit['type'] as type,fruit['weight'] as weight from (
select explode(filter(from_json(GET_JSON_OBJECT(json, '$.store.fruit[*]'),'array),x->x['type']='apple')) AS fruit from values (' { \"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\" }') src_json(json)
)
结果如下:
type | weight |
---|---|
apple | 8 |