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
– Ido Gal, Commented Sep 5, 2017 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
– marc_s, Commented Sep 5, 2017 at 13:44

David May · Accepted Answer · 2017-09-05 15:45:59Z

9

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'

edited Sep 5, 2017 at 15:45

answered Sep 5, 2017 at 13:51

David May

3881 gold badge3 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mnt Over a year ago

Close, but this is not the same structure I had. I had an array of nodeDataArray which contained the values...

Akira Yamamoto · Accepted Answer · 2022-03-15 06:47:20Z

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, 2022 at 6:47

Akira Yamamoto

4,9754 gold badges46 silver badges43 bronze badges

Comments

Brian · Accepted Answer · 2017-09-05 13:40:03Z

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 5, 2017 at 13:40

answered Sep 5, 2017 at 13:26

Brian

491 silver badge8 bronze badges

1 Comment

mnt Over a year ago

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

Collectives™ on Stack Overflow

SQL Server query JSON Array

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related