T-SQL OPENJSON path as variable

Question

declare @Path as nvarchar(100) 
set @path = '$.path.to."sub-object"'

DECLARE @json NVARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

SELECT [key], value
FROM OPENJSON(@json, @Path)

I got error:

Incorrect syntax near '@Path'.

how to declare @path that i can change it.

Answer 1

Passing path as variable to OPENJSON is available from SQL Server 2017 (aka vNext):

In SQL Server 2017 and in Azure SQL Database, you can provide a variable as the value of path.

declare @Path as nvarchar(100) 
set @path = '$.path.to."sub-object"'

DECLARE @json NVARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

SELECT [key], value
FROM OPENJSON(@json, @Path);

DbFiddle Demo

Answer 2

Interesting it appears you are following this link: https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql.

This works when done inline:

DECLARE @json VARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

SELECT [key], value
FROM OPENJSON(@json, '$.path.to."sub-object"')

When you make the change to a reference of a variable it does not:

declare @Path as nvarchar(128)  = '$.path.to."sub-object"'

DECLARE @json nVARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

SELECT [key], value
FROM OPENJSON(@json, @Path)

UPDATED Kind of a hack but it works

declare @Path as nvarchar(128)  = '$.path.to."sub-object"'

DECLARE @json nVARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

DECLARE @SQL NVARCHAR(MAX) = 
'SELECT [key], value
FROM OPENJSON(''' + @json + ''', ''' + @Path + ''')'

EXEC sp_executesql @Sql