arrays - SQL Server 查询 JSON 数组

Question

我正在尝试使用以下结构在 SQL Server 2016 中查询一些 JSON，我想在其中查找“键”具有特定值的所有记录。有关如何执行此操作的任何帮助？

{
    "nodeDataArray": [{
        "key": "5B502176-E51A-48B7-B8F0-350984CFBCF2",
        "category": "IFM"
    }, {
        "key": "1260263E-6111-47B2-9776-FE9BA5C90DCB",
        "category": "IFM"
    }, {
        "key": "8AE454D3-944E-47BE-8CA9-049318DE213B",
        "category": "IFM"
    }, {
        "key": "96B20972-F88C-44BA-84AA-C1F45BE5C7D5",
        "category": "IFM"
    }
    }]
}

谢谢，

马特

Answer 1

DECLARE @json NVARCHAR(MAX)


SET @json = N'{
    "nodeDataArray": [  
    {
       "key": "5B502176-E51A-48B7-B8F0-350984CFBCF2",
        "category": "IFM"
    }, 
    {
        "key": "1260263E-6111-47B2-9776-FE9BA5C90DCB",
        "category": "IFM"
    }, 
    {
        "key": "8AE454D3-944E-47BE-8CA9-049318DE213B",
        "category": "IFM"
    }, 
    {
        "key": "96B20972-F88C-44BA-84AA-C1F45BE5C7D5",
        "category": "IFM"
    }
]
}'

SELECT  
    JSON_VALUE(nda.value, '$.key') AS [key],
    JSON_VALUE(nda.value, '$.category') AS [category]
FROM OPENJSON(@json, '$.nodeDataArray') AS nda
WHERE  JSON_VALUE(nda.value, '$.key')   = '1260263E-6111-47B2-9776-FE9BA5C90DCB'