SQL Server : OPENJSON error with dynamic JSON columns to store in DB table

Question

I am getting error with below code where I am using SQL Server JSON functions for my project.

I want to try to get json key value pairs to be stored into some tables in DB.

Basically I am trying to hit REST API that is returning a JSON and that I have to store in my database.

  --- JSON document ---

DECLARE @json nvarchar(max) = N'
   [    
    {
    "ProductId":1,
    "ProductName":"Food",
    "ProductDescription":"Apple",
    "DatePurchased":"1995-05-01T00:00:00"
    },   
    {
    "ProductId":2,
    "ProductName":"Electronics",
    "ProductDescription":"TV",
    "DatePurchased":"2018-09-17T00:00:00"
    }
   ]   
'
-- selecting key values from json
SELECT [Key] 
FROM OPENJSON((@json), '$')

DECLARE @columns varchar(max) = N''
DECLARE @schema varchar(max) = N''
DECLARE @stm varchar(max)

-- Columns preparation
SELECT 
   @columns = CONCAT(@columns, ',', QUOTENAME([key])),
   @schema = CONCAT(@schema, ',', QUOTENAME([key]),' varchar(max) ''$.', [key], '')
FROM OPENJSON(@json, '$[0]')


DROP TABLE IF EXISTS #TestData

-- inserting the json values to db table Statement
SET @stm = 'SELECT '+
       CONCAT(STUFF(@columns, 1, 1, ''),' INTO #TestData FROM OPENJSON((SELECT * FROM @json),''$.records'') WITH (',
   STUFF(@schema, 1, 1, ''),')'
   )

PRINT @stm
EXEC(@stm)


::::: OUTPUT ::::

SELECT 
    [ProductId], [ProductName], [ProductDescription], [DatePurchased] 
INTO #TestData 
FROM OPENJSON((SELECT * FROM @json), '$.records') 
     WITH (
             [ProductId] varchar(max) '$.ProductId,
             [ProductName] varchar(max) '$.ProductName,
             [ProductDescription] varchar(max) '$.ProductDescription,
             [DatePurchased] varchar(max) '$.DatePurchased
          )

I get these errors:

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@json".

Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon

Answer 1

There are a handful of things wrong in your query. I'll detail those below, but first here's something that should work for what I think you're trying to do:

DECLARE @json nvarchar(max) = N'
   [    
    {
    "ProductId":1,
    "ProductName":"Food",
    "ProductDescription":"Apple",
    "DatePurchased":"1995-05-01T00:00:00"
    },   
    {
    "ProductId":2,
    "ProductName":"Electronics",
    "ProductDescription":"TV",
    "DatePurchased":"2018-09-17T00:00:00"
    }
   ]   
';

DECLARE @columns nvarchar(max) = N'';
DECLARE @schema nvarchar(max) = N'';
DECLARE @stm nvarchar(max);

-- Columns preparation
WITH keys AS (
    SELECT DISTINCT [Key]
    FROM OPENJSON(@json)
    WITH (
        ObjJson NVARCHAR(MAX) N'$' AS JSON
    ) l1
    CROSS APPLY OPENJSON(l1.ObjJson)
)
SELECT
   @columns = CONCAT(@columns, ',', QUOTENAME([key])),
   @schema = CONCAT(@schema, ',', QUOTENAME([key]),' varchar(max) ''$.', [key], '''')
FROM keys;

DROP TABLE IF EXISTS dbo.wip_testdata;

-- inserting the json values to db table Statement
SET @stm = 'SELECT '+
       CONCAT(STUFF(@columns, 1, 1, ''),
       ' INTO dbo.wip_testdata
         FROM OPENJSON(@json)
         WITH (
             ObjJson NVARCHAR(MAX) N''$'' AS JSON
         ) l1
         CROSS APPLY OPENJSON(l1.ObjJson)
         WITH (',
             STUFF(@schema, 1, 1, ''),') l2'
         );

EXEC sp_executesql @stm, N'@json NVARCHAR(MAX)', @json;

SELECT * FROM dbo.wip_testdata;

Now, to address the issues:

1. You're not digging deep enough into your JSON to get the property names out of the objects within the array for your column names. The CROSS APPLY addresses that. This also was the case within the dynamic SQL statement you're generating.
2. As mentioned in comments, you want to use sp_executesql so that you can pass the JSON down to the inner scope of the dynamic execution. The scope that the dynamic statement running in doesn't have access to @json from the outer scope so you have to pass it in as a parameter to sp_executesql. This is what is causing the Must declare the table variable "@json" error you get.
3. Also an issue with scope is the fact that you can't access the temp table that your dynamic statement creates after the dynamic execution returns. You'll see that I converted that to a physical table so that you can access it in the outer scope once your dynamic statement execution has returned. You could continue to use a temp table but you would need to dynamically create that table prior to executing your dynamic statement. The scope of the dynamic statement DOES have access to temp tables created beforehand from the outer scope.

Answer 2

There are multiple problems with what you have. I'm going go through each of my tips first, starting with Tip 1:

Don't use EXEC ({SQL Variable}) syntax, you can't pass your parameter to the dynamic statement. Use sys.sp_executesql.

This one is pretty much what it says on the tin. The first problem you're having is this error:

Must declare the table variable "@json".

The reason you're getting this error is because @json has no context in the dynamic statement; a variable only has scope inside the statement it is defined; it cannot be access by inner or outer scopes. So how to you use that variable in your dynamic statement? Well you pass it, as a parameter to said dynamic statement.

But, there is a problem, you've used EXEC (@stm) which means it's impossible to pass @json as a parameter. Instead, therefore, you need to use sys.sp_executesql, which you should always be using when writing dynamic statements:

EXEC sys.sp_executesql @stm, N'@json nvarchar(MAX)', @json;

If you do use this though, you'll get another error:

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

This is because you've also incorrectly defined @stm as a varchar(MAX); it needs to be an nvarchar(MAX). If you fix that, you get a new error:

Incorrect syntax near '$.'.

Tip 2:

The easiest way to debug dynamic SQL is to PRINT/SELECT your statement, debug that, and then propagate the solution to the SQL that creates the dynamic statement. If you look at the PRINT you have you'll see an obvious error with your single quotes.

I mean this literally. So let's look at your statement:

SELECT [ProductId],[ProductName],[ProductDescription],[DatePurchased] INTO #TestData FROM OPENJSON((SELECT * FROM @json),'$.records') WITH ([ProductId] varchar(max) '$.ProductId,[ProductName] varchar(max) '$.ProductName,[ProductDescription] varchar(max) '$.ProductDescription,[DatePurchased] varchar(max) '$.DatePurchased)

Well, that's ugly... Let's skip to Tip 3 for now, and come back to tip 2:

When writing dynamic SQL, formatting the statement you create is also important. Long single lines of code are notoriously difficult to debug.

As you can see, the prior SQL statement is very different to read. We really want it to be readable, something like this:

SELECT [ProductId],
       [ProductName],
       [ProductDescription],
       [DatePurchased]
INTO #TestData
FROM OPENJSON((SELECT *
               FROM @json),'$.records')
      WITH ([ProductId] varchar(max) '$.ProductId,
            [ProductName] varchar(max) '$.ProductName,
            [ProductDescription] varchar(max) '$.ProductDescription,
            [DatePurchased] varchar(max) '$.DatePurchased)

Now, the problem above is clear, but let's actually address Tip 3 in full first, and get your statement well formatted as it's written. I'm going to also therefore bin your quirky SET ~{Variable} = @{Variable} FROM dataset syntax; it's undocumented and shouldn't be trusted to work. I'm going to instead use string aggregation. I assume you're on a recent version of SQL Server here; otherwise you'll need to use the "old" FOR XML PATH method.

DECLARE @stm nvarchar(max),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

DECLARE @SelectDelim nvarchar(15) = N',' + @CRLF + N'       ',
        @WithDelim nvarchar(15) = N',' + @CRLF + N'           ';

-- Columns preparation
SELECT @stm = N'SELECT ' +
              STRING_AGG(QUOTENAME([key]),@SelectDelim) + @CRLF +
              N'INTO #TestData' + @CRLF + --This INTO is actually completely pointless, but we'll come onto this later
              N'FROM OPENJSON((SELECT *' + @CRLF +
              N'              FROM @json),''$.records'')' + @CRLF +
              N'     WITH (' + 
              STRING_AGG(QUOTENAME([key]) + N' nvarchar(MAX)', @WithDelim) + N');' --Doing this fixing the issue in point 2
FROM OPENJSON(@json, '$[0]');

PRINT @stm;

In this I actually fix your quote issue, if we look at the printed statement, we now get the following:

SELECT [ProductId],
       [ProductName],
       [ProductDescription],
       [DatePurchased]
INTO #TestData
FROM OPENJSON((SELECT *
              FROM @json),'$.records')
     WITH ([ProductId] nvarchar(MAX),
           [ProductName] nvarchar(MAX),
           [ProductDescription] nvarchar(MAX),
           [DatePurchased] nvarchar(MAX));

Looks nice doesn't it! Now let's add that call to sys.sp_executesql:

Must declare the table variable "@json".

Oh, what's that? WEll that's cause of these lines:

FROM OPENJSON((SELECT *
              FROM @json),'$.records')

@json isn't a table variable it's a scalar variable. So let's fix that and change it to the following:

FROM OPENJSON(@json,'$.records')

And, it runs! Yes! Now let's try and get that data with the following outside of the dynamic statement:

SELECT *
FROM #TestData;

Invalid object name '#TestData'.

Wait, what?! Well we're back to our friend "scope". By the time the dynamic SQL statement has run and we're back into the outer scope, the temporary table has been dropped, hence the above error. Let's, therefore, just comment out that INTO (as I don't know what you're plans are for it).

But now we get no data? Well, that's cause you have OPENJSON(@json,''$.records'') it should just be OPENJSON(@json). So, let's finally fix that, and then it all works!

Full working statement:

  --- JSON document ---

DECLARE @json nvarchar(max) = N'[    
    {
    "ProductId":1,
    "ProductName":"Food",
    "ProductDescription":"Apple",
    "DatePurchased":"1995-05-01T00:00:00"
    },   
    {
    "ProductId":2,
    "ProductName":"Electronics",
    "ProductDescription":"TV",
    "DatePurchased":"2018-09-17T00:00:00"
    }
   ]';

DECLARE @stm nvarchar(max),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

DECLARE @SelectDelim nvarchar(15) = N',' + @CRLF + N'       ',
        @WithDelim nvarchar(15) = N',' + @CRLF + N'           ';

-- Columns preparation
SELECT @stm = N'SELECT ' +
              STRING_AGG(QUOTENAME([key]),@SelectDelim) + @CRLF +
              N'--INTO #TestData' + @CRLF + --This INTO is actually completely pointless, but we'll come onto this later
              N'FROM OPENJSON(@json)' + @CRLF +
              N'     WITH (' + 
              STRING_AGG(QUOTENAME([key]) + N' nvarchar(MAX)', @WithDelim) + N');' --Doing this fixing the issue in point 2
FROM OPENJSON(@json, '$[0]');

PRINT @stm;

EXEC sys.sp_executesql @stm, N'@json nvarchar(MAX)', @json;

Dynamic SQL run:

SELECT [ProductId],
       [ProductName],
       [ProductDescription],
       [DatePurchased]
--INTO #TestData
FROM OPENJSON(@json)
     WITH ([ProductId] nvarchar(MAX),
           [ProductName] nvarchar(MAX),
           [ProductDescription] nvarchar(MAX),
           [DatePurchased] nvarchar(MAX));