概述

在数据处理中，经常遇到的一个数据类型就是JSON，MySQL数据库解析JSON，参考MySQL 5.7 JSON函数学习，
MySQL json_merge with group by。

在大数据执行引擎Hive中，我们也经常会遇到JSON解析的场景。

实战

get_json_object、json_tuple

Hive内部提供大量的内置函数用于处理各种类型的需求，参见官方文档：Hive Operators and User-Defined Functions (UDFs)。从这些内置的 UDF 可找到两个用于解析JSON的函数：get_json_object 和 json_tuple。

get_json_object 语法：get_json_object(STRING json_string, STRING path)

get_json_object一个只能取一个字段：SELECT get_json_object('{"name":"johnny","sex":"男"}', '$.name');
输出：johnny

如果想要取多个字段，这么写：
SELECT get_json_object('{"name":"johnny","sex":"男"}', '$.name'), get_json_object('{"name":"johnny","sex":"男"}', '$.sex');
输出：johnny 男

json_tuple 相对于 get_json_object 的优势：一次可以解析多个JSON字段。

语法：json_tuple(STRING jsonStr, STRING k1, STRING k2)

实例：SELECT json_tuple('{"name":"johnny","sex":"男"}', 'name', 'sex');
输出：johnny 男

但是如果有个JSON数组，get_json_object处理JSON数组的功能很有限：
SELECT get_json_object('[{"name":"johnny","sex":"男"}, {"name":"lucy","sex":"女"}]', '$.[0].name');
输出：johnny

如果想将整个JSON数组里面的name字段都解析出来，如果这么写将非常麻烦，因为无法确定数组的长度，而且即使确定，由于指定索引字段，可维护性很差。

explode

Hive内置函数

explode() takes in an array (or a map) as an input and outputs the elements of the array (map) as separate rows. UDTFs can be used in the SELECT expression list and as a part of LATERAL VIEW。

explode()接收一个 array 或 map 类型的数据作为输入，然后将 array 或 map 里面的元素按照每行的形式输出。可配合 LATERAL VIEW 一起使用。

比如：

select explode(array('A','B','C'));
A
B
C
select explode(map('A',10,'B',20,'C',30));
A 10
B 20
C 30

用于解析JSON：

SELECT explode(split(regexp_replace(regexp_replace('[{"name":"johnny","sex":"男"}, {"name":"lucy","sex":"女"}]', '{','\\}\\;\\{'),'\\[|\\]',''),'\\;'));

输出：

{"name":"johnny","sex":"男"}
{"name":"lucy","sex":"女"}

几点说明：

explode 函数只能接收数组或 map 类型的数据，而 split 函数生成的结果就是数组；
第一个 regexp_replace，外层的那个：将JSON数组元素之间的逗号换成分号
第二个 regexp_replace，内层的那个：将JSON数组两边的中括号去掉

然后可以结合 get_json_object 或 json_tuple 来解析里面的字段：

select json_tuple(json, 'name', 'sex') from(SELECT
explode(split(regexp_replace(regexp_replace('[{"name":"johnny","sex":"男"}, {"name":"lucy","sex":"女"}]', '\\}\\,\\{','\\}\\;\\{'),'\\[|\\]',''),'\\;')) 
as json) iteblog;

输出：

johnny 男
lucy 女

自定义函数解析JSON数组

<dependency>
	<groupId>org.apache.hive</groupId>
	<artifactId>hive-exec</artifactId>
	<version>2.1.1</version>
</dependency>

Hive提供强大的自定义函数接口，故可自定义解析JSON数组的UDF：

package com.johnny.demo;
import org.apache.hadoop.hive.ql.exec.Description;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.json.JSONArray;
import org.json.JSONException;
import java.util.ArrayList;
@Description(name = "json_array", value = "_FUNC_(array_string) - Convert a string of a JSON-encoded array to a Hive array of strings.")
public class UDFJsonAsArray extends UDF {
    public ArrayList evaluate(String jsonString) {
        if (jsonString == null) {
            return null;
        }
        try {
            JSONArray extractObject = new JSONArray(jsonString);
            ArrayList result = new ArrayList();
            for (int ii = 0; ii < extractObject.length(); ++ ii) {
                result.add(extractObject.get(ii).toString());
            }
            return result;
        } catch (JSONException | NumberFormatException e) {
            return null;
        }
    }
}

使用：

hive> add jar /root/json.jar;
hive> create temporary function json_array as 'com.johnny.demo.UDFJsonAsArray';
hive> select explode(json_array('[{"name":"johnny","sex":"男"}, {"name":"lucy","sex":"女"}]'));
OK
{"name":"johnny","sex":"男"}
{"name":"lucy","sex":"女"}
hive> select json_tuple(json, 'name', 'sex') from
(SELECT explode(json_array('[{"name":"johnny","sex":"男"}, {"name":"lucy","sex":"女"}]')) as json) iteblog;
johnny 男
lucy 女