用ETL解析JSON
JSON介绍
JSON 是基于 JavaScript 的一个子集，是一种开放的、轻量级的数据交换格式，采用独立于编程语言的文本格式来存储和表示数据，易于程序员阅读与编写，同时也易于计算机解析和生成，通常用于在 Web 客户端（浏览器）与 Web 服务器端之间传递数据。JSON 是一种纯字符串形式的数据，它本身不提供任何方法（函数），非常适合在网络中进行传输。
在 JSON 中，使用以下两种方式来表示数据：
Object（对象）：键/值对（key/value）的集合，使用花括号{ }定义。在每个键/值对中，以键开头，后跟一个冒号:，最后是值。键必须是字符串类型，使用双引号" "将键包裹起来；值可以是任意 JSON 中支持的数据类型（例如字符串、数字、对象、数组、布尔值、null 等）；多个键/值对之间使用逗号,分隔，例如 {"name":"ABC","age":20}；
Array（数组）：值的有序集合，使用方括号[ ]定义，数组中每个值之间使用逗号,进行分隔。
观远 BI 使用场景：
Web Services 数据集（API接口数据）和 MongoDB数据集可以自动解析JSON；但是对于只有部分字段为JSON的数据集，或者复杂的嵌套JSON数组，抽取到BI平台后显示为字符串 string 格式，不能直接解析。建议在ETL里使用 Spark 内置函数进行解析处理。以下介绍通过 Spark SQL内置函数的标准处理方式。
Spark 中 JSON 相关函数:
分类 | 用途 | 函数 | 举例 | 结果 |
转换 | 返回JSON字符串的数据结构 | schema_of_json(json[, options]) 函数里要直接放JSON字符串，不能引用字段 | schema_of_json('[{"a": 1, "b": "abc"}, {"b": "d"}]') | ARRAY<STRUCT<a: BIGINT, b: STRING>> |
把JSON字符串转换为指定数据结构(schema)的结构体(struct) | from_json(jsonStr, schema[, options]) schema要直接放内容，不能引用字段 | from_json([jsonStr], ' [jsonStr] 值为 '[{"a": 1, "b": "abc"}, {"b": "d"}]' | [{"a": 1, "b": "abc"}, {"a": null, "b": "d"}] | |
把结构体(struct) 转换为JSON 字符串 | to_json(expr[, options]) | to_json([struct]) [struct]为 [{"a": 1, "b": "abc"}, {"a": null, "b": "d"}] | '[{"a": 1, "b": "abc"}, {"b": "d"}]' | |
查询 | 从JSON字符串中提取键（key）对应的值（value） | get_json_object(jsonStr, path) | get_json_object ('{"a":"b"}', '$.a') | b |
json_tuple(jsonStr, key) | json_tuple('{"a":"b"}', 'a') | b | ||
从结构体(struct)中提取键（key）对应的值（value） | . 操作符 | [json_struct].a [json_struct]为 {"a":"b"} | b | |
返回JSON数组字符串里的元素数（仅外层json） | json_array_length(jsonArray) | json_array_length('[{"a": 1, "b": "abc"}, {"b": "d"}]') | 2 | |
返回JSON字符串里的所有外层JSON的键（key）组成的数组（array） | json_object_keys(json_object) | json_object_keys ('{"a":1,"b":2}') | [a, b] | |
拆分 | 把数组拆分为多行 | explode(expr) | explode([json数组]) [json数组] 为 [{"a": 1, "b": "abc"}, {"a": null, "b": "d"}] | {"a": 1, "b": "abc"} |
案例： 多层嵌套JSON 字段，且包含 JSON 数组
目标：提取 result, count, user里的 loginID, schedule 里的 page_name 共4个字段值。page_name 逻辑："type" 为"PAGE", 则取"name"的值；"type"为"CARD", 则取"page"的值。
{
"result": "ok",
"response": {
"count": 2,
"details": [
{
"user": {
"loginID": "test001"
},
"schedule": {
"name": "订阅1",
"type": "CARD",
"page": "日报"
}
},
{
"user": {
"loginID": "test002"
},
"schedule": {
"name": "月报",
"type": "PAGE"
}
}
]
}
}1. 先判断可以直接解析出的字段并提取
JSON字符串里， {} 里的对象（包括嵌套的对象）可以使用函数 get_json_object 直接提取，这里可以提取 result, count；数组 [] 内的对象无法直接提取, 所以把 details 对应的整个数组 [] 都先一并提取出来。公式和预览结果如下。
get_json_object([JSON字符串],'$.result') get_json_object([JSON字符串],'$.response.count') get_json_object([JSON字符串],'$.response.details')
2. 把JSON数组字符串转换回结构体 struct（或者数组 array）格式
因为数据源里的 [] 及里面的内容实际上是文本string格式保存的，并不是真正的数组 array 格式，必须要转换格式后才能正确拆分出来。以下提供两种方式来实现转换格式：
A. 判断 JSON字符串 的 schema，转换为结构体（struct）格式
from_json([details], schema_of_json('[{"user":{"loginID":"test001"},"schedule":{"name":"订阅1","type":"CARD","page":"日报"}},{"user":{"loginID":"test002"},"schedule":{"name":"月报","type":"PAGE"}}]')) --schema_of_json 里的json字段需取一行完整数据为例，不能引用 [details]。
from_json([details], 'ARRAY<STRUCT<schedule: STRUCT<name: STRING, page: STRING, type: STRING>, user: STRUCT<loginID: STRING>>>') -- schema来自 schema_of_json 取到的结果，熟练人员可以直接手动输入schema，除了键名称，反引号`可省略，大小写通用。schema 预览结果：
from_json 预览效果：
注意事项：
1）JSON 数据为非结构化数据，可能存在数组内两个JSON对象包含的元素不一致的情况，如上面例子 schedule 部分中，一个包含 name, type 和 page，另一个仅包含 name, type。根据上述 schema 转换后，缺少page的部分会用 null 值补齐，JSON 对象排列顺序也有所变化（不影响后续数据解析）。
2）选择不一样的 JSON 对象来解析，得到的schema 也会不同，处理的结果也会不同。例如字段 「details」，如果按 "array<struct<user: struct
[
{
"user": {
"loginID": "test001"
},
"schedule": {
"name": "订阅1",
"type": "CARD",
"page": "日报"
}
},
{
"user": {
"loginID": "test002"
},
"schedule": {
"name": "月报",
"type": "PAGE"
}
}
][
{
"user": {
"loginID": "test001"
}
},
{
"user": {
"loginID": "test002"
}
}
]B. 使用字符串函数把 JSON 字符串转换为字符串数组（array）
split(replace(translate([details],'[]',''),',{','~{'),'~')
-- translate: 去除中括号[]
-- replace: ',{' 替换为'~{'，使用 ~ 区分并分隔最外层JSON 对象
-- split: 用 ~ 把字符串拆分开，返回数组 array
2种方式区别：from_json 返回的是结构体或者结构体数组（struct_array）, 会对原数据值进行增删修改，拆分后使用struct相关函数解析；split 返回的是字符串数组（string_array）, 不修改原数据值， 拆分后使用文本类型函数处理。注意：如果存在多层数组嵌套（即[ ]里还有[ ]），那么用第2种方式可能会破坏原有层级结构，建议使用第1种方式。
3. 使用 explode 把数组拆分为多行数据
explode([json_array])
4. 从 JSON 中提取键（key）对应的值（value）
A. 用 from_json 得到的结构体 struct，按 struct 的取数方式，即 . 操作符， struct.wanted_key 从前面的结果里取得 JSON 对象 的 key-value 结果。
[explode].schedule.name
B. JSON字符串（包括从 split 得到的字符串），使用 get_json_object 解析（同步骤一）。和 struct 方式解析结果是一样的。
get_json_object([explode],'$.user.loginID')
5. 新建计算字段，使用 case when 判断逻辑得到 page_name 。
case when [type]='PAGE' then [name] when [type]='CARD' then [page] end -- "type" 为"PAGE", 则取"name"的值；"type"为"CARD", 则取"page"的值。
最终输出结果如下图。
