CODEWITHSUNDEEP
sqljsonsql-servert-sql
mskuratowski
mskuratowski

Reputation: 4124

Get values and keys from JSON in SQL Server

Let's say I have JSON in SQL Server:

[
    {
        "keyname": "keyname1",
        "valuename": "somestring"
    },
    {
        "keyname": "keyname2",
        "valuename": "somestring"
    },
    {
        "keyname": "keyname3",
        "valuename": 100
    }
]

Is it possible to search JSON by valuename e.g. "somestring" and return keyname and valuename.

I tried to use JSON_VALUE and JSON_QUERY:

SELECT JSON_VALUE(Mydata,'$[0].mydata') AS Mydata
FROM MyDataRows

Upvotes: 1

Views: 97

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272386

You can use OPENJSON with a WITH clause:

SELECT *
FROM OPENJSON(N'[
    {
        "keyname": "keyname1",
        "valuename": "somestring"
    },
    {
        "keyname": "keyname2",
        "valuename": "somestring"
    },
    {
        "keyname": "keyname3",
        "valuename": 100
    }
]')
WITH (
    keyname   NVARCHAR(100) '$.keyname',
    valuename NVARCHAR(100) '$.valuename'
)
WHERE valuename = 'somestring'

Upvotes: 2

Related Questions