转换嵌套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'
        ) ;

 

最后是查询结果：

 

最后再想修改一下，把3段SQL语句，使用UNION ALL串连起来：

 

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])
UNION ALL

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])
UNION ALL

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