Parsing Nested JSON with TSQL

Question

I'm trying to parse a JSON text file and convert it into a table.
I've been able to parse JSON responses which are not nested successfully, but am having difficulty in trying to parse nested JSON files.

I've used the ISJSON function to test whether the JSON is valid, and it is. I've tried looking at many coding examples and Microsoft SQL JSon videos but I still can't get it to work.

About the example JSON below;

• exports is the top-level
• filters contains severity and state. It's from filters and below that I can't seem to access it's contents

Here is the statement I used to import the JSON file;

DECLARE @json NVARCHAR(MAX)
SELECT @json = BulkColumn
FROM OPENROWSET (BULK 'C:\Dev\REST CURL API\tenableVulnerabilitiesOutput.json', SINGLE_CLOB) tenableJsonFile

I've tried various coding approaches using CROSS APPLY and nested CROSS APPLY but I can't seem to figure it out, also that this is my first time trying to understand the JSON programming functions and syntax within TSQL.

My expected result in this instance would be:

• 1 table that contains the keys & values below exports.
• 1 table that contains the keys & values below filters.severity with the exports.uuid included so I can link the two tables back later.
• 1 table that contains the keys & values below filters.state with the exports.uuid included so I can link the two tables back later.

Any help or direction would be appreciated, thank you.

/* Code */ 
DECLARE @json NVARCHAR(MAX) 
SET @json =   
N'{ 
    "exports": [ 
        { 
            "uuid": "1234ABCD", 
            "status": "FINISHED", 
            "filters": { 
                "severity": [ 
                    "INFO", 
                    "CRITICAL" 
                ], 
                "state": [ 
                    "FIXED" 
                ], 
                "tags": {}, 
                "since": 0 
            }, 
            "chunk": 500, 
            "created": 16224576597 
        }, 
        { 
            "uuid": "5678ABCD", 
            "status": "STARTED", 
            "filters": { 
                "severity": [ 
                    "INFO", 
                    "LOW", 
                    "MEDIUM", 
                    "HIGH", 
                    "CRITICAL" 
                ], 
                "state": [ 
                    "OPEN", 
                    "REOPENED" 
                ], 
                "tags": {}, 
                "since": 0 
            }, 
            "chunk": 500, 
            "created": 6587658868 
        }, 
        { 
            "uuid": "1234ZXYG", 
            "status": "FINISHED", 
            "filters": { 
                "severity": [ 
                    "HIGH", 
                    "CRITICAL" 
                ], 
                "state": [ 
                    "FIXED" 
                ], 
                "tags": {}, 
                "since": 0 
            }, 
            "chunk": 500, 
            "created": 87678656858 
        } 
    ] 
}'   

-- Check for Valid JSON 
select isjson(@json)  

select *  
FROM OPENJSON(@json)

Answer 1

Please try the following.

SQL

DECLARE @json NVARCHAR(MAX)  =
N'{
 "exports": [
 {
 "uuid": "1234ABCD",
 "status": "FINISHED",
 "filters": {
 "severity": [
 "INFO",
 "CRITICAL"
 ],
 "state": [
 "FIXED"
 ],
 "tags": {},
 "since": 0
 },
 "chunk": 500,
 "created": 16224576597
 },
 {
 "uuid": "5678ABCD",
 "status": "STARTED",
 "filters": {
 "severity": [
 "INFO",
 "LOW",
 "MEDIUM",
 "HIGH",
 "CRITICAL"
 ],
 "state": [
 "OPEN",
 "REOPENED"
 ],
 "tags": {},
 "since": 0
 },
 "chunk": 500,
 "created": 6587658868
 },
 {
 "uuid": "1234ZXYG",
 "status": "FINISHED",
 "filters": {
 "severity": [
 "HIGH",
 "CRITICAL"
 ],
 "state": [
 "FIXED"
 ],
 "tags": {},
 "since": 0
 },
 "chunk": 500,
 "created": 87678656858
 }
 ]
}';

SELECT JSON_VALUE(exports.value, '$.uuid') as uuid
   , JSON_VALUE(exports.value, '$.status') as [status]
   , JSON_VALUE(exports.value, '$.chunk') as [chunk]
   , JSON_VALUE(exports.value, '$.created') as [created]
   , JSON_QUERY(exports.value, '$.filters.severity') as [severity]
   , JSON_QUERY(exports.value, '$.filters.state') as [state]
   , JSON_VALUE(exports.value, '$.filters.tags') as [tags]
   , JSON_VALUE(exports.value, '$.filters.since') as [since]
FROM OPENJSON (@json, 'strict $.exports') as exports;

SELECT JSON_VALUE(exports.value, '$.uuid') as uuid
   , severity.Value AS [Severity]
FROM OPENJSON (@json, 'strict $.exports') as exports
   CROSS APPLY OPENJSON(exports.value, 'strict $.filters.severity') as severity;

SELECT JSON_VALUE(exports.value, '$.uuid') as uuid
   , state.Value AS [State]
FROM OPENJSON (@json, 'strict $.exports') as exports
   CROSS APPLY OPENJSON(exports.value, 'strict $.filters.state') as state;

Answer 2

Thank you! This worked and I think I understand the structure that relates to how you accessed the nested objects.

Here is my understanding of your code;