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;
```
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中的处理思路:
- 把JSON对象之间的逗号(,)替换成特殊字符,比如^*,因为之后要以这个特殊字符串来切分
- 替换掉中括号([]),为空
- 以步骤1中的特殊字符串切分处理后的JSON数组
- 结合 lateral view explode()函数,使得JSON数组转成多行JSON对象
select
id, json
from db.table
lateral view explode(
split(
regexp_replace(regexp_replace(json_array, "},", "}^*^*"), "\\[|\\]", ""), "\\^\\*\\^\\*"
)
) t as json
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")
)
}