身为数据分析师，使用Hive SQL提取和加工数据是日常工作的重要部分。但你是否也曾遇到过这样棘手的情况：在数仓表中，某个字段并非我们常见的一行对应一个实体信息的结构化数据，而是以json格式存储的半结构化数据。
这类json数据的存储排列方式极为复杂，如果对SQL高级函数的掌握不够扎实，面对它时难免会感到手足无措，不知从何下手。别担心，今天就带大家深入剖析json数据的结构，同时分享一套解析json的思路与实用模板，帮助大家快速、高效地解析json数据，攻克这个数据处理过程中的难关 。
一、JSON数据格式有哪些？
JSON数据主要有两种格式：JSON数组（array）和JSON对象（object）。JSON对象的特点是，多个属性被大括号{}括起来；而JSON数组则是包含多个JSON对象的集合，使用方括号[]括起来。以下分别举例说明：
- JSON对象示例：
{ "name": "张三", "sex": "25" } - JSON数组示例：
[ { "name": "张三", "sex": "25" }, { "name": "王五", "sex": "18" } ]
其中，大括号{}表示对象；方括号[]表示数组；双引号""内是属性或值；冒号:表示后者是前者的值（注意：这个值可以是字符串、数字，也可以是另一个数组或对象）。
对于复杂的JSON数据，其属性对应的值往往并非单纯的字符串或数字，而是数组或对象，这无疑增加了JSON解析的难度。例如：
{
"userId": "张三",
"tags": [
{
"tag": "男"
},
{
"tag": "高收入",
"value": "2w+"
}
]
}
在上述示例中，json_a里的tags属性对应的值是一个数组。再看另一个例子：
{
"userId": "张三",
"data": {
"dataInfo": "{\"5fsfa\":\"successed\",\"fdt57\":\"Error\"}",
"id": "K499z"
},
"result": "ok"
}
在json_b中，data属性对应的值是一个对象，该对象里的dataInfo属性对应的值是字符串，但这个字符串又是由带双引号的JSON组成。
小提示：若想快速了解某个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
get_json_object是Hive中一个极为实用的函数，主要用途是从JSON格式的数据中精准获取某个特定key对应的具体值 。该函数的显著特点是一次仅能获取一个key的值，这使得在处理复杂JSON数据时，能够有针对性地提取所需信息，避免数据冗余。
使用说明：
- 参数构成：此函数包含两个参数。第一个参数需填写JSON格式的变量，它可以是存储JSON数据的列名，也可以是直接表示JSON数据的字符串。第二个参数则用于指定要获取的
key的路径。在这个参数中，使用$作为JSON变量标识，随后通过.来读取JSON对象中的属性，利用[]来读取JSON数组中的元素。 - 注意事项：在使用
get_json_object时，务必确保JSON数据格式的正确性，否则可能无法准确获取数据。同时，对于多层嵌套的JSON数据，需要准确书写key的路径，以保证获取到期望的数据。
使用范围：get_json_object广泛应用于数据仓库的数据处理、ETL（Extract，Transform，Load）过程以及数据分析任务中。当数据以JSON格式存储在Hive表中，需要提取其中的特定信息进行统计分析、数据清洗或者数据转换时，都可以借助这个函数来实现。
更多示例：
- 示例1：获取嵌套对象中的值 假设有如下JSON数据：
{
"user": {
"name": "李四",
"age": 30,
"address": {
"city": "北京",
"street": "中关村大街"
}
}
}
若要获取address对象中的city值，在Hive中可这样操作：
-- 假设上述JSON数据存储在名为json_data的列中
SELECT get_json_object(json_data, '$.user.address.city')
FROM your_table;
执行结果将返回北京。
- 示例2：获取数组中对象的属性值 考虑如下JSON数据：
{
"students": [
{
"name": "王五",
"score": 85
},
{
"name": "赵六",
"score": 90
}
]
}
若要获取第二个学生（数组索引从0开始）的score值，可使用以下查询：
-- 假设上述JSON数据存储在名为student_json的列中
SELECT get_json_object(student_json, '$.students[1].score')
FROM your_table;
执行结果将返回90。
- 示例3：处理含特殊字符的JSON数据 对于包含特殊字符的JSON数据，例如：
{
"info": {
"key with space": "value with space"
}
}
在获取key with space对应的值时，需要注意路径的书写，在Hive中可这样查询：
-- 假设上述JSON数据存储在名为special_json的列中
SELECT get_json_object(special_json, '$.info["key with space"]')
FROM your_table;
执行结果将返回value with space，这里使用双引号将包含空格的key括起来，以正确定位到该属性。
2、json_tuple
json_tuple 是Hive中一个功能强大且高效的函数，专门用于处理JSON格式的数据。它的主要用途是能够一次性解析JSON字符串中的多个字段，相较于get_json_object每次只能获取一个key的值，json_tuple在需要同时提取多个字段时，大大提高了数据处理效率。
使用说明：
- 参数设置：
json_tuple函数需要传入多个参数。第一个参数是包含JSON数据的变量，它可以是Hive表中的列名，也可以是一个直接表示JSON数据的字符串。从第二个参数开始，依次填写需要提取的key的名称。例如，在select json_tuple(json_a, 'userId', 'tags')中，json_a是JSON数据变量，userId和tags是要提取的字段key。 - 返回值处理：该函数返回的结果是一个元组，其中包含了按照参数顺序提取的各个
key对应的值。在Hive查询中，这些值会以列的形式呈现，方便后续的数据处理和分析。需要注意的是，提取的key在JSON数据中必须存在，否则对应的列会返回NULL值。
使用范围：json_tuple广泛应用于数据仓库建设、数据分析与挖掘、ETL（Extract，Transform，Load）流程等场景。当面对大量以JSON格式存储的数据，且需要同时提取多个字段进行关联分析、统计计算或数据转换时，json_tuple能发挥重要作用。例如，在电商数据处理中，对于存储订单信息的JSON数据，可使用json_tuple一次性提取订单编号、客户ID、商品列表等多个字段，以便进行订单统计和客户行为分析。
更多示例：
- 示例1：提取多层嵌套JSON数据的多个字段 假设有如下JSON数据：
{
"user": {
"name": "李四",
"age": 30,
"contact": {
"phone": "13800138000",
"email": "[email protected]"
}
}
}
在Hive中，若要一次性提取name、age和email字段，可使用如下查询：
-- 假设上述JSON数据存储在名为user_json的列中
SELECT json_tuple(user_json, 'user.name', 'user.age', 'user.contact.email')
FROM your_table;
执行结果将返回一个包含三个字段值的元组，分别对应name、age和email的值。
- 示例2：结合其他函数进行数据处理 假设有JSON数据表示学生成绩，如下：
{
"student": "王五",
"scores": {
"math": 85,
"english": 90,
"physics": 88
}
}
若要计算该学生的平均成绩，可结合json_tuple和avg函数进行处理：
-- 假设上述JSON数据存储在名为score_json的列中
SELECT (math_score + english_score + physics_score) / 3 AS average_score
FROM (
SELECT json_tuple(score_json,'scores.math','scores.english','scores.physics')
AS (math_score, english_score, physics_score)
FROM your_table
) subquery;
在这个示例中，先使用json_tuple提取出各科成绩，然后在子查询中进行计算，最终得到学生的平均成绩。
- 示例3：处理复杂数组类型的JSON数据 考虑如下JSON数据：
{
"orders": [
{
"order_id": "1001",
"product": "手机",
"quantity": 2,
"price": 5000
},
{
"order_id": "1002",
"product": "电脑",
"quantity": 1,
"price": 8000
}
]
}
若要提取所有订单的order_id和price，并计算订单总价，可使用如下查询：
-- 假设上述JSON数据存储在名为order_json的列中
SELECT order_id, price, quantity, price * quantity AS total_price
FROM (
SELECT json_tuple(orders_json, 'order_id', 'price', 'quantity')
AS (order_id, price, quantity)
FROM your_table
LATERAL VIEW explode(json_extract_array(order_json, '$.orders')) exploded_table AS orders_json
) subquery;
这里通过LATERAL VIEW explode将JSON数组展开，再结合json_tuple提取每个订单的相关字段，最后计算出每个订单的总价。
值得注意的是，如果要把json_a中的tag按照行输出，则get_json_object和json_tuple这两个函数都无法直接实现，需要借助其他函数，如explode函数结合相关的JSON处理函数来完成。
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解析问题，至少可以做到不再焦灼了，可以直接套用以上的解析模版和思路进行解析。
文章来源： https://study.disign.me/article/202508/9.hive-sql-json-parsing.md
发布时间: 2025-02-20
作者： 技术书栈编辑