A Sample to Transform JSON File from barchart.com to SQL Server Table (SQL Server 2016)


  
--Sample to download json file with your own apikey (paid subscription )
--http://ondemand.websol.barchart.com/getGrainBids.json?apikey=1234yourapikey789&zipCode=00000&maxDistance=100&getAllLocations=1&getAllBids=1

---a table to hold json file data
Drop  table  if exists dbo.RawData_JsonFileTemp

Declare @sql nvarchar(max);
Declare @ColumnHeaders VARCHAR(MAX) 

declare @json1 varchar(max) 
--
SELECT @json1=BulkColumn--,isjson(BulkColumn)
  FROM OPENROWSET (BULK 'C:\temp\JSON\getGrainBids.json', SINGLE_CLOB) t

;with mycteSrc as (
 SELECT  c.[key],  c.[value] 
 FROM OPENJSON(@json1, '$.results') as c
 
)

,myctebids as (
Select  d.value
from mycteSrc
cross apply  OPENJSON ( value,  '$.bids' )   d
)
,myColumnList as (
select 1 as seq, e.[key] ,   'JSON_Value (b.value, ''$.'+e.[key]+ ''') as '+ quotename(e.[key])   col
from myctebids
cross apply   OPENJSON ( value) e
UNION 
Select 2 as seq,  d.[key] ,   'JSON_Value (c.value, ''$.'+d.[key]+ ''') as '+ quotename(d.[key])  
from mycteSrc
cross apply   OPENJSON ( value ) d
)


Select @ColumnHeaders = STUFF( (SELECT  ','   + col  
  FROM myColumnList
 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
 
 --Print @ColumnHeaders
 Select @sql='SELECT '+@ColumnHeaders+ '
  into RawData_JsonFileTemp from OPENJSON('''+ @json1+'''  , ''$.results'') as c
 CROSS APPLY OPENJSON (c.value, ''$.bids'')  b
 '
 --print @sql
EXEC sp_executesql @sql;


--Result
 SELECT *  FROM dbo.RawData_JsonFileTemp 
 
 


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s