SQL Sever 2016 - Inconsistent Behavior - The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal

Question

We are using SQL Server 2016 and I wrote a function that uses the following code:

IF @JsonString IS NULL OR LTRIM(RTRIM(@JsonString)) = ''
BEGIN
   RETURN NULL;
END

DECLARE @ArrayValues VARCHAR(100);

SELECT @ArrayValues =   REPLACE(REPLACE(REPLACE(
                      JSON_QUERY(@JsonString, @JsonPathToArray)
                   ,'"', ''), '[', ''), ']', '');

RETURN @ArrayValues;

Basically, I get the array and remove the delimiting characters.

I have 2 stored procedures that use this function. One works perfectly, the other does not.

Any ideas?

The [documentation](https://learn.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql?view=sql-server-ver15#arguments) states, that you need SQL Server 2017 to _provide a variable as the value of path_. — Zhorov, Sep 30 '20 at 19:07
Similiar case [C# Dapper using JSON_VALUE for SQL Server 2016](https://stackoverflow.com/questions/46860751/c-sharp-dapper-using-json-value-for-sql-server-2016/) — Lukasz Szozda, Sep 30 '20 at 19:40
I understand that you need SQL 2017+ and I read the articles posted here, after the 2nd stored procedure was failing. My question is does any one have an idea? Is it just because it is 2016 and may be inconsistent? — crimsonisland, Oct 01 '20 at 13:46
@crimsonisland Yor code is correct, but you need SQL Server 2017+. — Zhorov, Oct 01 '20 at 13:47

Zhorov · Answer 1 · 2020-10-02T06:35:50.270

Explanations:

The documentation states, that you need SQL Server 2017+ to provide a ...variable as the value of path... and this is the explanation for the "The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal." error in your statement.

For SQL Server 2016 you may use OPENJSON():

DECLARE @ArrayValues VARCHAR(100)
DECLARE @JsonString VARCHAR(100) = '{"Key" :["a","b","c"]}'
DECLARE @JsonPathToArray VARCHAR(100) = '$.Key'

SELECT @ArrayValues = REPLACE(REPLACE(REPLACE(
                         JSON_QUERY([value])
                      ,'"', ''), '[', ''), ']', '')
FROM OPENJSON(@JsonString)                      
WHERE CONCAT('$.', [key]) = @JsonPathToArray
                      
SELECT @ArrayValues

Additional notes:

Note, that the statement from your current apporach replaces every occurrence of the ", [ and ] characters, even if these characters are part of the JSON values.

So, the statement from the question:

DECLARE @ArrayValues VARCHAR(100)
DECLARE @JsonString VARCHAR(100) = '{"Key" :["a","b","c","EscapedContent\"[]"]}'
DECLARE @JsonPathToArray VARCHAR(100) = '$.Key'

SELECT @ArrayValues = REPLACE(REPLACE(REPLACE(
                         JSON_QUERY(@JsonString, @JsonPathToArray)
                      ,'"', ''), '[', ''), ']', '');
SELECT @ArrayValues

returns:

a,b,c,EscapedContent\

If you want to aggregate the items from one JSON array, you may try a different approach (again SQL Server 2017+ is needed):

DECLARE @ArrayValues VARCHAR(100)
DECLARE @JsonString VARCHAR(100) = '{"Key" :["a","b","c", "EscapedContent\"[]"]}'
DECLARE @JsonPathToArray VARCHAR(100) = '$.Key'

SELECT @ArrayValues = STRING_AGG([value], ',') WITHIN GROUP (ORDER BY CONVERT(int, [key])) 
FROm OPENJSON(@JsonString, @JsonPathToArray)
SELECT @ArrayValues

Result:

a,b,c,EscapedContent"[]

SQL Sever 2016 - Inconsistent Behavior - The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal

1 Answers1

Linked