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

What SQL Server version do you use? Is the query guaranteed to run on this version? — Ido Gal, Sep 05 '17 at 13:41
If you post code or data samples, **PLEASE** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Sep 05 '17 at 13:44

David May · Accepted Answer · 2017-09-05T15:45:59.853

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'

Close, but this is not the same structure I had. I had an array of nodeDataArray which contained the values... — mnt, Sep 05 '17 at 14:30

score 2 · Answer 2 · answered Mar 15 '22 at 06:47

2

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.

answered Mar 15 '22 at 06:47

Akira Yamamoto

4,685
4
42
43

Brian · Answer 3 · 2017-09-05T13:40:03.160

0

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).

edited Sep 05 '17 at 13:40

answered Sep 05 '17 at 13:26

Brian

41
1
8

I'm on SQL Server 2016. The issue really is around direct querying the DB to find all records (we have several hundreds of records with JSON data in them) which contain a certain value for key. The syntax for that is not clear to me. I could write a .Net function to do this of course but was wondering if I could simply send off a query to get the information for me... – mnt Sep 05 '17 at 14:45

SQL Server query JSON Array

3 Answers3

Linked