观远 BI

用ETL解析JSON

创建于 2022-11-01 / 最近更新于 2023-12-18 / 7585
字体: [默认] [大] [更大]

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


43 人点赞过