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.

Questions need to be stand alone i.e. contain all the code. The fiddle is helpful, but not dependable. — Dale K
– Dale K, Commented May 27, 2021 at 20:35

Charlieface · Accepted Answer · 2021-05-27 20:20:04Z

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

answered May 27, 2021 at 20:20

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

d2907 Over a year ago

Thanks for you reply. The result is the one requested by the client. That is store in a table where they will take it.. . For some colleagues the my code it's to large and asked me to try to reduce it especially the requirements part.

Charlieface Over a year ago

You could always refactor it into an inline Table Valued Function

Collectives™ on Stack Overflow

Parse JSON file - Improve query in SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related