SQL SERVER 解析树形JSON格式数据

下面是解析树形JSON的存储过程

Create proc GetDataFromJson(@jsn nvarchar(max),@pid int)
as  
begin 
   
   if ISJSON(@jsn)<>1  
      return

	declare @json2 varchar(max)
		    ,@fid int=0 
			,@count int=0
			,@fname varchar(200) 

	SELECT * into #t1
	FROM OPENJSON (@jsn )
	set @count=@@ROWCOUNT

    if exists(select * from #t1 where [type]=1 )
	begin
       select @fname=value from #t1 where [key]='fname'
	   select @json2=value from #t1 where [key]='child'

	   insert into json_tmp(fname,fpid)
	   select @fname,@pid
	   set @fid = @@IDENTITY
	  
		if ISJSON(@json2)=1
		begin 
			exec GetDataFromJson @json2,@fid  
		end

	end else  
		while @fid<@count
		begin 
		   select @json2=value 
		   from #t1 where  [key]=@fid

			set @fid=@fid +1

		   if ISJSON(@json2)=1
		   begin 
			   exec GetDataFromJson @json2,@pid
		   end
		   set @json2=''
	end
   
end

假如有如下结构的JSON

{
    "fname":"1",
    "child":[
        {
            "fname":"1.1"
        },
        {
            "fname":"1.2",
            "child":[
                {
                    "fname":"1.2.1"
                },
                {
                    "fname":"1.2.2"
                },
                {
                    "fname":"1.2.3"
                }
            ]
        },
        {
            "fname":"1.3"
        }
    ]
}

 我们需要建立如下结构的表来存储

create table json_tmp
(
fid int identity(1,1)
,fpid int
,fname varchar(200)
)

调用方式如下

declare @json nvarchar(max)='    {
    "fname":"1",
    "child":[
        {
            "fname":"1.1"
        },
        {
            "fname":"1.2",
            "child":[
                {
                    "fname":"1.2.1"
                },
                {
                    "fname":"1.2.2"
                },
                {
                    "fname":"1.2.3"
                }
            ]
        },
        {
            "fname":"1.3"
        }
    ]
}
'

exec GetDataFromJson2 @json,0

select * from json_tmp

执行后的结果如下

注意：如果语句报错，需要开启数据库的兼容级别最少要到130 

ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130