Introduction

SQL Server 2016 has introduced support for JSON data. As of now there are 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.



↑ Return to Top


 


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 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 data in single row single column format.



↑ Return to Top


 

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

↑ Return to Top


 

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 column 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.



↑ Return to Top


 

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 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 the JSON Data has been successfully imported to the table as shown below.



↑ Return to Top


 

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



See Also