用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 字段，且包含 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, user: STRUCT>>') 
-- schema来自 schema_of_json 取到的结果，熟练人员可以直接手动输入schema，除了键名称，反引号`可省略，大小写通用。

from_json 预览效果：

注意事项：

1）JSON 数据为非结构化数据，可能存在数组内两个JSON对象包含的元素不一致的情况，如上面例子 schedule 部分中，一个包含 name, type 和 page，另一个仅包含 name, type。根据上述 schema 转换后，缺少page的部分会用 null 值补齐，JSON 对象排列顺序也有所变化（不影响后续数据解析）。

2）选择不一样的 JSON 对象来解析，得到的schema 也会不同，处理的结果也会不同。例如字段 「details」，如果按 "array<struct<user: struct>>" 结构处理，处理后仅会保留loginID 相关数据。

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"的值。

最终输出结果如下图。