sql - 按兄弟属性的值过滤 JSON 数据

Question

示例数据设置：

DECLARE @Data TABLE (Id INT IDENTITY(1,1), JSONData NVARCHAR(MAX))
INSERT INTO @Data
VALUES ( N'
{
    "propId": 0,
    "propName": "Helo.World",
    "dataDump": [   {
                        "propId": 0,
                        "propName": "fName",
                        "val": "John"
                    },
                    {
                        "propId": 0,
                        "propName": "lName",
                        "val": "Doe",
                        "dataDump": [   {
                                            "propId": 0,
                                            "propName": "homePhone",
                                            "val": "1010101010"
                                        },
                                        {
                                            "propId": 0,
                                            "propName": "mobilePhone",
                                            "val": "010101010101"
                                        }
                                    ]
                    },
                    {
                        "propId": 0,
                        "propName": "isAuthorized",
                        "val": "true"
                    },
                    {
                        "propId": 0,
                        "propName": "isFullAccess",
                        "val": "false"
                    }
                ]
}' ),
( N'
{
    "propId": 0,
    "propName": "Helo.World",
    "dataDump": [   {
                        "propId": 0,
                        "propName": "fName",
                        "val": "Joe"
                    },
                    {
                        "propId": 0,
                        "propName": "lName",
                        "val": "Harris",
                        "dataDump": [   {
                                            "propId": 0,
                                            "propName": "homePhone",
                                            "val": "2020202020"
                                        }
                                    ]
                    },
                    {
                        "propId": 0,
                        "propName": "isAuthorized",
                        "val": "true"
                    },
                    {
                        "propId": 0,
                        "propName": "isFullAccess",
                        "val": "true"
                    }
                ]
}' ), (N'
{
    "propId": 0,
    "propName": "Helo.World",
    "dataDump": [   {
                        "propId": 0,
                        "propName": "fName",
                        "val": "Olivia"
                    },
                    {
                        "propId": 0,
                        "propName": "lName",
                        "val": "Smith"
                    },
                    {
                        "propId": 0,
                        "propName": "isAuthorized",
                        "val": "false"
                    },
                    {
                        "propId": 0,
                        "propName": "isFullAccess",
                        "val": "false"
                    }
                ]
}' ),( N'
{
    "propId": 0,
    "propName": "Helo.World",
    "dataDump": [   {
                        "propId": 0,
                        "propName": "fName",
                        "val": "George"
                    },
                    {
                        "propId": 0,
                        "propName": "lName",
                        "val": "Hart",
                        "dataDump": [   {
                                            "propId": 0,
                                            "propName": "homePhone",
                                            "val": "3030303030"
                                        },
                                        {
                                            "propId": 0,
                                            "propName": "mobilePhone",
                                            "val": "030303030303"
                                        }
                                    ]
                    },
                    {
                        "propId": 0,
                        "propName": "isFullAccess",
                        "val": "false"
                    }
                ]
}' ) ;

SELECT [Id]
     , [JSONData]
FROM @Data
-- WHERE [JSONData].dataDump.propName = 'isAuthorized'
--       AND [JSONData].dataDump.val = 'true'

目标：

返回具有 "[JSONData].dataDump.propName" = 'isAuthorized' 和 "[JSONData].dataDump.val" = 'true' 的记录的 Id 和 JSON

预期输出：

应该返回第一条和第二条记录，因为尽管第三条记录有“[JSONData].dataDump.propName”='isAuthorized'，但是它没有它的“[JSONData].dataDump.val”='true'（它是'false ') 和第四条记录甚至没有这个属性。

我对在 SQL Server 中解析 JSON 并没有找到按兄弟值过滤的类似示例完全陌生。

我的（不成功的）尝试：

SELECT  *
FROM    @Data AS [T]
CROSS APPLY
        OPENJSON ( [T].[JSONData], '$.dataDump' )
            WITH
                ( [propName] NVARCHAR ( 100 )
                , [val] NVARCHAR ( MAX ) AS JSON ) AS [dat]
WHERE   [dat].[propName] = 'isAuthorized'
        AND [dat].[val] = 'true' ;

这似乎没有返回任何数据。

标签： sqljsonsql-servertsqlsql-server-2016

Answer 1

非常接近，但val只是一个字符串，如propName. 所以

SELECT *
FROM @Data
cross apply openjson(JSONData,'$.dataDump') 
with 
(
  propName nvarchar(200),
  val      nvarchar(200)
)
where propName = 'isAuthorized'
  and val = 'true'