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?

Answer 1

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"[]