前言

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<map<string, string>>')) 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<string, string>>,得到的数组通过explode展开

map_keys 获取key

这个udf不复杂，就是提取key，只不过一时半会想不到，提取key的场景少很多，但是需要的时候就头大。

select map_keys(from_json(json,'map<string,string>'))
from values('{"a":1,"b":2}') src_json(json)

结果:[a, b]

可以看到，如果需要变成一行行数据的话也需要通过explode展开:


select explode(map_keys(from_json(json,'map<string,string>'))) 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<map<string, string>>'),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)  
)