Convert nested JSON data to TABLE

Prepare some data first:

 

Create a temporary table to store:

 

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

 

Get the first level data:

 

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

 

Get the data of the second layer DB ﹣ CLR node:

 

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

 

In the same way, get the DB table node data of the second layer:

 

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

 

Finally, query the data of the temporary table storage table:

 

However, if we want to add the root name of the node, it can be used to really distinguish the types of records:

Add a field [Root] to the temporary table:

 

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

 

 

 

 

Source code obtained by the above three nodes:

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

 

Finally, query results:

Tags: PHP JSON

Posted on Tue, 05 Nov 2019 16:18:11 -0500 by rolajaz