Where clause on json data in SQL Server | JSON_VALUE

A very rare example of this. But I find out suppose you have a list of key values, or you want to search value corresponding of the key, you will pass key and get the value of that from JSON list. Using the below example you can easy to achieve this.

I have this json in some table field.

[{“keyname”:”key1″,”keyVal”:”190″},
{“keyname”:”key2″,”keyVal”:”62″},
{“keyname”:”key3″,”keyVal”:”100″},
{“keyname”:”key4″,”keyVal”:”133″}]

Or you want to find value ok key3

Like you want output should 100, Right?

Example from Direct Json

DECLARE @jsonData nvarchar(max) =N'[{“keyname”:”key1″,”keyVal”:”190″},
{“keyname”:”key2″,”keyVal”:”62″},
{“keyname”:”key3″,”keyVal”:”100″},
{“keyname”:”key4″,”keyVal”:”133″}]’;

SELECT JSON_VALUE(value,’$.keyVal’) FROM OPENJSON(@jsonData, ‘$’) WHERE JSON_VALUE(value, ‘$.keyname’) = ‘key3’;

Example from Table Json Field

DECLARE @jsonData nvarchar(max) =(select top 1 JsonFieldName from SomeTableName);

SELECT JSON_VALUE(value,’$.keyVal’) FROM OPENJSON(@jsonData, ‘$’) WHERE JSON_VALUE(value, ‘$.keyname’) = ‘key3’;