转换嵌套JSON数据为TABLE

先准备一些数据：

 

创建一张临时表来存储：

 

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'
        ) ;

 

最后是查询结果：