Parse JSON file - Improve query in SQL Server

Question

From this JSON File (just an example) I need to reach this final result

{
    "Id": "101",
    "name": "C01",
    "testparameters": {
        "room": [
            {
                "Floor": "First_Floor",
                "Rooms": ["Room1", "Room2", "Room3"]
            },
            {
                "Floor": "Second_Floor",
                "Rooms": ["Room1", "Room2", "Room3"]
            }
        ],
        "area": [
            {               
                "Name": "Area1",
                "Subarea": ["Subarea1", "Subarea2", "Subarea3"]
            },
            {               
                "Name": "Area2",
                "Subarea": ["Subarea4", "Subarea5"]
            }
        ],
        "requirements": [{
                "condition": "",
                "type": "type1",                
                "field1": "",
                "field2": "aaaaa",
                "operator": "",
                "value2": ""
            },
            {
                "condition": "AND",
                "type": "type2",            
                "field1": "",
                "field2": "numPersons",
                "operator": ">",
                "value2": "20"
            },
            {
                "condition": "OR",
                "type": "type2",            
                "field1": "",
                "field2": "specification",
                "operator": "=",
                "value2": "wifi"
            }
        ]
    }
}
'

In one register I need to have all the information that is requested.

This is the first time that I need to parse a JSON file. After asking (a lot) I manage to reach the expected result by doing this:

Parsing JSON Example

However, I had to open the JSON file several times, and process each section apart. I'm wondering, how can I improve the code by reducing the number of times that I need to use the OPENJSON function, and in particular, how to rewrite the code snippet that handle the requirements section.

Answer 1

I must say, your desired result looks pretty de-normalized, you may want to rethink it.

Be that as it may, you can combine these quite easily, by using nested subqueries

SELECT
   ID = JSON_VALUE(j.json, '$.Id'),
   name = JSON_VALUE(j.json, '$.name'),

   area = (
        SELECT STRING_AGG(concat(d.a , ':', b.value),' - ')
        from openjson(j.json, '$.testparameters.area')
        with 
        (
            a nvarchar(250) '$.Name',
            s nvarchar(max) '$.Subarea' as json
        ) as d
        cross apply openjson(d.s) as b
    ),

    room = (
        SELECT STRING_AGG(concat(c.f, ':', d.value), ' - ') 
        from openjson(j.json, '$.testparameters.room') 
        with(
            f nvarchar(50) '$.Floor',
            r nvarchar(Max) '$.Rooms' as json
            ) as c
        cross apply openjson(c.r) as d
    ),

    requirements = (
        SELECT IIF(
            SUBSTRING(requirements,1,3) = 'AND' or SUBSTRING(requirements,1,3) = 'OR',
            SUBSTRING(requirements,5,LEN(requirements)),
            requirements
            ) 
        
        from 
        (
            select 
            STRING_AGG(CONCAT_WS(' ',
                        a.condition,  
                        a.field2,
                        operator, 
                        IIF (ISNUMERIC(a.value2) = 1,
                            a.value2,
                            CONCAT('''',a.value2,'''')
                            )
                        ),
                ' ') as requirements
            from openjson(j.json, '$.testparameters.requirements' ) 
            with 
            (
                condition nvarchar(255) '$.condition',
                type nvarchar(255) '$.type',
                field2 nvarchar(255) '$.field2',
                operator nvarchar(255) '$.operator',
                value2 nvarchar(255) '$.value2'
            ) a 
            where a.type = 'type2'
        ) a
    )

FROM (VALUES(@json)) AS j(json)  -- or you can reference a table