转换嵌套JSON数据为TABLE

来源：cnblogs　　作者：Insus.NET　　时间：2019/5/27 8:47:04　　对本文有异议

先准备一些数据：

创建一张临时表来存储：

DECLARE @json_table AS TABLE
(
    [type] NVARCHAR(MAX),
    [desc] NVARCHAR(MAX)
)

Source Code

获取第一层数据：

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;

Source Code

获取第二层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'
        );

Source Code

同样方法，获取第二层的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'
        ) ;

Source Code

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

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

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

DECLARE @json_table AS TABLE
(
    [root] NVARCHAR(MAX),
    [type] NVARCHAR(MAX),
    [desc] NVARCHAR(MAX)
);

Source Code

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

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

Source Code

最后是查询结果：

最后再想修改一下，把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'
        ) ;