SQL Server JSON Path – How to Pass Local Variable in JSON_VALUE Function

jsonsql serversql-server-2016

My Query is

declare @id INT
set @id = 69
select 
   (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."69".value')) CorrespondanceNumber,
   (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"@id"***.value')) Agency,
   (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."69".value')) Protocal
from actions_new a
  left join action_data ad on ad.id_ref = a.id
where baid = 12;

Best Answer

You should cast @id to varchar and concate it. like this.

(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"'+cast(@id as varchar)+'"***.value')) Agency,

Complete query is. Hope this will help you.

declare @id INT
set @id = 69
select 
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."69".value')) CorrespondanceNumber,
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"'+cast(@id as varchar)+'"***.value')) Agency,
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."69".value')) Protocal
from actions_new a
left join action_data ad on ad.id_ref = a.id
where baid = 12;

Thanks!

Related Question