How to Import JSON files into SQL Server -

Contents hide

2.2 Method 2: With explicit schema definition

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

In SQL Server, you can import JSON documents, parse JSON text, transform an array of objects into a 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.

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.

Method 2: With explicit schema definition

As an alternative to the 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 an explicit schema definition

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

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