Get values and keys from JSON in SQL Server

Question

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

Salman Arshad · Accepted Answer · 2019-08-29 12:17:51Z

2

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'

answered Aug 29, 2019 at 12:17

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

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

Collectives™ on Stack Overflow

Get values and keys from JSON in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related