江苏龙网

  1. 主页 > 生活百科 > >

手把手教你如何用SQL解析复杂JSON

SQL

基于Hive SQL 提取加工数据是每个数据分析师的工作日常，但屏幕面前的你是否遇到过这样的囧境：数仓表中的某个字段并非是以往那种一行一个实体信息的结构化数据，而是 json格式的半结构化数据 。
如果你的SQL高级函数掌握得不够熟练，那么面对这种存储排列方式极为复杂的json数据必然会显得手足无措 。所以今天让我们带大家仔细研究一下 json 数据的结构，同时给到大家解析json的思路和模版，以快速地解析 json 数据 。
一、json数据格式有哪些？
json 的数据格式，分为 json 数组 (array)和 json 对象 (object)两种 。对于 json 对象，其特征就是多个属性是被 {} 括起来的；而 json 数组，其实就是包含了多个 json 对象的一个集合，数组是以 数组括号 [] 括起来的 。以下分别举例说明：
这里，{} 双括号表示对象; [] 中括号表示数组; "" 双引号内是属性或值; : 冒号表示后者是前者的值(注意：这个值可以是字符串、数字、也可以是另一个数组或对象)
对于复杂的json数据，其属性对应的值往往不是单纯的字符串或数字，而是一个数组或对象，这给json解析增加更大的难度 。
比如，下面的例子中，json_a 中 tags 属性对应的值为一个数组; 而 json_b 中的data对应的 属性的值为一个对象，该对象中dataInfo对应的值为字符串，但该字符串又是由一个带双引号的json组成 。
小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对象
举例2：读取json数组
2. json_tuple
用途：比json_tuple更强大，用来一次性解析json字符串中的多个字段
用法：函数第一个参数填写json变量，后面参数填写 key 的名称
举例：
值得注意的是，如果要把json_a 中的tag 按照行输出，则以上两个函数都显得无能为力，需要用到以下的函数 。
3. explode
用法：explode()函数的参数输入是 array或者map 类型的数据，它可以将 array 或 map 里面的元素按照行的形式输出 。具体可以配合 LATERAL VIEW 一起使用 。
为方便大家理解，这里简单介绍下 array 格式和map 格式，顺便介绍下struct 格式 。
array 格式举例：
>> ["北京","上海","天津","杭州"]
map 格式举例：
>> {"语文":60,"数学":80,"英语":99}
struct 格式举例：
>> {"course":"english","score":80}
{"course":"math","score":89}
{"course":"chinese","score":95}
举例说明 explode()的用法
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后面使用，用于拆解多列 。
两个函数用法示例：
5. 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
举例：去掉所有中扩号[]
3） regexp_extract
举例：只去掉首末中扩号[]
6. split
用途：支持使用正则表达式对字符串进行切割，返回值为数组，因此常作为explode的输入


推荐阅读


上一篇：软文推广应着重关注这四点

下一篇：成本高、获客难，餐饮企业如何在微信生态中乘风破浪？