CODEWITHSUNDEEP
arrayssql-serverjson
mnt
mnt

Reputation: 93

SQL Server query JSON Array

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

Upvotes: 6

Views: 11857

Answers (3)

Akira Yamamoto
Akira Yamamoto

Reputation: 4935

SELECT tj.*
FROM TableName t
CROSS APPLY OPENJSON (t.JsonColumn, '$.nodeDataArray')
  WITH (
    key VARCHAR(300) '$.key',
    category VARCHAR(300) '$.category'
  ) AS tj

Source: Microsoft SQL Docs.

Upvotes: 2

David May
David May

Reputation: 388

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'

Upvotes: 9

Brian
Brian

Reputation: 41

If you are on SQL 2016, have a look at this article: JSON Data (SQL Server).

If you are not on SQL 2016, there is no native JSON support. Your best bet would be to write something in .NET and call it from a SQL SP or function (Google can help you get started with this).

Upvotes: 0

Related Questions