Hive——Hive/Spark SQL解析JSON对象、JSON数组_hive sql解析数组

1. JSON对象解析

JSON对象的处理可以用get_json_object()函数或json_tuple()函数。

字段field的值是一个JSONObject：{“status”:0,“version”:“v1.0”}

1. 使用get_json_object()函数

```sql
select 
    get_json_object(field, "$.status"),
    get_json_object(field, "$.version")
from db.table;
```
1
2
3
4
5
6

2. 使用json_tuple()函数

如果需要获取多个key的值，建议用json_tuple函数，性能优于get_json_object()。
sql select json.status, json.version from qjdods.cif_credit_report t lateral view json_tuple(report_value, 'status', 'version') json as status, version limit 1;

2. JSON数组解析

1. Hive实现

Hive中的处理思路：

1. 把JSON对象之间的逗号(，)替换成特殊字符，比如^*，因为之后要以这个特殊字符串来切分
2. 替换掉中括号([])，为空
3. 以步骤1中的特殊字符串切分处理后的JSON数组
4. 结合 lateral view explode()函数，使得JSON数组转成多行JSON对象

select 
    id, json
from db.table
lateral view explode(
    split(
        regexp_replace(regexp_replace(json_array, "},", "}^*^*"), "\\[|\\]", ""), "\\^\\*\\^\\*"
    )
) t as json
1
2
3
4
5
6
7
8

2. Spark SQL实现

使用Hive SQL处理JSON数组有一个弊端，如果JSON数组里面有嵌套数组的时候，单纯的替换掉中括号得出的结果就是错误的。而Spark SQL提供了一个内建函数substring_index(str: Column, delim: String, count: Int)，这个函数可以从指定的索引位置，指定分隔符来切分JSON字符串，这样就可以实现只替换JSON数组中的首尾中括号。当然，在Hive SQL也可以自己写一个UDF来实现这个功能。

Spark实现代码如下：

//Json数组行转列
def explodeFunc(spark: SparkSession, df: Dataset[Row]): Dataset[Row] = {
    import spark.implicits._
    df.select($"user_id",
        explode(
            split(
                substring_index(
                    substring_index(
                        regexp_replace($"json_array", "},", "}^*^*"),
                        "[", -1),
                    "]", 1),
                "\\^\\*\\^\\*"
            )
        ).as("json")
    )
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16