CODEWITHSUNDEEP
.netjsonsql-serversql-server-2017
user11915641
user11915641

Reputation:

SQL Server: Dynamically create Column Names from JSON Auto Data

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);

Upvotes: 1

Views: 11297

Answers (3)

Karl Henselin
Karl Henselin

Reputation: 1034

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

Upvotes: 0

Zhorov
Zhorov

Reputation: 29943

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

Upvotes: 3

hardikpatel
hardikpatel

Reputation: 310

    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

Upvotes: 0

Related Questions