跳到主要内容
版本：6.5.0

用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], 'ARRAY<STRUCT<a: BIGINT, b: STRING>>')

[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"}
{"a": null, "b": "d"}

案例： 多层嵌套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')

image.png

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 预览结果：

image.png

from_json 预览效果：

image.png

注意事项：

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

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

[
    {
        "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

image.png

2种方式区别：from_json 返回的是结构体或者结构体数组（struct_array）, 会对原数据值进行增删修改，拆分后使用struct相关函数解析；split 返回的是字符串数组（string_array）, 不修改原数据值， 拆分后使用文本类型函数处理。注意：如果存在多层数组嵌套（即[ ]里还有[ ]），那么用第2种方式可能会破坏原有层级结构，建议使用第1种方式。

3. 使用 explode 把数组拆分为多行数据​

explode([json_array])

image.png

4. 从 JSON 中提取键（key）对应的值（value）​

A. 用 from_json 得到的结构体 struct，按 struct 的取数方式，即 . 操作符， struct.wanted_key 从前面的结果里取得 JSON 对象 的 key-value 结果。

[explode].schedule.name

image.png

B.  JSON字符串（包括从 split 得到的字符串），使用 get_json_object 解析（同步骤一）。和 struct 方式解析结果是一样的。

get_json_object([explode],'$.user.loginID')

image.png

5. 新建计算字段，使用 case when 判断逻辑得到 page_name 。​

case when [type]='PAGE' then [name]
when [type]='CARD' then [page]
end
-- "type" 为"PAGE", 则取"name"的值；"type"为"CARD", 则取"page"的值。

最终输出结果如下图。

image.png