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?

The sql:variable() syntax is not supported in any version; this only works for XML queries. — Jeroen Mostert
– Jeroen Mostert, Commented Sep 29, 2020 at 7:36

Zhorov · Accepted Answer · 2020-09-29 07:58:07Z

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

edited Sep 29, 2020 at 7:58

answered Sep 29, 2020 at 7:36

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JSON_VALUE does not take dynamic JSON path

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related