点击上方「数据分析师的FIRE人生」→
点击右上角「...」→设为星标⭐
大家好，我是风影楼，一名互联网公司的数据分析师。之前我曾在CSDN编写了Hive系列的付费博客专栏，目前收获了25W次的访问，不过由于CSDN的付费设置无法取消，所以我决定在公众号重新免费分享一遍。这一篇主要介绍Hive中的JSON解析函数。
1.什么是JSON格式
这里直接上百度百科的解释：
JSON(JavaScript Object Notation, JS 对象简谱) 是一种轻量级的数据交换格式，采用完全独立于编程语言的文本格式来存储和表示数据。
对象和数组是比较特殊且常用的两种类型。
对象：对象在 JS 中是使用花括号包裹 {} 起来的内容，数据结构为 {key1：value1, key2：value2, ...} 的键值对结构。在面向对象的语言中，key 为对象的属性，value 为对应的值。键名可以使用整数和字符串来表示。值的类型可以是任意类型。
例如下面这样，其中user_id和platform是key，也就是我们要解析出来的列名，10000和ios是value，也就是我们要解析出来的数据内容：
{"user_id": "10000", "platform": "ios"}
数组：数组在 JS 中是方括号 [] 包裹起来的内容，数据结构为 ["java", "javascript", "vb", ...] 的索引结构。在 JS 中，数组是一种比较特殊的数据类型，它也可以像对象那样使用键值对，但还是索引使用得多。同样，值的类型可以是任意类型。
2. 解析JSON字符串的一个字段
get_json_object(string json_string, string path)
功能：解析JSON的字符串json_string，返回path指定的内容。如果输入的JSON字符串无效，那么返回NULL。path的具体语法如下：
$ : Root object（根对象）
. : Child operator（子连接符）
[ ] : Subscript operator for array（数组下标操作符）
举例:
假设表src_json中只有一个列json，里面的内容如下：
{"store":{"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],"egg":{"price":10,"color":"red"}},"email":"139690@qq.com","owner":"hzy"}
可以看到这是一个较为复杂的复合JSON字符串，我们可以使用百度出的在线JSON格式化工具将其格式化，如下：
{
"store": {
"fruit": [{
"weight": 8,
"type": "apple"
}, {
"weight": 9,
"type": "pear"
}],
"egg": {
"price": 10,
"color": "red"
}
},
"email": "139690@qq.com",
"owner": "hzy"
}
提取单个JSON字符串的value,比如提取owner中的内容：
hive> SELECT get_json_object(json, '$.owner') FROM src_json;
hzy
提取嵌套JSON字符串里层的value,比如提取store中的egg的price：
hive> SELECT get_json_object(json, '$.store.egg.price') FROM src_json;
10
提取JSON字符串数组格式的value,比如提取store中的fruit中的第一个元素：
hive> SELECT get_json_object(json, '$.store.fruit[0]') FROM src_json;
{"weight":8,"type":"apple"}
如果在path中输入不存在的key，会返回NULL:
hive> SELECT get_json_object(json, '$.aaa') FROM src_json;
NULL
3. 同时解析JSON字符串的多个字段
json_tuple(string jsonStr,stringk1,...,string kn)
功能：一次解析多个JSON字符串中的key-value，可以输入任意多个key，并返回其对应的value。
举例：
如果使用get_json_object想解析出email和owner需要写两次，如下：
hive (app)> SELECT get_json_object(json, '$.email') AS email
> ,get_json_object(json, '$.owner') AS owner
> FROM src_json;
email owner
139690@qq.com hzy
使用json_tuple则只需要使用一次，并且需要提取的key越多，这种方式越简便，如下：
有时最后结果除了要解析JSON还需要表中的其他字段，这时新定义的列别名注意不要和已有字段名重复
hive (app)> SELECT a.json
> ,b.email
> ,b.owner
> FROM src_json a lateral VIEW json_tuple(a.json, 'email', 'owner') b AS email,owner;
a.json b.email b.owner
{"store":{"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],"egg":{"price":10,"color":"red"}},"email":"139690@qq.com","owner":"hzy"} 139690@qq.com hzy
hive (app)> SELECT email
> ,owner
> FROM src_json lateral VIEW json_tuple(json, 'email', 'owner') b AS email,owner;
email owner
139690@qq.com hzy
注意：使用lateral VIEW json_tuple后必须定义一个别名，否则会报错，如下：
hive (app)> SELECT email
> ,owner
> FROM src_json lateral VIEW json_tuple(json, 'email', 'owner') AS email,owner;
FAILED: ParseException line 3:63 Failed to recognize predicate 'AS'. Failed rule: 'identifier' in table alias
4. 解析不确定key的JSON，将key和value分别放入两列
Hive自带解析JSON的的函数get_json_object和json_tuple都必须要指定key才能将json解析出，并且一个key一列，value作为key列中的值。如果想解析不确定key的JSON，并将key和value分别放入两列的话，就需要使用其他函数进行组合了。
举例：
原始数据：
{
"email": "139690@qq.com",
"owner": "hzy"
}
希望的结果数据：
json_key json_value
email 139690@qq.com
owner hzy
代码如下：
hive (app)> select * from src_json1;
OK
src_json1.json
{"email":"139690@qq.com","owner":"hzy"}
hive (app)> SELECT split(regexp_replace(array_json, '"', ''), ':') [0] json_key
> ,split(regexp_replace(array_json, '"', ''), ':') [1] json_value
> FROM src_json1
> lateral VIEW explode(split(regexp_replace(json, '\\{|\\}', ''), ',')) a AS array_json;
OK
json_key json_value
email 139690@qq.com
owner hzy
hive (app)> select regexp_replace(json, '\\{|\\}', '') json from src_json1;
OK
_c0
"email":"139690@qq.com","owner":"hzy"
hive (app)> SELECT array_json
> FROM src_json1
> lateral VIEW explode(split(regexp_replace(json, '\\{|\\}', ''), ',')) a AS array_json;
OK
array_json
"email":"139690@qq.com"
"owner":"hzy"
hive (app)> SELECT split(regexp_replace(array_json, '"', ''), ':') [0] json_key
> ,split(regexp_replace(array_json, '"', ''), ':') [1] json_value
> FROM src_json1
> lateral VIEW explode(split(regexp_replace(json, '\\{|\\}', ''), ',')) a AS array_json;
OK
json_key json_value
email 139690@qq.com
owner hzy
