I am trying to do this:
JSON_VALUE(@jsondata, CONCAT('$.[', @count, '].Name')
such that I can reference a value in my json list using a variable, however the JSON_VALUE function requires a string literal for the 2nd argument (JSON Path).
Is there a way to either make the function read a string as a string literal, or another way to reference a list with a variable?
Thanks.
You can address a list with a variable by destructuring it into a table using OPENJSON.
For example, given:
declare @jsondata nvarchar(max) = '[
{"Name":"zero","Value":"cero"},
{"Name":"one","Value":"uno"},
{"Name":"two","Value":"dos"},
{"Name":"three","Value":"tres"}
]'
declare @count int set @count = 1
declare @val nvarchar(50)
set @val = (select JSON_VALUE(value, '$.Name') from OPENJSON(@JsonData) d where d."key" = @count)
select @count, @val
For a more advanced method you can query JSON with a schema that describes the structure and elements you are interested in:
