SQL解析嵌套json数组，直接看例子

前言

在日常工作中，经常需要用到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数组数据，提升数据处理效率和灵活性。