欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

转换嵌套JSON数据为TABLE

程序员文章站 2022-07-09 21:33:32
先准备一些数据: 创建一张临时表来存储: DECLARE @json_table AS TABLE ( [type] NVARCHAR(MAX), [desc] NVARCHAR(MAX) ) 获取第一层数据: INSERT INTO @json_table ([type],[desc]) SELE ......

先准备一些数据:

 

创建一张临时表来存储:

 

declare @json_table as table
(
    [type] nvarchar(max),
    [desc] nvarchar(max)
)

 

获取第一层数据:

 

insert into @json_table ([type],[desc])
select [type],[desc] from 
openjson (@json_text,'$.db')
with (
    [type] nvarchar(max) '$.type',
    [desc] nvarchar(max) '$.desc'
    )
where [type] is not null;

 

获取第二层db_clr节点的数据:

 

insert into @json_table ([type],[desc])
select [type],[desc] from 
openjson (@json_text,'$.db')
with (     
        db_clr nvarchar(max) as json    
     )
cross apply 
        openjson (db_clr)
        with 
        (         
            [type] nvarchar(max) '$.type',
            [desc] nvarchar(max) '$.desc'
        );

 

同样方法,获取第二层的db_table节点数据:

 

insert into @json_table ([type],[desc])
select [type],[desc] from 
openjson (@json_text,'$.db')
with (     
        db_table nvarchar(max) as json
    ) 
cross apply 
        openjson (db_table)
        with 
        (         
            [type] nvarchar(max) '$.type',
            [desc] nvarchar(max) '$.desc'
        ) ;

 

最后查询临时表存储表的数据:

 

但是,如果我们想加上节点root名称,用来真正区别记录的类别:

把临时表添加一个字段[root]:

 

declare @json_table as table
(
    [root] nvarchar(max),
    [type] nvarchar(max),
    [desc] nvarchar(max)
);

 

 

 

 

以上三个节点获取的源代码:

insert into @json_table ([root],[type],[desc])
select [key],b.[type],[desc] from  
openjson (@json_text) a
cross apply
openjson (@json_text,'$.db')
with (
    [type] nvarchar(max) '$.type',
    [desc] nvarchar(max) '$.desc'
    )b
where b.[type] is not null;


insert into @json_table ([root],[type],[desc])
select 'db_clr', [type],[desc] from 
openjson (@json_text,'$.db')
with (     
        db_clr nvarchar(max) as json    
     )
cross apply 
        openjson (db_clr)
        with 
        (         
            [type] nvarchar(max) '$.type',
            [desc] nvarchar(max) '$.desc'
        );
        

insert into @json_table ([root],[type],[desc])
select 'db_table', [type],[desc] from 
openjson (@json_text,'$.db')
with (     
        db_table nvarchar(max) as json
    ) 
cross apply 
        openjson (db_table)
        with 
        (         
            [type] nvarchar(max) '$.type',
            [desc] nvarchar(max) '$.desc'
        ) ;

 

最后是查询结果: