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
        }
      ]
    }
  ]
}

Mikael Eriksson · Accepted Answer · 2018-03-07 13:30:23Z

9

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.

answered Mar 7, 2018 at 13:30

Mikael Eriksson

22.3k5 gold badges63 silver badges106 bronze badges

Can you expand on what you mean by creating dynamically

mmv_sat
– mmv_sat

2020-04-21 18:14:19 +00:00
Commented Apr 21, 2020 at 18:14

Add a comment |

Mark Sinkinson · Accepted Answer · 2018-03-07 13:31:31Z

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

Mikael Eriksson · Accepted Answer · 2018-03-08 08:54:40Z

0

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;

edited Mar 8, 2018 at 8:54

Mikael Eriksson

22.3k5 gold badges63 silver badges106 bronze badges

answered Mar 8, 2018 at 8:48

Arvind Mishra

611 gold badge1 silver badge2 bronze badges

Add a comment |

try this · Accepted Answer · 2021-08-24 06:26:03Z

0

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

answered Aug 24, 2021 at 6:26

try this

1

Add a comment |

Stack Exchange Network

Passing variable as parameter to json_query function

4 Answers 4

Your Answer

Linked

Hot Network Questions

Passing variable as parameter to json_query function

4 Answers 4

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions