hive解析字符串---get_json_object

hive解析字符串—get_json_object

简要介绍：在Hive里面使用get_json_object函数来解析json格式数据的具体用法。

语法：

get_json_object（json数据，’$’）

解释：

第一个参数填写json对象变量

第二个参数使用$表示json变量标识

然后用 . 或 [] 读取对象或数组；

如果输入的json字符串无效，那么返回NULL。

每次只能返回一个数据项。

例子：

（0）用到的json字符串介绍：

数组：

[{"name":"巍","age":"20"},{"name":"lan","age":"23"}]

对象：

{
    "mylove":
    	{
            "weilan":[{"name":"巍","age":"20"},{"name":"澜","age":"23"}],
            "柯兰":[{"name":"柯","age":"20"},{"name":"兰","age":"23"}]
		},
	"name":"zhenhun"
}

（一）读取数组内容： $[下标]

hive (g)> select get_json_object('[{"name":"巍","age":"20"},{"name":"lan","age":"23"}]','$[0]');
OK
_c0
{"name":"巍","age":"20"}

（二）读取对象： $.

（1）获取单层值：第二个参数：’$.mylove’

hive (g)> select get_json_object('{"mylove":{"weilan":[{"name":"巍","age":"20"},{"name":"澜","age":"23"}],"柯兰":[{"name":"柯","age":"20"},{"name":"兰","age":"23"}]},"name":"zhenhun"}','$.mylove');
OK
_c0
{"weilan":[{"name":"巍","age":"20"},{"name":"澜","age":"23"}],"柯兰":[{"name":"柯","age":"20"},{"name":"兰","age":"23"}]}
Time taken: 0.622 seconds, Fetched: 1 row(s)
hive (g)> 

（2）获取多层值：第二个参数：’$.mylove.weilan’

hive (g)>  select get_json_object('{"mylove":{"weilan":[{"name":"巍","age":"20"},{"name":"澜","age":"23"}],"柯兰":[{"name":"柯","age":"20"},{"name":"兰","age":"23"}]},:"zhenhun"}','$.mylove.weilan');
OK
_c0
[{"name":"巍","age":"20"},{"name":"澜","age":"23"}]
Time taken: 0.586 seconds, Fetched: 1 row(s)

（3）获取数组值：第二个参数：’$.mylove.weilan[0]’

hive (g)> select get_json_object('{"mylove":{"weilan":[{"name":"巍","age":"20"},{"name":"澜","age":"23"}],"柯兰":[{"name":"柯","age":"20"},{"name":"兰","age":"23"}]},"name":"zhenhun"}','$.mylove.weilan[0]');
OK
_c0
{"name":"巍","age":"20"}
Time taken: 0.185 seconds, Fetched: 1 row(s)
hive (g)> 

（4）注意：如果第二个参数，有汉字，会出现问题，结果为NULL

hive (g)>  select get_json_object('{"mylove":{"weilan":[{"name":"巍","age":"20"},{"name":"澜","age":"23"}],"柯兰":[{"name":"柯","age":"20"},{"name":"兰","age":"23"}]},:"zhenhun"}','$.mylove.柯兰');
OK
_c0
NULL
Time taken: 0.607 seconds, Fetched: 1 row(s)
hive (g)> 

PS：如果第二个参数没有中文，出现解析结果如下

OK
_c0
NULL
Time taken: 0.162 seconds, Fetched: 1 row(s)

说明：输入的json字符串无效，所以返回NULL，一定一定检查有没有中文状态输入的