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')
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
>>"
结构处理，处理后仅会保留
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
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]
-- "type" 为"PAGE", 则取"name"的值；"type"为"CARD", 则取"page"的值。
最终输出结果如下图。
