Passing variable as parameter to json_query function

Question

I am getting error:

The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal

...while trying to pass variable as parameter to JSON_QUERY function. Please help me to resolve it.

DECLARE @cnt INT = 1;
DECLARE @cnt_total INT = 1;
DECLARE @json NVARCHAR(MAX);
DECLARE @json1 NVARCHAR(MAX);
declare @str VARCHAR(200);
WHILE @cnt <= 10
BEGIN
 set @str = '$.Seasons[0].Products['+convert(varchar,@cnt)+'].ProductChannels';
 set @str = char(39) +@str+ char(39);
 PRINT @STR
 select @json = json_query(jfile,@STR) from Import.tstjson;
--MORE CODE 
 SET @cnt = @cnt + 1;
END

Sample data:

{
 "Seasons": [
 {
 "Season": "12321231",
 "Products": [
 {
 "ProductId": "211",
 "ProductChannels": [
 {
 "ChannelId": 1,
 "WeekQuantities": []
 }
 ],
 "ccc": 3,
 "Tttt": 4
 }
 ]
 }
 ]
}

Answer 1

It is possible in SQL Server 2017.

From JSON_QUERY (Transact-SQL)

In SQL Server 2017 and in Azure SQL Database, you can provide a variable as the value of path.

In SQL Server 2016 you have to build the query dynamically.

• mmv_sat: Can you expand on what you mean by creating dynamically

Answer 2

I would guess that as it says 'string literal' you must pass json_query a string and not a variable, like in the BOL example

SELECT PersonID,FullName,
 JSON_QUERY(CustomFields,'$.OtherLanguages') AS Languages
FROM Application.People

So in your case, you'd probably need to EXEC.

Disclaimer: you'll need to ensure that your implemented code is not subject to SQL Injection.

DECLARE @SQL VARCHAR(500); 
DECLARE @cnt INT = 1;
WHILE @cnt <= 10
BEGIN
 SET @SQL = 'SELECT json_query(jfile, ''$.Seasons[0].Products['+convert(varchar(2),@cnt)+'].ProductChannels'') from Import.tstjson';
 EXEC @SQL;
 SET @cnt = @cnt + 1;
END

Answer 3

Thanks. It's working now. One can take help of following code for similar problems

SET @SQLString = 'SELECT @json = json_query(jfile, ''$.Seasons[0].Products['+convert(varchar,@cnt)+'].ProductChannels'') from Import.tstjson';
 print @SQLString
 SET @ParmDefinition = N'@json NVARCHAR(MAX) OUTPUT';
 EXECUTE sp_executesql 
 @SQLString 
 ,@ParmDefinition 
 ,@json = @json1 OUTPUT; 

Answer 4

 WHILE ( @start < @count)
 BEGIN
 declare @val varchar =@start;
 SET @json=JSON_MODIFY(@json,'$['+@val+'].value', @value);
 SET @start = @start + 1;
 END