Importing Json files into SQL Server

Posted on Author

Background:

New to SQL 2016 is the availability to use native JSON support to import and manipulate JSON format data, This support is also in Azure SQLDB.

Setup and Code:

Note: Your database compatibility has to be 130 or higher.

An option if you can’t set your main database to 130 or higher compatibility is to use a staging database and copy your final data to the destination which is on a lower compatible setting.

Resolution:

First thing needed is a SQL Database and table to land the data. A project I worked on was pulling Disaster Summary data from the OPEN Fema web services, this data was then married up to mortgage loans to see who deserved a break on payment and for how long due to a disaster occurring in their area.

  1. Create the database ( Compatibility 130 or higher)
  2. Create the table to store the imported data ( check for column null ability on the Fema Webite and set appropriately for a Production solution)

CREATE TABLE [dbo].[DisasterDeclarationsSummary](
	[id] [varchar](128) NULL,
	[disasterNumber] [int] NULL,
	[ihProgramDeclared] [bit] NULL,
	[iaProgramDeclared] [bit] NULL,
	[hmProgramDeclared] [bit] NULL,
	[totalAmountOnaApproved] [bit] NULL,
	[paProgramDeclared] [bit] NULL,
	[totalObligatedAmountCatAb] [bit] NULL,
	[fyDeclared] [int] NULL,
	[disasterType] [varchar](50) NULL,
	[incidentType] [varchar](50) NULL,
	[title] [varchar](500) NULL,
	[state] [varchar](50) NULL,
	[declarationDate] [varchar](128) NULL,
	[incidentBeginDate] [varchar](128) NULL,
	[incidentEndDate] [varchar](128) NULL,
	[disasterCloseOutDate] [varchar](128) NULL,
	[declaredCountyArea] [varchar](75) NULL,
	[placeCode] [varchar](75) NULL
) ON [PRIMARY]
GO

3.Build the stored procedure to import the data. Here I am showing the procedure importing 1 file, but in Production you could have a wrapper application that imports many files and feeds each to this procedure. In the case of Fema and many other web services they only provide 1000 records at a time so you will need some wrapper to step through the dataset and pull back each set of data until you hit the end.

CREATE PROCEDURE [dbo].[ImportFemaJson]

@strFileName NVARCHAR (255) 

AS 

BEGIN


DECLARE @DisasterSummariesJson_Out NVARCHAR(MAX)
DECLARE @DisasterSummariesJson NVARCHAR(MAX)

DECLARE @SqlStmt NVARCHAR(MAX) 
SET @SqlStmt= 
'Select @DisasterSummariesJson =  BulkColumn FROM OPENROWSET (BULK ''' + @strFileName + ''', SINGLE_BLOB) as json';


EXEC sp_executesql @sqlStmt, N'@DisasterSummariesJson VARCHAR(MAX) OUT', @DisasterSummariesJson_Out OUT


IF (ISJSON(@DisasterSummariesJson_Out) =1) 
BEGIN 
INSERT INTO  [dbo].[DisasterDeclarationsSummary] ([id], [disasterNumber], [ihProgramDeclared], [iaProgramDeclared], [hmProgramDeclared], [totalAmountOnaApproved], [paProgramDeclared], [totalObligatedAmountCatAb], [fyDeclared], [disasterType], [incidentType], [title], [state], [declarationDate], [incidentBeginDate], [incidentEndDate], [disasterCloseOutDate], [declaredCountyArea], [placeCode])
SELECT [id], [disasterNumber], [ihProgramDeclared], [iaProgramDeclared], [hmProgramDeclared], [totalAmountOnaApproved], [paProgramDeclared], [totalObligatedAmountCatAb], [fyDeclared], [disasterType], [incidentType], [title], [state], [declarationDate], [incidentBeginDate], [incidentEndDate], [disasterCloseOutDate], [declaredCountyArea], [placeCode]
FROM OPENJSON (@DisasterSummariesJson_Out,  '$.DisasterDeclarationsSummaries')
WITH (

[id] varchar(128) '$.id',
[disasterNumber] int  '$.disasterNumber', 
[ihProgramDeclared] bit '$.ihProgramDeclared' , 
[iaProgramDeclared] bit '$.iaProgramDeclared', 
[hmProgramDeclared] bit '$.hmProgramDeclared', 
[totalAmountOnaApproved] int '$.totalAmountOnaApproved', 
[paProgramDeclared] bit '$.paProgramDeclared', 
[totalObligatedAmountCatAb] bit  '$.totalObligatedAmountCatAb', 
[fyDeclared] int  '$.fyDeclared', 
[disasterType] varchar(75) '$.disasterType',
[incidentType] varchar(75) '$.incidentType',
[title] varchar(500) '$.title',
[state] varchar (75) '$.state',
[declarationDate] datetime '$.declarationDate', 
[incidentBeginDate] varchar(128) '$.incidentBeginDate',
[incidentEndDate] varchar(128) '$.incidentBeginDate',
[disasterCloseOutDate] varchar(128) '$.disasterCloseOutDate',
[declaredCountyArea] varchar(50) '$.declaredCountyArea',
[placeCode] varchar(75) '$.placeCode'
) 
END

END

      4. Call the procedure passing in the file you downloaded from the webservice:

DECLARE	@return_value int

EXEC	@return_value = [dbo].[ImportFemaJson]
		@strFileName = N'\\TestFiles\JSON\DisasterDeclarationsSummaries.json'

SELECT	'Return Value' = @return_value

GO

Assuming a successful completion you should now see the data in your table.

 

Information on Terms:

JSON – JavaScript Standard Object Notation

 

Subject Links:

Open Fema Data Sets – https://www.fema.gov/openfema-dataset-disaster-declarations-summaries-v1