Hivesql解析多层Json数据

首先介���下背景：在之前的一次需求中，需要将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的几个函数

1. 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 sssfrom z_test0907;

结果如下：

1. 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;

结果如下：

1. 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    // colfrom 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)

结果如下：

1. 搭配使用 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.shiftInfofrom z_test0907lateral VIEW outer json_tuple(col,'id','sKUs','shiftInfo')t_alias as id,sKUs,shiftInfolateral VIEW outer json_tuple(col,'id')t_alias2 as id2

结果如下：

示例2：搭配explode函数

// 搭配explode函数select    skufrom(    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"}            ]        }    ]}

1. 最外面是{}，好，我们使用 json_tuple

select    t_alias.id,    t_alias.sKUs,    t_alias.shiftInfofrom z_test0907lateral VIEW outer json_tuple(col,'id','sKUs','shiftInfo')t_alias as id,sKUs,shiftInfo

1. 解析sKUs，使用炸裂函数，将其分为sku

select    a.id,    skus.sku,    a.shiftInfofrom (    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条

1. 接下来，解析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.shiftfrom(    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.ordIdfrom (    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,    shiftfrom(    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 skulateral VIEW outer explode(split(replace(regexp_extract(shiftInfo,'^\[(.+)\]$',1),'},{"shiftId"', '}||{"shiftId"'),'\|\|'))shiftList AS shift

结果和上面是一致的

结语，以上所介绍的方法比较简单且繁琐，由于笔者技术有限，只使用了几种简单函数多层子查询嵌套得出结果，sql并不算优雅美观，如果数据结构再复杂一点或者数据有些其他变化，则很可能无法正确解析，如果有大佬有更好的方法，欢迎评论区留言或链接指路。

Life is fantastic !