Byzer-lang 解析嵌套 JSON 的正确打开方式

github：chengwang15

引言：

在日常的业务数据分析场景中，解析复杂的 JSON 文件并提取出有用的信息进行分析是非常高频的需求。但是解析复杂的 JSON 数据并不是一件易事，需要在理解数据结构的基础上，再配合一些 JSON 处理包，像剥洋葱一样一层一层地往里剥，费时又费力。

而在本文中，我们将结合具体的示例为大家介绍：如何利用 Byzer-lang 的内置 ET 插件简单快速地进行 JSON 数据的处理和分析，以及在遇到复杂处理逻辑时的最佳实践是什么。

那就接着往下看吧～

基本使用

当我们从数据源中按照不同的业务需求获取数据时，数据会以不同层级嵌套的 JSON 结构的形式呈现。

在 Byzer-lang 中，直接使用内置的 JsonExpandExt ET，就能方便地处理结果集，将一个 JSON 字段展开为多个字段方便后续的分析处理。

JSON 结构大体上可以分为两种类型：JSON Object 和 JSON Array 。

第一种类型：JSON Object

JSON Object 形如 '{ "data": [1,2,3,4] }'

以如下数据为例：

-- 首先我们创建了一个 mock_data 表，并将 stu 设置为一个层级嵌套的 JSON 结构列
select '''
    { "id": "1", "name": "student_1", "detail": { "age": "8" } }
''' as stu
as mock_data;
--然后我们使用 JsonExpandExt 去推断数据结构
run mock_data as JsonExpandExt.`` where inputCol="stu" and structColumn="true" as mock_data_1;
  • JsonExpandExt：为Byzer-lang 内置的 Json 处理插件
  • ``：Byzer-lang 使用 ET 时的语法规则，为空即可
  • inputCol="stu"：表示 JSON 字段叫 stu
  • structColumn="true"：表示解析 inputCol 字段的数据类型

提示：在 Byzer-lang 里，如果想看到对应的 ET 插件包含哪些参数，可以用宏命令查看，例如：!show "et/params/JsonExpandExt"; 可以查看 JsonExpandExt 的相关参数。

该 Column 的结构和层级可以通过 JsonExpandExt 的推断获取到：

image

此时，再想要解析这条数据就十分简单了，可以直接通过层级获取相应字段：

select 
    stu.id as id, 
    stu.name as name,
    stu.detail.age as age
from mock_data_1 as output;

我们可以看到，此时我们想要的列已经从复杂的 JSON 格式中被提取并展开成一张二维表：

image

第二种类型：JSON Array

JSON Array 形如 '[1,2,3,4]'

select '''
    [1,2,3,4]
''' as json_arr
as output;
image

这种格式其实不怎么方便处理，但是我们可以借助一点技巧，先将 json array 转化成 json object

select '''
    [1,2,3,4]
''' as json_arr
as demo_1;

select
    -- 利用字符串拼接将 json array 转化成 json object
    concat("{\"data\":", json_arr, "}") as json_arr_concat
from demo_1
as demo_2;
image

concat(col1, col2, ..., colN): Spark SQL 的函数，会将 col1, col2, ..., colN 拼接后返回，转化完成后我们就能用处理 json object 的方法处理了

如果一个字段的值是一个数组，我们可以使用 Spark SQL 的 explode 方法将数组展开。

explode(expr): 将数组 expr 的元素分隔为多行，或将 map expr 的元素分隔为多行和多列。除非另有说明，否则对数组的元素使用默认的列名 col，或对映射的元素使用键和值。

-- 以 json array 转化成的 json object 为例
select '''
    [1,2,3,4]
''' as json_arr
as demo_1;

select
    -- 利用字符串拼接将 json array 转化成 json object
    concat("{\"data\":", json_arr, "}") as json_arr_concat
from demo_1
as demo_2;

--然后我们使用 JsonExpandExt 去推断数据结构
run demo_2 as JsonExpandExt.`` where inputCol="json_arr_concat" and structColumn="true" as demo_3;

select
    -- 通过 Spark SQL 的 explode 方法将数组展开
    explode(json_arr_concat.data) as item
from demo_3
as output;
image

问题初现

实践中我们发现，在某些特定情况下，若结果集中所有数据的 detail 字段都为 null，此时使用相同的逻辑，就会出现问题了。

select '''
    { "id": "2", "name": "student_2", "detail": null }
''' as stu
as mock_data;

run mock_data as JsonExpandExt.`` where inputCol="stu" and structColumn="true" as mock_data_1;

select 
    stu.id as id, 
    stu.name as name,
    stu.detail.age as age
from mock_data_1 as output;
image

报错提示需要一个 struct 但是实际却是一个 string，这是什么情况？

第一反应猜测可能是因为 detail 为 null，先用 case when 判断试试。

select '''
    { "id": "2", "name": "student_2", "detail": null }
''' as stu
as mock_data;

run mock_data as JsonExpandExt.`` where inputCol="stu" and structColumn="true" as mock_data_1;

select 
    stu.id as id, 
    stu.name as name,
    case
        when isnotnull(stu.detail) then stu.detail.age
        else ""
    end as age
from mock_data_1 as output;

还是一样的报错，这是为什么呢？

其实在 SQL 中第 12 行 stu.detail.age 这个解析是在很早的阶段就进行校验的，而 case when 的逻辑则是在执行时。所以 case when 不会生效，在 SQL 做解析的时候就已经报错了。

原因分析

本质上是因为结果集中所有 JSON 数据的 detail 字段都为 NULL，导致 JsonExpandExt 推断该字段没有嵌套子级，而在后续处理逻辑中，我们却将 stu.detail 当做一个 struct 去获取 stu.detail.age，这就与推断的数据结构出现冲突，因此会报错。

解决方案

问题知道了，那么就该思考解决方案了。

方案一：利用 Byzer 分支语句处理结果集

我们首先很容易就会想到一条路：有这个字段，就处理，如果全都没有这个字段，不处理，这不就行了？这是一个很明显的条件判断。

SQL 是不支持分支语句的，但是在 Byzer-lang 中配合宏命令做到了分支语句的支持，允许我们正常使用 if/else，强化拓展了语言自身的能力，具体使用手册请戳这里。

这样就得到了我们的方案一：利用分支语句处理结果集。

select '''
    { "id": "2", "name": "student_2", "detail": null }
''' as stu
as mock_data;

run mock_data as JsonExpandExt.`` where inputCol="stu" and structColumn="true" as mock_data_1;

-- 计算 detail 字段不为 NULL 的数据的条数
set count = `select count(*) from mock_data_1 where stu.detail is not null` where type="sql";

-- 如果所有数据的 detail 字段都为 NULL
!if ''' :count == 0 ''';
!then;
    select 
        stu.id as id, 
        stu.name as name,
        -- 指定 age 为空字符串
        "" as age
    from mock_data_1 as mock_data_2;
!else;
    -- 如果有 detail 字段不为空的数据，那么正常处理
    select 
        stu.id as id, 
        stu.name as name,
        stu.detail.age as age
    from mock_data_1 as mock_data_2;
!fi;

select * from mock_data_2 as output;

此时，所有字段都可以被正确获取到了：

image

再加一条数据试试

select '''
    { "id": "1", "name": "student_1", "detail": { "age": "8" } }
''' as stu
union
select '''
    { "id": "2", "name": "student_2", "detail": null }
''' as stu
as mock_data;

run mock_data as JsonExpandExt.`` where inputCol="stu" and structColumn="true" as mock_data_1;

set count = `select count(*) from mock_data_1 where stu.detail is not null` where type="sql";

!if ''' :count == 0 ''';
!then;
    select 
        stu.id as id, 
        stu.name as name,
        "" as age
    from mock_data_1 as mock_data_2;
!else;
    select 
        stu.id as id, 
        stu.name as name,
        stu.detail.age as age
    from mock_data_1 as mock_data_2;
!fi;

select * from mock_data_2 as output;
image

也没有问题。

方案二：分支语句结合 SQL 补全字段

在方案一的基础上扩展一下思维，我们是不是也可以自己补全一下缺失的字段？

select '''
    { "id": "2", "name": "student_2", "detail": null }
''' as stu
as mock_data;

run mock_data as JsonExpandExt.`` where inputCol="stu" and structColumn="true" as mock_data_1;

-- 计算 detail 字段不为 NULL 的数据的条数
set count = `select count(*) from mock_data_1 where stu.detail is not null` where type="sql";

-- 如果所有数据的 detail 字段都为 NULL
!if ''' :count == 0 ''';
!then;
    select 
        stu.id as id, 
        stu.name as name,
        -- 通过 Spark SQL 的 map 函数手动给 detail 加上子级
        map("age","123") as detail
    from mock_data_1 as mock_data_2;
!fi;

select id, name, detail.age from mock_data_2 as output;

手动补全数据，但不是补全在 stu 字段里，而是把 detail 单独出来了：

image

方案三：自主开发 ET 满足需求

方案一和方案二在字段比较少的时候还是可以使用的，比较简单，但是如果数据嵌套的层级过深，就无法解决了。

现在就轮到第三个方案出场了：开发一个自定义的 ET，给定一个 schema，强制修改原来的 schema 为正确值，这样解析 SQL 时就可以用 If 函数不会报错了。

Byzer-lang 在此基础上才能具备足够灵活的扩展性，开发 ET 的方法请戳 这里。

例如下面的一段伪代码解释了自主开发一个叫 ReplaceColumnExt 的 ET 的参考方式：

select '''
    { "id": "2", "name": "student_2", "detail": null }
''' as stu
as mock_data;

run mock_data as JsonExpandExt.`` where inputCol="stu" and structColumn="true" as mock_data_1;

-- 这是一段伪代码
run mock_data_1 as ReplaceColumnExt.`` where col="stu.detail" and 
colType="st(field(age,string))" and if=''' fieldType(stu.detail)=="string" ''' as mock_data_2;

select if(stu.detail is null, "", stu.detail.age) as age 
from mock_data_2 as output;

select * from table2 as output;

方案四：使用 python 脚本补全字段

Byzer-lang 对 Python 也提供了支持：Byzer-python，有了 Byzer-python 我们就可以在 Byzer 中拥抱 Python 的生态了。

所以即使不去自定义 ET，我们也能使用 python 脚本来修改 schema。

1）首先获取数据：

select '''
    { "id": "2", "name": "student_2", "detail": null }
''' as stu
as mock_data;

2）然后用 python 脚本补全数据

以 Byzer Notebook 为例，新建一个类型为 Python 的 cell，增加如下配置

image.png
#%python
#%input=mock_data_1
#%output=mock_data_2
#%schema=st(field(stu,string))
#%runIn=driver
#%dataMode=data
#%cache=true
#%env=source activate dev

from pyjava.api.mlsql import PythonContext,RayContext
import json

# type hint
context:PythonContext = context

ray_context = RayContext.connect(globals(),None)

def completion(row):
    result = row
    result['stu']['detail'] = result['stu'].get('detail') if result['stu']['detail'] else {'age': ""}
    result['stu'] = json.dumps(result['stu'])
    return result

ray_context.foreach(completion)

3）补全之后的数据结果显示如下：

image

这样就能很轻易的处理 detail 的 age 字段了

run mock_data_2 as JsonExpandExt.`` where inputCol="stu" and structColumn="true" as mock_data_3;

select 
    stu.id as id, 
    stu.name as name,
    stu.detail.age as age
from mock_data_3 as output;
image

总结

以上四种方法只是抛砖引玉，期待小伙伴们有更多优雅的姿势来 Byzer 社区一起分享。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 214,172评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 91,346评论 3赞 389
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 159,788评论 0赞 349
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 57,299评论 1赞 288
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 66,409评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,467评论 1赞 292
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,476评论 3赞 412
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,262评论 0赞 269
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,699评论 1赞 307
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,994评论 2赞 328
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,167评论 1赞 343
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,827评论 4赞 337
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,499评论 3赞 322
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,149评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,387评论 1赞 267
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,028评论 2赞 365
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,055评论 2赞 352