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.

What you posted does not error out on my system. What version of SQL Server are you running? OPENJSON is a 2016 feature. — Alan Burstein
– Alan Burstein, Commented Aug 7, 2017 at 14:13

Lukasz Szozda · Accepted Answer · 2017-08-08 05:20:21Z

5

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

answered Aug 8, 2017 at 5:20

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

5 Comments

djangojazz Over a year ago

Thank God, that seemed ridiculously short sighted it was not in 2016.

Misbit Over a year ago

This is great, thanks for pointing that out! I do have a problem though; in SSMS this works fine but when added in a stored procedure in a SQL project in Visual Studio it doesn't recognize the syntax, marks it as an error thus preventing me to build/deploy. Anyone else having this issue? Am I perhaps missing an add-on to handle new syntax? I've updated everything I can find.

Lukasz Szozda Over a year ago

@vsdev Probably incorrect target platform. Please check your project properties and set to SQL Server 2016.

Misbit Over a year ago

@lad2025 I actually tried 2016, 2017 as well as the Azure once. No change. It always marks it as an error synthax. "SQL46010: Incorrect syntax near @JsonPath"

Jargon Over a year ago

@vsdev This is still an issue in stored procedures with the latest version of VS2017 (15.9.5) and SSDT. Looks like this is never going to get fixed. How did you get around the issue?

djangojazz · Accepted Answer · 2017-08-07 16:47:05Z

3

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

edited Aug 7, 2017 at 16:47

answered Aug 7, 2017 at 14:37

djangojazz

13.2k10 gold badges64 silver badges101 bronze badges

3 Comments

Mindaugas Petruškevičius Over a year ago

the question is, How to do that second example would work?

djangojazz Over a year ago

I can hack it to work with dynamic sql but that seems silly. There has to be a better way to do this. It must be the type of the path it does not like.

djangojazz Over a year ago

I'm glad, happy coding. If you are satisfied with the answer please mark it complete.

Collectives™ on Stack Overflow

T-SQL OPENJSON path as variable

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related