How pass a local variable in Json path during data extraction using Json_value function in sql sever select statement

Question

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;

• user1822: I removed that tags for Postgres and Oracle as this is clearly (only) for SQL Server

Answer 1

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!

• Prabin Swain: Thanks its working
• Rajesh Ranjan: @PrabinSwain Then you should mark this answer correct.
• Prabin Swain: +cast(@id as varchar)+ not working sql server 2016 error throughout The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal.