JSON_VALUE does not take dynamic JSON path

Question

I am using SQL Server 2016.

While this works fine:

SELECT JSON_VALUE(N'[{"FieldName":"xyz"}]', N'strict $[0]."FieldName"')

This retruns an error:

DECLARE @x nvarchar(100)
SET @x =N'0'

SELECT JSON_VALUE(N'[{"FieldName":"xyz"}]', N'strict $['+@x+']."FieldName"')

SELECT JSON_VALUE(N'[{"FieldName":"xyz"}]', N'strict $[sql:variable(@x)]."FieldName"')

Error:

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

How can I solve this?

Answer 1

You need to use SQL Server 2017+. As is mentioned in the documentation, in SQL Server 2017 (14.x) and in Azure SQL Database, you can provide a variable as the value of path. But even then, I don't think that N'strict $[sql:variable(@x)]."FieldName"' is a valid path expresison for a JSON_VALUE() call.

Working example for SQL Server 2017+:

DECLARE @x nvarchar(100)
SET @x = N'0'

SELECT JSON_VALUE(N'[{"FieldName":"xyz"}]', N'strict $[' + @x + ']."FieldName"')

For SQL Server 2016 you may try the following:

DECLARE @json nvarchar(100) = N'[{"FieldName":"xyz"}]'
DECLARE @x nvarchar(100) = N'0'

SELECT JSON_VALUE([value], '$.FieldName')
FROM OPENJSON(@json) j1
WHERE [key] = @x