SQL Server 2016 - Bulk Import JSON File Data To Table

SQL Server 2016 has introduced support for JSON data. As of now, there are no JSON-specific data types, SQL Server 2016 continues to use NVARCHAR type to store JSON data. However, it does provide several important T-SQL methods and commands to work with JSON. In this walkthrough, 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, which we defined. Here, we will define the model such that the root node of JSON is Employee details and we will add “repeat(1000)”, so that 1000 JSON key value pairs are generated, which are based on the employee model.

On clicking Generate, it will create 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 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 error given below.

When used with the bulk keyword, we can specify one of the options given below.

• 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 in to 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 the 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 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 has 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 JSON object.

On running the command, JSON data has been converted to a table format with the 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 column using the syntax given below.

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 JSON file. It will read JSON data and build the table format with the table columns as Name, Gender, Company and an 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')

On running the query given above, we will get a fully formatted table output from JSON data.

Insert data into the table

Thus, we are now in a position to insert JSON data, which is read from the file into SQL Server table. We will make use of the Insert into statement along with the above statements to insert the data in to 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 JSON data has been successfully imported to the table, as shown below.

Full code for JSON import and table population

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

JSON file used for the demo is attached with this article.

Summary

Thus, we saw how to read the data from JSON file and use the newly introduced JSON methods in SQL Server 2016 to format it and insert it into the table.