1

I created JSON Data with JSONAUTO in SQL Server.

   SELECT *
   from #Testdata
   FOR JSON AUTO

Its stored in nvarchar(max), and create multiple records of the same type.

[{"ProductId":1,"ProductName":"Food","ProductDescription":"Apple","DatePurchased":"1995-05-01T00:00:00"},   {"ProductId":2,"ProductName":"Electronics","ProductDescription":"TV","DatePurchase":"2018-09-17T00:00:00"}]

Now I want to insert into Tablename with Exact same Column names as Json Fields. Trying to Dynamically create the column names, to insert into table with exact same column names. How would I conduct this with SQL Server Json library?

insert into dbo.SampleTable
(
      ProductId,
      ProductName, 
      ProductDescription, 
      DatePurchased
)

Now allowed to Explicity state the column numes, from typing, column names must be derived from Json data itself. Will have future types of data later.

Update:

Reading this answer, wondering if there is a way to bypass removing first and last letter, doesn't SQL server have an internal library function? This is what I am looking for, while eliminating the substring stuff, which is removing brackets [ and ].

SQL Server - Using JSON to return Column Names

declare @json NVARCHAR(MAX)  = '[{"ProductId":1,"ProductName":"Food","ProductDescription":"Apple","DatePurchased":"1995-05-01T00:00:00"},   {"ProductId":2,"ProductName":"Electronics","ProductDescription":"TV","DatePurchase":"2018-09-17T00:00:00"}]'

DECLARE @jsonformatted NVARCHAR(MAX) =  substring(@json, 2, len(@json)-1)
SELECT * FROM OPENJSON(@jsonformatted);
Improve this question

3 Answers 3

Reset to default
3

Explanations:

If you don't know the column names, you need to use OPENJSON() with default schema to return a table with one row for each property of the object or for each element in the array. In your case you may try to query the first item of your JSON data.

T-SQL:

SELECT [key] 
FROM OPENJSON(@json, '$[0]')

Output:

-------------------
key
-------------------
ProductId
ProductName
ProductDescription
DatePurchase

Example:

If you need more complex data manipulation, you may try to generate and execute a dynamic statement:

JSON:

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"
    }
   ]   
'

Dynamic INSERT statement:

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

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

-- Statement
SET @stm = CONCAT(
   N'INSERT INTO #TestData (',
   STUFF(@columns, 1, 1, N''),
   N') SELECT * FROM OPENJSON(@json) WITH (',
   STUFF(@schema, 1, 1, N''),   
   N') json'
)

PRINT @stm
EXEC sp_executesql @stm

Generated statement:

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

Original answer:

If I understand you correctly, you need to use OPENJSON() with explicit schema definition.

Statement:

INSERT INTO #TestData
   (ProductId, ProductName, ProductDescription, DatePurchased)
SELECT *
FROM OPENJSON(@json) WITH (
   ProductId varchar(100) '$.ProductId',
   ProductName varchar(100) '$.ProductName', 
   ProductDescription varchar(100) '$.ProductDescription', 
   DatePurchased datetime '$.DatePurchased'
) json
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

hi, not exactly what I am looking for, I cannot Directly Explicit state the column names, they must be dynamic,I will be reading variable column names later, will restate in question, thanks
@MattSmith I've made an update. Probably a dynamic statement is more appropriate in this case. Thanks.
this just makes the answer more complicated, the point is to Simplify answer in question above
0 
    DECLARE @jsonata NVARCHAR(MAX)='[{"ProductId":1,"ProductName":"Food","ProductDescription":"Apple","DatePurchase":"1995-05-01T00:00:00"},   {"ProductId":2,"ProductName":"Electronics","ProductDescription":"TV","DatePurchase":"2018-09-17T00:00:00"}]'

DECLARE @Query NVARCHAR(MAX)=N' INSERT INTO [TableName] ('

SELECT @Query+=T.ColumnName+','
FROM
(    
SELECT   y.[key] AS ColumnName,
         x.[Key]                                    
FROM OPENJSON(@jsonata) x
CROSS APPLY (SELECT * FROM OPENJSON(x.[value])) y
) AS T
WHERE T.[Key]=0


SET @Query=STUFF(@Query,LEN(@Query),1,' ')+' ) VALUES ('


SELECT @Query+=''''+T.Value+''''+
       (CASE WHEN ISNULL(LEAD(T.[Key]) OVER(ORDER BY T.[Key]),0)=T.[Key] THEN ',' ELSE '),(' END) 
FROM
(
SELECT   y.[key] AS ColumnName,
         y.[Value],
         X.[key]
FROM OPENJSON(@jsonata) x
CROSS APPLY (SELECT * FROM OPENJSON(x.[value])) y
) AS T

SET @Query=SUBSTRING(@Query,1,LEN(@Query)-2)

--Print Query Statment
PRINT (@Query)

EXEC(@Query)

  [1]: https://i.sstatic.net/KpNKc.png
Improve this answer

1 Comment

this just makes the answer more complicated, the point is to Simplify answer in question above
0

If you don't want the array brackets around it, then you may want

SELECT * from #Testdata FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

But, be aware that if you get multiple rows, that would be invalid JSON.

See Microsoft documentation: https://learn.microsoft.com/en-us/sql/relational-databases/json/remove-square-brackets-from-json-without-array-wrapper-option?view=sql-server-ver15

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.