解析多层嵌套JSON并实现1:n关系

1.1 应用场景

用户的 JSON 数据结构是多层数组嵌套，如下图所示：

现需要解析上层数组元素和下层数组元素，实现 1:n 的关系。

例如：希望 titles 数组中的字段，能与 items 数组中（items 数组又包含 drivers 数组）的字段关联起来。如下图所示：

titles 中的字段，与 items 中 drivers 包含的元素是 1:n 关系。

1.2 实现思路

以解析 JSON 数据结构中第一个对象为例（解析第二、第三个对象方案类似）：

1）获取 JSON 数据结构中第一个对象中的 titles 数组中的所有元素。

2）使用 ROW_NUMBER() 函数，为查询结果集中的每一行生成一个唯一的行号（num列），行号从 0 开始。

3）使用 get_json_object 函数，从 JSON 数据结构中第一个对象的 items 数组中，提取第 num 个元素，作为 a 列。

4）解析 a 列内容。

2.1 模拟 JSON 数据

1）新建定时任务，拖入「数据转换」节点，进入「数据转换」节点。

2）拖入「文件输入」算子，模拟用户场景，创建复杂 JSON 数据。如下图所示：

点击「数据预览」，如下图所示：

2.2 获取titles数组中的所有元素

1）拖入「JSON解析」算子，源字段选择 column，勾选「解析后保留所有上游输出字段」按钮，添加字段 titles，解析路径为$[0].titles[*]

该步骤目的：获取 JSON 数据结构中第一个对象中的 titles 数组中的所有元素。

注：若需要获取 JSON 数据结构中第二个对象中的 titles 数组中的所有元素，解析路径为：$[1].titles[*]

2）点击「数据预览」，如下图所示：

2.3 对titles列进行编号，根据编号取出items数组元素

拖入「Spark SQL」算子，输入 SQL 语句：

注：SQL 语句中的「JSON 解析」为点击生成。

select *,get_json_object(column, concat("$",'[0].items[',num,']')) as a from 

(select *,ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY 1 asc)-1 as num from JSON解析)

SQL 语句说明如下表所示：

2.4 解析取出的items数组

1）拖入「JSON解析」算子，解析 2.3 节生成的 a 字段（将 items 包含的 id、routeld 字段解析出来；将 items 中 drivers 包含的 driverid、itemId 字段解析出来）。如下图所示：

2）点击「数据预览」，如下图所示：

可看出 titles 字段，与 items 中 drivers 包含的元素是 1:n 关系。

2.5 删除多余字段

拖入「字段设置」算子，删除 column、num、a 字段。如下图所示：

2.6 数据输出

拖入「DB表输出」算子，将处理后的数据输出。如下图所示：