SQL Server query JSON Array

Question

I am trying to query some JSON in SQL Server 2016 with the below structure where I would like to find all records where the 'key' has a certain value. Any help on how to do this?

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

Thanks,

Matt

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'