首先介绍下背景：在之前的一次需求中，需要将mongo表数据导入Hive表使用，导入Hive表时，mongo表数据被存成了一行行的Json格式的字符串，后续使用需要解析出Json中的各个字段。
先贴一条要解析的数据（格式化后的），数据我做了一些处理，去除了大部分类似的字段，只保留了比较有代表的几类。这是一条有着多重结构的Json，我要解析字段并将其打平成多条数据。
{
"id":"1001",
"sKUs":"20230909,20230908,20230907",
"shiftInfo":[
{
"shiftId":"100101",
"resInfo":[
{"resId":"10010101","ordId":"ord001"},
{"resId":"10010102"}
]
},
{
"shiftId":"100102",
"resInfo":[
{"resId":"10010201","ordId":"ord003"},
{"resId":"10010202","ordId":"ord004"}
]
}
]
}
在hive表中，它长这样，存在z_test0907表的col字段中
首先，需要介绍下Hivesql中解析json的几个函数
- get_json_object(jsonString, ‘$.key’)
它可以返回jsonString中所传key的内容，不过一次只能返回一个字段，想要返回多个字段时，需要多写几个，获取没有的字段会返回null。例如，想要从上面数据中获取 id和skus字段，可以这么写
select
get_json_object(col,'$.id') as `id`,
get_json_object(col,'$.sKUs') as sKUs,
get_json_object(col,'$.sss') as sss
from z_test0907;
结果如下：
- json_tuple(col,‘key1’,‘key2’,…)
json_tuple可以看作是get_json_object函数的加强版，它可以一次取出多个字段，获取没有的字段时一样会返回null，获取 id和skus字段，可以这么写
select
json_tuple(col,'id','sKus','sss') as (`id`,sKUs,sss)
from z_test0907;
结果如下：
- explode(Array/Map)
explode称为炸裂函数，可以将一行数据炸裂成多行，它的入参只能是Map或Array，所以大部分情况下搭配split函数使用。比如，我们想要获取sKUs字段中的每个值，可以使用前文中的get_json_object函数、split函数与之结合。
注意，直接使用explode函数时禁止出现其他表达式，否则会报错。
// 首先，使用get_json_object 获取sKUs字段
// 观察sKUs字段，可以发现它是用英文逗号隔开的，使用split函数将其分成Array
// 使用explode函数将生成的Array炸裂至多行
select
explode(split(get_json_object(col,'$.sKUs'),',')) as sku,
// explode(split(get_json_object(col,'$.sKUs'),',')) as sku2
// col
from z_test0907;
// 放开sql任意中任意一行注释都会报错
// Error: Error while compiling statement: FAILED: SemanticException 3:0 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'sku2' (state=42000,code=40000)
结果如下：
- 搭配使用 lateral VIEW (outer) 行转列函数
行转列函数可以搭配udtf函数，比如前文提到的 explode和json_tuple函数，它需要跟在所查询表的后面，且必须加别名，from后可以跟多个 lateral VIEW ，如果所解析的列字段存在null值，需要加 outer，否则会报错。看一下具体示例就会明白了。
示例1：搭配json_tuple函数
// 搭配json_tuple函数
select
t_alias.id,
t_alias2.id2,
t_alias.sKUs,
t_alias.shiftInfo
from z_test0907
lateral VIEW outer json_tuple(col,'id','sKUs','shiftInfo')t_alias as id,sKUs,shiftInfo
lateral VIEW outer json_tuple(col,'id')t_alias2 as id2
结果如下：
示例2：搭配explode函数
// 搭配explode函数
select
sku
from
(
select
get_json_object(col,'$.sKUs') as skus
from z_test0907
)a lateral VIEW outer explode(split(a.skus,',')) t_alias as sku;
结果如下：
好了，介绍完可能会用到的的函数，我们现在正式开始解析
解析数据前，先观察数据的层级结构，根据实际需求情况确定要取哪些字段，它们分别在哪一层级、在什么结构里。这里说一个小技巧，面对{}使用 json_tuple,面对[]使用explode，从外向里一层一层解析。
废话不多说，开始解析。观察数据结构。
{
"id":"1001",
"sKUs":"20230909,20230908,20230907",
"shiftInfo":[
{
"shiftId":"100101",
"resInfo":[
{"resId":"10010101","ordId":"ord001"},
{"resId":"10010102"}
]
},
{
"shiftId":"100102",
"resInfo":[
{"resId":"10010201","ordId":"ord003"},
{"resId":"10010202","ordId":"ord004"}
]
}
]
}
- 最外面是{}，好，我们使用 json_tuple
select
t_alias.id,
t_alias.sKUs,
t_alias.shiftInfo
from z_test0907
lateral VIEW outer json_tuple(col,'id','sKUs','shiftInfo')t_alias as id,sKUs,shiftInfo
- 解析sKUs，使用炸裂函数，将其分为sku
select
a.id,
skus.sku,
a.shiftInfo
from
(
select
t_alias.id,
t_alias.sKUs,
t_alias.shiftInfo
from z_test0907
lateral VIEW outer json_tuple(col,'id','sKUs','shiftInfo')t_alias as id,sKUs,shiftInfo
)a lateral VIEW outer explode(split(a.skus,',')) skus as sku;
好的，现在sku字段获取成功了，2条数据炸裂成了6条
- 接下来，解析shiftInfo。
shiftInfo是个Array，使用explode，但是呢，虽然这里看起来是数组，但它实际只是数组样式的字符串，我们需要逐步将其用多个步骤包括split函数转为数组。
第一步，去除shiftInfo最外层的[]
regexp_extract(a.shiftInfo,'^\\[(.+)\\]$',1)
第二步， 由于shiftInfo 是用 逗号 分割的，会和数据中其他逗号混淆，为了避免分割错误，将其转为其他字符，这里尽量使用数据中不会出现的符号，我这里是将 , 转为了 ||
// 因为是多层结构，内层也可能有{}，所以不能直接用 },{ 需要特殊处理下
replace(regexp_extract(a.shiftInfo,'^\\[(.+)\\]$',1),'},{"shiftId"', '}||{"shiftId"')
第三步，使用split函数用 || 分割
split(replace(regexp_extract(a.shiftInfo,'^\\[(.+)\\]$',1),'},{"shiftId"', '}||{"shiftId"'),'\\|\\|'
第四步，好了，我们现在可以使用 explode函数了，将前几步合并起来，则得到下面的sql
select
b.id,
b.sku,
shiftList.shift
from
(
select
a.id,
skus.sku,
replace(regexp_extract(shiftInfo,'^\\[(.+)\\]$',1),'},{"shiftId"', '}||{"shiftId"') as shiftInfo
from
(
select
t_alias.id,
t_alias.sKUs,
t_alias.shiftInfo
from z_test0907
lateral VIEW outer json_tuple(col,'id','sKUs','shiftInfo')t_alias as id,sKUs,shiftInfo
)a lateral VIEW outer explode(split(a.skus,',')) skus as sku
)b lateral VIEW outer explode(split(shiftInfo,'\\|\\|'))shiftList AS shift
我们成功将shift分出来了，现在我们有12条数据了。
接下来，就是同样的方法，一层一层向下解析，最后，我们得到的最终sql为：
select
e.id,
e.sku,
e.shiftId,
res.resId,
res.ordId
from (
select
d.id,
d.sku,
d.shiftId,
resList.res
from
(
select
c.id,
c.sku,
rer_alias.shiftId,
replace(regexp_extract(rer_alias.resInfo,'^\\[(.+)\\]$',1),'},{"resId"', '}||{"resId"') as resInfo
from
(
select
b.id,
b.sku,
shiftList.shift
from
(
select
a.id,
skus.sku,
replace(regexp_extract(shiftInfo,'^\\[(.+)\\]$',1),'},{"shiftId"', '}||{"shiftId"') as shiftInfo
from
(
select
t_alias.id,
t_alias.sKUs,
t_alias.shiftInfo
from z_test0907
lateral VIEW outer json_tuple(col,'id','sKUs','shiftInfo')t_alias as id,sKUs,shiftInfo
)a lateral VIEW outer explode(split(a.skus,',')) skus as sku
)b lateral VIEW outer explode(split(shiftInfo,'\\|\\|'))shiftList AS shift
)c lateral VIEW outer json_tuple(shift,'shiftId','resInfo')rer_alias as shiftId,resInfo
)d lateral VIEW outer explode(split(resInfo,'\\|\\|'))resList AS res
)e lateral VIEW outer json_tuple(res,'resId','ordId')res as resId,ordId
而我们最终得到的结果为
到此解析正式结束，我们只需要将解析结果保存到目标表，就可以随时查询调用了。
为了便于理解，我每一次解析都只解析一个，其实 lateral VIEW outer 函数可以一次使用多个，对于同一层的解析，完全可以放在一个sql里，比如，第一层中的 炸裂 sKUs和解析shiftInfo。
select
a.id,
skus.sku,
shift
from
(
select
t_alias.id,
t_alias.sKUs,
t_alias.shiftInfo
from z_test0907
lateral VIEW outer json_tuple(col,'id','sKUs','shiftInfo')t_alias as id,sKUs,shiftInfo
)a lateral VIEW outer explode(split(a.skus,',')) skus as sku
lateral VIEW outer explode(split(replace(regexp_extract(shiftInfo,'^\\[(.+)\\]$',1),'},{"shiftId"', '}||{"shiftId"'),'\\|\\|'))shiftList AS shift
结果和上面是一致的
结语，以上所介绍的方法比较简单且繁琐，由于笔者技术有限，只使用了几种简单函数多层子查询嵌套得出结果，sql并不算优雅美观，如果数据结构再复杂一点或者数据有些其他变化，则很可能无法正确解析，如果有大佬有更好的方法，欢迎评论区留言或链接指路。
Life is fantastic !
196
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
