您当前的位置:首页 > 电脑百科 > 数据库 > 百科

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

时间:2021-02-03 11:33:35  来源:  作者:

基于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的输入

用法:第一个参数为待切割的变量,第二个参数为切割符号

注意:所有正则表达式中的预定义字符比如?,},|,逗号,分号等需要在这里用\进行反转义才能表达本意。比如正则表达式中w表示匹配字母,所以也属于预定义字符,单独的w表示匹配的是字母w,而\w才表示匹配字母。

三、经典实战案例

1. 案例 A

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

字段tag的数据取值如下:

思路整理:

1. userID 可用 get_json_object 函数直接取出;

2. tag和weight提取

a. 先取出每个tags的子json结构。用 get_json_object 函数取出tags,然后用正则和split处理成map格式,用 LATERAL VIEW explode 函数以行展开;

b. 解析子json结构的tag和weight。用 LATERAL VIEW json_tuple 函数解析并以行展开

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

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

2. 案例 B(难度升级)

该题比案例A 难度升高,具体为:

1)json结构中object的K-V值不固定;

2)dataInfo对应的值为字符串,由一个带双引号的json组成。双引号的存在导致无法正常使用get_json_object函数

原数据:

思路整理:

1)先通过正则函数处理dataInfo对应的值的双引号,以正常的使用get_json_object函数

2)对于object的K-V值不固定的情况,可以通过冒号分割截取;

代码:

以上便是全部内容啦。相信大家在阅读完本文后,如果再遇到复杂的json解析问题,至少可以做到不再焦灼了,可以直接套用以上的解析模版和思路进行解析。所以,记得收藏或者点个在看哦~



Tags:SQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
1增1.1【插入单行】insert [into] <表名> (列名) values (列值)例:insert into Strdents (姓名,性别,出生日期) values (&#39;开心朋朋&#39;,&#39;男&#39;,&#39;1980/6/15&#3...【详细内容】
2021-12-27  Tags: SQL  点击:(1)  评论:(0)  加入收藏
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  Tags: SQL  点击:(6)  评论:(0)  加入收藏
前言JDBC访问Postgresql的jsonb类型字段当然可以使用Postgresql jdbc驱动中提供的PGobject,但是这样在需要兼容多种数据库的系统开发中显得不那么通用,需要特殊处理。本文介绍...【详细内容】
2021-12-23  Tags: SQL  点击:(12)  评论:(0)  加入收藏
最近发现还有不少做开发的小伙伴,在写存储过程的时候,在参考已有的不同的写法时,往往很迷茫, 不知道各种写法孰优孰劣,该选用哪种写法,以及各种写法的优缺点,本文以一个简单的查询...【详细内容】
2021-12-23  Tags: SQL  点击:(9)  评论:(0)  加入收藏
场景描述:由于生产环境的表比较复杂,字段很多。这里我们做下简化,只为说明今天要聊的问题。有两张表 tab1,tab2: tab1 数据如下: tab2 数据如下: 然后给你看下,我用来统计 name=&#3...【详细内容】
2021-12-20  Tags: SQL  点击:(5)  评论:(0)  加入收藏
概述我们知道SQL Server是微软公司推出的重要的数据库产品,通常情况下只支持部署在windows平台上。不过令人感到兴奋的是,从SQL Server 2017开始支持 linux系统。此 SQL Serve...【详细内容】
2021-12-17  Tags: SQL  点击:(13)  评论:(0)  加入收藏
读取SQLite数据库,就是读取一个路径\\192.168.100.**\position\db.sqlite下的文件<startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0"/...【详细内容】
2021-12-16  Tags: SQL  点击:(21)  评论:(0)  加入收藏
前言知识无底,学海无涯,知识点虽然简单,但是比较多,所以将MySQL的基础写出来,方便自己以后查找,还有就是分享给大家。一、SQL简述1.SQL的概述Structure Query Language(结构化查...【详细内容】
2021-12-16  Tags: SQL  点击:(13)  评论:(0)  加入收藏
一、为什么要搭建主从架构呢1.数据安全,可以进行数据的备份。2.读写分离,大部分的业务系统来说都是读数据多,写数据少,当访问压力过大时,可以把读请求给到从服务器。从而缓解数据...【详细内容】
2021-12-15  Tags: SQL  点击:(10)  评论:(0)  加入收藏
前言作为一名测试工程师,工作中在对测试结果进行数据比对的时候,或多或少要和数据库打交道的,要和数据库打交道,那么一些常用的 SQL 查询语法必须要掌握。最近有部分做测试小伙...【详细内容】
2021-12-14  Tags: SQL  点击:(15)  评论:(0)  加入收藏
▌简易百科推荐
1增1.1【插入单行】insert [into] <表名> (列名) values (列值)例:insert into Strdents (姓名,性别,出生日期) values (&#39;开心朋朋&#39;,&#39;男&#39;,&#39;1980/6/15&#3...【详细内容】
2021-12-27  快乐火车9d3    Tags:SQL   点击:(1)  评论:(0)  加入收藏
最近发现还有不少做开发的小伙伴,在写存储过程的时候,在参考已有的不同的写法时,往往很迷茫, 不知道各种写法孰优孰劣,该选用哪种写法,以及各种写法的优缺点,本文以一个简单的查询...【详细内容】
2021-12-23  linux上的码农    Tags:sql   点击:(9)  评论:(0)  加入收藏
《开源精选》是我们分享Github、Gitee等开源社区中优质项目的栏目,包括技术、学习、实用与各种有趣的内容。本期推荐的HasorDB 是一个全功能数据库访问工具,提供对象映射、丰...【详细内容】
2021-12-22  GitHub精选    Tags:HasorDB   点击:(5)  评论:(0)  加入收藏
作者丨Rafal Grzegorczyk译者丨陈骏策划丨孙淑娟【51CTO.com原创稿件】您是否还在手动对数据库执行各种脚本?您是否还在浪费时间去验证数据库脚本的正确性?您是否还需要将...【详细内容】
2021-12-22    51CTO  Tags:Liquibase   点击:(3)  评论:(0)  加入收藏
场景描述:由于生产环境的表比较复杂,字段很多。这里我们做下简化,只为说明今天要聊的问题。有两张表 tab1,tab2: tab1 数据如下: tab2 数据如下: 然后给你看下,我用来统计 name=&#3...【详细内容】
2021-12-20  Bald    Tags:SQL   点击:(5)  评论:(0)  加入收藏
前言知识无底,学海无涯,知识点虽然简单,但是比较多,所以将MySQL的基础写出来,方便自己以后查找,还有就是分享给大家。一、SQL简述1.SQL的概述Structure Query Language(结构化查...【详细内容】
2021-12-16  谣言止于独立思考    Tags:SQL基础   点击:(13)  评论:(0)  加入收藏
前言作为一名测试工程师,工作中在对测试结果进行数据比对的时候,或多或少要和数据库打交道的,要和数据库打交道,那么一些常用的 SQL 查询语法必须要掌握。最近有部分做测试小伙...【详细内容】
2021-12-14  柠檬班软件测试    Tags:SQL   点击:(15)  评论:(0)  加入收藏
话说C是面向内存的编程语言。数据要能存得进去,取得出来,且要考虑效率。不管是顺序存储还是链式存储,其寻址方式总是很重要。顺序存储是连续存储。同质结构的数组通过其索引表...【详细内容】
2021-12-08  小智雅汇    Tags:数据存储   点击:(17)  评论:(0)  加入收藏
概述DBConvert Studio 是一款强大的跨数据库迁移和同步软件,可在不同数据库格式之间转换数据库结构和数据。它将成熟、稳定、久经考验的 DBConvert 和 DBSync 核心与改进的现...【详细内容】
2021-11-17  雪竹聊运维    Tags:数据库   点击:(26)  评论:(0)  加入收藏
一、前言 大家好,我是小诚,《从0到1-全面深刻理解MySQL系列》已经来到第四章,这一章节的主要从一条SQL执行的开始,由浅入深的解析SQL语句由客户端到服务器的完整执行流程,最...【详细内容】
2021-11-09  woaker    Tags:SQL   点击:(35)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条