前言
在日常工作中,经常需要用到sql解析json格式数据,有时候是直接解析,有时候则需要解析里面的数组,本文将介绍如何使用SQL解析嵌套JSON数组,帮助读者更好地理解和应用,下面直接举例子说明。
案例
表结构:
CREATE TABLE table_json_01
(
id string
,message STRING
)
;
造了一条数据
message字段json 格式数据如下
{
"content": [{
"cid": "g12343",
"data": {
"titleTag": "FD-13688",
"userIds": [{
"userId": "1349386177421744342",
"userName": "铁笛仙"
}]
}
}, {
"cid": "f34235",
"data": {
"titleTag": "EB-13688",
"userIds": [{
"userId": "1548585912129847055",
"userName": "摸着天"
}, {
"userId": "1249204342897768983",
"userName": "赛仁贵"
}, {
"userId": "1949354415331326744",
"userName": "圣手书生"
}, {
"userId": "1447655565778186262",
"userName": "活阎罗"
}]
}
}]
}
一、直接获取
直接使用函数 GET_JSON_OBJECT 即可
SELECT GET_JSON_OBJECT(message,'$.content') as aa FROM table_json_01 ;
二、解析json里面的数组,并且每个userId都单独一条数据
通过分析得 content>data>userIds>userId
除了正常解析json,还要去掉数据的[] 符号,还要采用炸裂函数去炸裂数据
SELECT id
,userId
FROM table_json_01
LATERAL VIEW json_tuple(message,"content[*].data.userIds[*].userId") t1 AS userIds
LATERAL VIEW explode(split(regexp_replace(userIds,'\\[|\\]|\\"',''),',')) t2 AS userId
如果想要连id,name都一起解析出来,其实跟上面的方法是一样的,只不过是先获取到每一组userId+userName,然后explode,最后再分出userId和userName
注意事项
在使用SQL解析嵌套JSON数组时,需要注意以下几点:
确保JSON数据格式正确,符合JSON规范。
使用合适的JSON函数和路径来提取所需的数据。
对解析后的数据进行适当的类型转换和处理,以符合实际需求。
结语
通过本文的介绍和示例,读者可以了解如何使用SQL解析嵌套JSON数组,并将其中的数据提取出来进行进一步处理。SQL提供了丰富的JSON函数和语法,能够很好地支持对JSON数据的解析和操作,希望本文能帮助读者更好地利用SQL处理嵌套JSON数组数据,提升数据处理效率和灵活性。