SQL Server 2016: Bulk Import JSON file data to Table
Introduction
SQL Server 2016 has introduced support for JSON data. As of now, there is no JSON-specific data type, SQL Server 2016 continues to use the NVARCHAR type to store JSON data. However, it does provide several important T-SQL method and command to work with JSON. In this walk through we will see some of the newly introduced JSON methods and see how we can bulk import JSON file data to SQL Server table.
Generate Bulk JSON file
If we don’t have a ready-made JSON to test out this demo, we can make use of the Online Service that generates random JSON data as per the model that we define. Here we will define the model such that the root node of the JSON is Employee Details and we will add “repeat(1000)” so that 1000 JSON key value pairs are generated based on the employee model.
On clicking Generate it will create the JSON data as shown below:
Let’s copy and paste it into a file with JSON extension.
Create a SQL Server Table
In order to import the data from JSON file, we will create a table with the name EmployeeDetails that has the similar columns as in JSON data.
OPENROWSET Bulk Import
SQL Server has the OPENROWSET command that enables bulk import of data from a file. It has the syntax:
SELECT BulkColumn
FROM OPENROWSET (BULK ‘TextFile Path’, SINGLE_BLOB) FileName
This command returns the data from the text file as a single row single column data to ‘BulkColumn’. We have to specify a correlation file name at the end of the command which will act as an alias name for the OPENROWSET. If we skip it we will get the below error.
When used with the Bulk Keyword we can specify one of the below options:
- SINGLE_BLOB, reads a file as varbinary(max)
- SINGLE_CLOB, reads a file as varchar(max) and in ASCII format
- SINGLE_NCLOB, reads a file as nvarchar(max) and in UNICODE format
In our case, we will read the content into a variable “EmployeeDetails”. We will use the OPENROWSET to read the contents into this variable as shown below:
DECLARE @EmployeeDetails VARCHAR(MAX)
SELECT @EmployeeDetails = BulkColumn FROM OPENROWSET(BULK'C:\Users\SQLAdmin\Desktop\JSONFile\SampleJSON.json', SINGLE_BLOB) JSON;
SELECT @EmployeeDetails as SingleRow_Column
It will return data in single row single column format.
Check for Valid JSON
We can check and see if the returned data is a valid JSON using the new JSON commands available in SQL Server 2016. ISJSON checks for the syntactic correctness of the imported JSON data.
IF (ISJSON(@EmployeeDetails) = 1)
It has checked our imported data and validated its syntax.
Now let’s see the result by importing an invalid JSON Data. We will strip of a Quotation from “Employee” and read it using OPENROWSET.
It has successfully imported the data to the variable with an improper syntax.
However, ISJSON has validated the data and thrown the error message.
DECLARE @EmployeeDetails VARCHAR(MAX)
SELECT @EmployeeDetails = BulkColumn FROM OPENROWSET(BULK'C:\Users\SQLAdmin\Desktop\JSONFile\SampleJSON.json', SINGLE_BLOB) JSON;
SELECT @EmployeeDetails as SingleRow_Column
IF (ISJSON(@EmployeeDetails) = 1)
BEGIN
PRINT 'Imported JSON is Valid'
END
ELSE
BEGIN
PRINT 'Invalid JSON Imported'
END
GO
Convert Single Row to Multiple Rows
The data returned by OPENROWSET is a single row single column data which is not of much use. We need to split the JSON data as individual rows if we need to work on the imported data. We can make use of OPENJSON to read the OPENROWSET data from ‘EmployeeDetails’ variable.
SELECT * FROM OPENJSON(@EmployeeDetails, '$.EmployeeDetails.Employee')
Here EmployeeDetails.Employee indicates the starting node of the JSON object.
On running the command, JSON data has been converted to a table format with individual rows.
Convert Value Column to Multiple Rows
Though the Single Row Single Column data format of OPENROWSET has been converted to multiple rows, we still have a single column for the data – “value”. We will split the column to multiple columns using the syntax:
SELECT * FROM
OPENJSON ( @jsonVariable )
WITH (
Col1 varchar(200) '$.Order.ObjectPath' ,
Col2 datetime '$.Order.ObjectPath'
)
In our case, the object path is the location of the ‘key’ in the JSON file. It will read the JSON Data and build the table format with table columns as Name,Gender,Company and Email.
SELECT Name,Gender,Company,Email
FROM OPENJSON(@EmployeeDetails, '$.EmployeeDetails.Employee')
WITH(
Name nvarchar(50) '$.name',
Gender nvarchar(50) '$.gender',
Company nvarchar(50) '$.company',
Email nvarchar(50) '$.email'
)
Upon running the above query we will get a fully formatted table output from the JSON data.
Insert Data into the Table
Thus we are now in a position to insert the JSON data read from the file into the SQL Server table. We will make use of the Insert Into statement along with the above statements to insert the data into the table.
INSERT INTO EmployeeDetails
SELECT Name,Gender,Company,Email
FROM OPENJSON(@EmployeeDetails, '$.EmployeeDetails.Employee')
WITH(
Name nvarchar(50) '$.name',
Gender nvarchar(50) '$.gender',
Company nvarchar(50) '$.company',
Email nvarchar(50) '$.email'
)
Heading over to the table, we can see that the JSON Data has been successfully imported to the table as shown below.
Full Code for JSON Import and Table Population
The full SQL Query that we had cross sectioned and examined is given below:
DECLARE @EmployeeDetails VARCHAR(MAX)
SELECT @EmployeeDetails = BulkColumn FROM OPENROWSET(BULK'C:\Users\SQLAdmin\Desktop\JSONFile\SampleJSON.json', SINGLE_BLOB) JSON;
SELECT @EmployeeDetails as SingleRow_Column
IF (ISJSON(@EmployeeDetails) = 1)
BEGIN
PRINT 'Imported JSON is Valid'
END
ELSE
BEGIN
PRINT 'Invalid JSON Imported'
END
SELECT * FROM OPENJSON(@EmployeeDetails, '$.EmployeeDetails.Employee')
SELECT Name,Gender,Company,Email
FROM OPENJSON(@EmployeeDetails, '$.EmployeeDetails.Employee')
WITH(
Name nvarchar(50) '$.name',
Gender nvarchar(50) '$.gender',
Company nvarchar(50) '$.company',
Email nvarchar(50) '$.email'
)
INSERT INTO EmployeeDetails
SELECT Name,Gender,Company,Email
FROM OPENJSON(@EmployeeDetails, '$.EmployeeDetails.Employee')
WITH(
Name nvarchar(50) '$.name',
Gender nvarchar(50) '$.gender',
Company nvarchar(50) '$.company',
Email nvarchar(50) '$.email'
)
Summary
Thus we saw how to read data from the JSON file and use the newly introduced JSON methods in SQL Server 2016 to format it and insert it into the table