基于Hive SQL 提取加工数据是每个数据分析师的工作日常，但屏幕面前的你是否遇到过这样的囧境：数仓表中的某个字段并非是以往那种一行一个实体信息的结构化数据，而是 json格式的半结构化数据。

如果SQL高级函数掌握得不够熟练，那么面对这种存储排列方式极为复杂的json数据必然会显得手足无措。所以今天让我们带大家仔细研究一下 json 数据的结构，同时给到大家解析json的思路和模版，以快速地解析 json 数据。

一、json数据格式有哪些？

json 的数据格式，分为 json 数组 (array)和 json 对象 (object)两种。对于 json 对象，其特征就是多个属性是被 {} 括起来的；而 json 数组，其实就是包含了多个 json 对象的一个集合，数组是以数组括号 [] 括起来的。以下分别举例说明：

json对象：json = { “name”: “张三”, “sex”: “25” };
json数组：json = [ { “name”: “张三”, “sex”: “25” },{ “name”: “王五”, “sex”: “18” }]

这里，{} 双括号表示对象; [] 中括号表示数组; “” 双引号内是属性或值; : 冒号表示后者是前者的值 (注意：这个值可以是字符串、数字、也可以是另一个数组或对象)
对于复杂的json数据，其属性对应的值往往不是单纯的字符串或数字，而是一个数组或对象，这给json解析增加更大的难度。
比如，下面的例子中，json_a 中 tags 属性对应的值为一个数组; 而 json_b 中的data对应的属性的值为一个对象，该对象中dataInfo对应的值为字符串，但该字符串又是由一个带双引号的json组成。

json_a= {"userId": "张三", "tags": [{"tag": "男"} ,{"tag": "高收入","value":"2w+"}]}
json_b={"userId": "张三","data":{"dataInfo":"{\"5fsfa\":\"successed\",\"fdt57\":\"Error\"}","id":"K499z"},"result":"ok"}

小tips： 如果想快速了解某json是否存在互相嵌套的关系，可以使用json网页工具进行结构识别（https://www.sojson.com/simple_json.html）
json_a 的存储格式更为常见些，因此后续会以json_a为例给大家讲解解析json需要用到的函数。

二、解析json需要用到哪些函数？

由于 json_a 的排列格式更为常见些，因此后续会以json_a为例给大家讲解解析json需要用到的函数。json_a 的格式如下：

json_a= {"userId": "张三", "tags": [{"tag": "男"} ,{"tag": "高收入","value":"2w+"}]}

1、get_json_object

用途：用于获取某个key的具体值。特点在于：一次只能获取一个key的值。
用法：函数第一个参数填写json变量；第二个参数中，使用$表示json变量标识，然后用 . 读取对象，用 [] 读取数组。
举例1：读取json对象

>> select  get_json_object (json_a,'$.userId')   
>> 张三

举例2：读取json数组

>> select  get_json_object (json_a,'$.tags[1]')   
>> {"tag":"高收入","value":"2w+"}

2、json_tuple

用途：比json_tuple更强大，用来一次性解析json字符串中的多个字段
用法：函数第一个参数填写json变量，后面参数填写 key 的名称
举例：

>>  select  json_tuple (json_a,'userId','tags')    --/** 输出两列数据**/--
>>  张三    {"tag":"高收入","value":"2w+"}

值得注意的是，如果要把json_a 中的tag 按照行输出，则以上两个函数都显得无能为力，需要用到以下的函数。

3、explode

用法： explode()函数的参数输入是 array或者map 类型的数据，它可以将 array 或 map 里面的元素按照行的形式输出。具体可以配合 LATERAL VIEW 一起使用。
为方便大家理解，这里简单介绍下 array 格式和map 格式，顺便介绍下struct 格式。

>>  ["北京","上海","天津","杭州"]

map 格式举例：

>> {"语文":60,"数学":80,"英语":99}

struct 格式举例：

>>  {"course":"english","score":80}
      {"course":"math","score":89}
      {"course":"chinese","score":95}

举例说明 explode()的用法

>>   select explode(array('A','B','C'));
>>   A
     B
     C

>>  select explode(map('A',10,'B',20,'C',30)) 
>>   A 10
     B 20
     C 30

4、LATERAL VIEW explode和LATERAL VIEW json_tuple

LATERAL VIEW explode可以将explode展开的结果行与输入表的列名进行表连接。同时，FROM子句可以有伴随多个LATERAL VIEW子句，后续的LATERAL VIEWS可以引用出现在LATERAL VIEW左侧的任何表格中的列。
而LATERAL VIEW json_tuple 函数一般是跟在LATERAL VIEW explode后面使用，用于拆解多列。
两个函数用法示例：

SELECT * FROM exampleTableLATERAL VIEW explode(col1) myTable1 AS myCol1 LATERAL VIEW explode(myCol1) myTable2 AS myCol2  LATERAL VIEW json_tuple(myCol2,'key_1','key_2') myTable3 AS myCol3_value1,myCol3_value2

6、regexp_replace 和 regexp_extract 以及正则匹配表达式

值得注意的是，假设我们要将json_a中的tag按列输出，但由于explode()函数的输入只能是map或array，如果直接将tags的json数组作为输入，系统会报错。

正确的方法是，将tags的json数组两边的中括号去掉，然后按照一定规则进行分列，以转换为map格式。因此，需要用到下面的正则函数以及分隔split函数。

1）正则表达式大全

参考以下网址：https://www.jb51.net/article/97732.htm

2） regexp_replace

举例：去掉所有中扩号[]

>> regexp_replace  (json_a,'\\[|\\]','')

3） regexp_extract

举例：只去掉首末中扩号[]

>> regexp_extract(tags,'^\\[(.+)\\]$',1)

7、split

用途： 支持使用正则表达式对字符串进行切割，返回值为数组，因此常作为explode的输入
用法： 第一个参数为待切割的变量，第二个参数为切割符号
注意：所有正则表达式中的预定义字符比如?，}，|，逗号，分号等需要在这里用\进行反转义才能表达本意。比如正则表达式中w表示匹配字母，所以也属于预定义字符，单独的w表示匹配的是字母w，而\w才表示匹配字母。

SELECT SPLIT('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1','\?') [0] AS A,
--对url进行使用？进行切割，返回值是数组，这里取？前面的值。

三、经典实战案例

1、案例 A

需求背景：hive表中某字段tag按行存储了每个用户的多个标签，但如果想要计算每个标签下的用户数，需要将tag里的userID、tag、weight字段信息扩展抽取出来。

字段tag的数据取值如下：

{"userId": "张三", "tags": [{"tag": "男"} ,{"tag": "高收入","value":"2w+"}]}
{"userId": "李四", "tags": [{"tag": "女"} ,{"tag": "中等收入","value":"1w+"}]}

思路整理：

userID 可用 get_json_object 函数直接取出;
tag和weight提取
先取出每个tags的子json结构。用 get_json_object 函数取出tags，然后用正则和split处理成map格式，用 LATERAL VIEW explode 函数以行展开;
解析子json结构的tag和weight。用 LATERAL VIEW json_tuple 函数解析并以行展开

以下为可在 Hive 环境里执行的代码：

with temp as (
  select
    '{"userId": "张三", "tags": [{"tag": "男"} ,{"tag": "高收入","value":"2w+"}]}' as json
  union
  select
    '{"userId": "李四", "tags": [{"tag": "女"} ,{"tag": "中等收入","value":"1w+"}]}' as json
)  -- 将数据源创建为临时表，字段名为json，仅包含两行数据


-- 具体的json解析过程
select
  distinct t.userId,
  table_tag_data.tag,
  table_tag_data.weight
from
  (
    select
      get_json_object(temp.json, '$.userId') as userId,
      get_json_object(temp.json, '$.tags') as tags
    from  temp  
  ) t LATERAL VIEW explode(
    split(
      regexp_replace(
        regexp_replace(tags, '\\[|\\]', ''),
        -- 将 json 数组两边的中括号去掉
        '\\}\\,\\{',   -- 只把json对象之间之间的逗号换成分号，注意要避免把map内部的有用逗号也去掉
        '\\}\\;\\{'
      ),
      '\\;' -- 按照分号分割
    )
  ) table_tags as tag_data LATERAL VIEW json_tuple(table_tags.tag_data, 'tag', 'value') table_tag_data as tag,
  weight

小tips： 上面案例的数据格式是json数据的常见格式，后续重复遇到与之高度类似的json结构概率极大，到时可以直接套用上述中的代码思路进行快速解析，因此建议收藏以上代码。

2、案例 B（难度升级）

该题比案例A 难度升高，具体为：

json结构中object的K-V值不固定；
dataInfo 对应的值为字符串，由一个带双引号的json组成。双引号的存在导致无法正常使用get_json_object函数

原数据：

{"userId": "张三","data":{"dataInfo":"{"5fsfa":"successed","fdt57":"Error"}","id":"K499z"}}
{"userId": "王五","data":{"dataInfo":"{"2345e":"successed"}","id":"K499z"}}
...

思路整理：

先通过正则函数处理dataInfo对应的值的双引号，以正常的使用get_json_object函数
对于object的K-V值不固定的情况，可以通过冒号分割截取；

代码：

with temp_db as (
  select
    '{"userId": "张三","data":{"dataInfo":"{\"5fsfa\":\"successed\",\"fdt57\":\"Error\"}","id":"K499z"}}' as json
  union
  select
    '{"userId": "王五","data":{"dataInfo":"{\"2345e\":\"successed\"}","id":"K499z"}}' as json
),  -- 将数据源创建为临时表，字段名为json，仅包含两行数据


temp as (
  select
    regexp_replace(regexp_replace(json, '\\"\\{', '{'), '\\}\\"', '}') as json
  from
    temp_db
)  -- 将json字段中dataInfo的值的的双引号去掉

select
  userId,
  regexp_extract(dataInfo_exp, '^(.+)\\:', 1) AS key,  -- 提取冒号前的key
  regexp_extract(dataInfo_exp, '\\:(.+)$', 1) AS value, -- 提取冒号后的value
from
  (
    select
      get_json_object(temp.json, '$.userId') AS userId,
      get_json_object(temp.json, '$.data') AS data,
      get_json_object(temp.json, '$.data.dataInfo') AS dataInfo,
      get_json_object(temp.json, '$.data.id') AS id
    from
      temp
  ) t1 lateral view explode(
    split(
      regexp_replace(
        regexp_extract(t1.dataInfo, '^\\{(.+)\\}$', 1), -- 去掉dataInfo的json对象{}双括号
        '\\"','' 
        ),  -- 去掉双引号使之成为一个普通字符串
        ','  -- 按照逗号分割
    )
  ) t2 as dataInfo_exp;

以上便是全部内容啦。相信大家在阅读完本文后，如果再遇到复杂的json解析问题，至少可以做到不再焦灼了，可以直接套用以上的解析模版和思路进行解析。

Hive SQL解析复杂JSON