sql-server - 解析 JSON 文件 - 改进 SQL Server 中的查询

Question

从这个 JSON 文件（只是一个例子）我需要达到这个最终结果

{
    "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"
            }
        ]
    }
}
'

在一个登记册中，我需要拥有所要求的所有信息。

这是我第一次需要解析 JSON 文件。在询问（很多）之后，我设法通过这样做达到了预期的结果：

解析 JSON 示例

但是，我不得不多次打开 JSON 文件，并将每个部分分开处理。我想知道，如何通过减少需要使用 OPENJSON 函数的次数来改进代码，特别是如何重写处理需求部分的代码片段。

标签： sql-serverjsonparserstring-agg

Answer 1

我必须说，您想要的结果看起来很不规范，您可能需要重新考虑它。

尽管如此，您可以通过使用嵌套子查询很容易地组合这些

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