【数据库】HIVE SQL之lateral view json_tuple和lateral view explode联合使用

         在工作中，处理数据时遇到的情况是多种多样的，这次就遇到一种相对有一丢丢复杂的情况，案例如下：

String类型的字段，案例如下：

[{"key":"upload-screenshot","value":false},{"key":"choice","value":"已解决"}]

          现在需要把key和value解析出来。乍一看，直接用get_json_object或者lateral view json_tuple不都可以么（之前博客中有讲过lateral view json_tuple）是相对高效的解析方式，大家可以参考小白之前的博文。

但是，可以看一下直接使用该函数的效果：我们随机从表中取一条数据，进行解析，首先需要先把[]去掉

select reply, key, vaule
  from (select reply, regexp_replace(reply_1, '\\}\\,\\{', ',') as reply_1
          from (select reply, regexp_replace(reply, '\\[|\\]', '') as reply_1
                  from table
                 where d = '2019-07-18'
                   and reply = '[{"key":"upload-screenshot","value":false},{"key":"choice","value":"已解决"}]'
                 limit 1
               ) c
       ) a lateral view json_tuple(reply_1, 'key', 'value') b as key,vaule

结果是：

reply	key	vaule
[{"key":"upload-screenshot","value":false},{"key":"choice","value":"已解决"}]	choice	已解决

        这明显不是我们想要的结果，从这就可以看出来，我们首先要做的是把reply的内容，按照花括号给分开成多行，然后再进行解析。

       这里两个花括号之间的分隔符是逗号，这样不太好分割，那么就把这个逗号替换成分号，然后将每个花括号分成一行，一行转多行之后，再进行解析。

select reply, key, vaule
  from (select reply, reply_2
          from (select reply,
                       regexp_replace(reply_1, '\\}\\,\\{', '\\}\\;\\{') as reply_1
                  from (select reply,
                               regexp_replace(reply, '\\[|\\]', '') as reply_1
                          from table
                         where d = '2019-07-18'
                           and reply =
                               '[{"key":"upload-screenshot","value":false},{"key":"choice","value":"已解决"}]'
                         limit 1
                       ) c
               ) a LATERAL VIEW explode(split(reply_1, '\\;')) b as reply_2
       ) a lateral view json_tuple(reply_2, 'key', 'value') b as key,vaule

结果为：

reply	key	vaule
[{"key":"upload-screenshot","value":false},{"key":"choice","value":"已解决"}]	upload-screenshot	false
[{"key":"upload-screenshot","value":false},{"key":"choice","value":"已解决"}]	choice	已解决

这样就解析出来了~