Import JSON files into SQL Server

By | 29th August 2019

JavaScript Object Notation (JSON) is a lightweight data interchange format. JSON format is widely used to exchange data in most of the web and mobile applications. Also, the REST web services returns results in JSON format. In this article, let’s see how to import JSON file into SQL Server.

In SQL Server, you can import JSON document, parse JSON text, transform array of objects into table format and vice versa using built-in functions and operators.

Like XML, there is no JSON data type to store JSON data in a column. You can use either VARCHAR or NVARCHAR data type.

Import JSON file as string

You can use the OPENROWSET function to read the data from a file. This function reads the entire file content as a string and returns the value as a table with a single column. The value can be stored in a table or a variable for further processing.

DECLARE @JSONText VARCHAR(4000)
SELECT @JSONText = BulkColumn 
FROM OPENROWSET (BULK 'D:\JSONFiles\Employees.JSON', SINGLE_CLOB) as TableA
SELECT @JSONText

Use the data type ‘SINGLE_CLOB’ to import the string data.

Import JSON using OPENROWSET

To validate whether the string is a valid JSON, use ISJSON function. It returns 1 when the input is a valid JSON string.

IF (ISJSON(@JSONText) = 1)
BEGIN 

END

Import JSON data into a table

You can use OPENJSON table-valued function to parse the JSON data and transform it into a tabular format. It returns the objects and properties of the JSON text as rows and columns.

Method 1: With default schema

By default, the OPENJSON function returns three columns – key name, value and the type of each key-value pair.

DECLARE @JSONText VARCHAR(4000) = N'
[{"EmpID":101,"FirstName":"Sean","MiddleInitial":"E","LastName":"Paul"},  
{"EmpID":201,"FirstName":"John","MiddleInitial":"R","LastName":"Peter"},  
{"EmpID":301,"FirstName":"Samantha", "MiddleInitial":null, "LastName":"Sam"}]'

SELECT * FROM OPENJSON(@JSONText)
Import JSON using OPENJSON default schema
OPENJSON with default schema

Type column in the above image returns a number that denotes the data type of the value column. It ranges from 0 through 5 for various data types.

JSON Types

Method 2: With explicit schema definition

As an alternative to default method, you can define the schema of the result by using with clause. This method allows you to specify the column name and its data type.

DECLARE @JSONText VARCHAR(4000) = N'
[{"EmpID":101,"FirstName":"Sean","MiddleInitial":"E","LastName":"Paul"},  
{"EmpID":201,"FirstName":"John","MiddleInitial":"R","LastName":"Peter"},  
{"EmpID":301,"FirstName":"Samantha", "MiddleInitial":null, "LastName":"Sam"}]'

SELECT * FROM OPENJSON(@JSONText)
WITH(EmpID INT, FirstName VARCHAR(25), MiddleInitial CHAR(1), LastName VARCHAR(25))
Import JSON using OPENJSON with explicit schema definition
OPENJSON with explicit schema definition

Below is the consolidated script to import, parse and validate JSON file.

DECLARE @JSONText VARCHAR(4000)
--OPENROWSET method to read JSON file
SELECT @JSONText = BulkColumn FROM OPENROWSET (BULK 'D:\JSONFiles\Employees.JSON', SINGLE_CLOB) AS TableA
SELECT @JSONText

--Validate JSON
IF (ISJSON(@JSONText) = 1)
BEGIN 

--OPENJSON Default Schema
SELECT * FROM OPENJSON(@JSONText)

--OPENJSON Explicit Schema
SELECT * FROM OPENJSON(@JSONText)
WITH(EmpID INT, FirstName VARCHAR(25), MiddleInitial CHAR(1), LastName VARCHAR(25))

END

Leave a Reply

Your email address will not be published. Required fields are marked *