Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Different Ways to Import JSON Files into SQL Server


By:   |   Updated: 2018-03-01   |   Comments (6)   |   Related: More > Import and Export

Problem

Most web applications are designed to exchange data in the JSON format. In addition, application logs are also available in JSON format. So, it is evident that we need to load JSON files into the database for analysis and reporting. In this tip, I will load sample JSON files into SQL Server.

Solution

Following are a couple examples of how to load JSON files into SQL Server.

Importing simple JSON file into SQL Server

In this example, the data file contains the order details such as "OrderID", "CustomerID" and "OrderStatus" for 2 orders. The below image represents the supplied JSON data file to load data into SQL server.

Orders JSON File - Description: Orders JSON File

Importing files using OPENROWSET

Openrowset is a table value function that can read data from a file. This function returns a table with a single column (Column Name: Bulk). With a simple syntax, this function will load the entire file content as a text value. Using this method, the entire file content can be loaded into a table or it can be stored in a variable for further processing.

The below script will load the contents of the Orders JSON file.

SELECT * 
FROM OPENROWSET (BULK 'E:\Temp\Data\Orders.JSON', SINGLE_CLOB) as j
			
Simple method to Query a JSON file - Description: Simple method to Query a JSON file

The actual contents of the JSON file can be read using the "BulkColumn" and that can be stored in a variable. It is always recommended to validate the JSON with the ISJSON function, before trying to use the JSON data. This function will return 1 if it’s a valid JSON format.

The below script will read the Orders.JSON file and store its contents in a variable with the help of the "BulkColumn" variable. In addition, this script will also validate the JSON using ISJSON function.

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'E:\Temp\Data\Orders.JSON', SINGLE_CLOB) as j

Select @JSON

If (ISJSON(@JSON)=1)
Print 'Valid JSON'
Store the JSON file content into a variable - Description: Store the JSON file content into a variable

Using OpenJSON to query JSON

The OpenJSON function will help us to parse the JSON data content and transform into a relational result set. Once the JSON content has been transformed by the OpenJSON, then the result set can be used for further processing.

The OpenJSON function accepts JSON as a parameter and it returns a dataset in two different formats:

  • Format #1: It returns the key:value pairs of the first level elements in the JSON.
  • Format #2: It returns all the elements with their indexes.

The OPENJSON function can be used in methods to query JSON dataset.

Method 1 - OPENJSON with the default output

The OpenJSON function can be called by default without passing additional arguments. In this case, it will return three columns. The first column represents the index of the element. The value of the element will be available as a second column. The third column indicates the data type of the value.

Method 2 - OPENJSON output with an explicit structure

The "With Clause" in the OpenJSON function will allow you to define the schema for the output result set. The “With” Clause is optional in OpenJSON function. In the with clause, you can define a list of output columns, their types, and the paths of the JSON source properties for each output value.

OpenJSON iterates through the JSON object and reads the value on the specified path for each defined column, and generate the result set.

For each element in the JSON, OpenJSON generates a new row in the output table. If there are two elements in the JSON, then they will be converted into two rows in the returned result set.

In addition, the OpenJSON uses the column name, type, json_path syntax to read the value and convert into the specified type.

Compatibility Level for OPENJSON

The OpenJSON function is available only with the database compatibility level 130. If the database compatibility level is less than 130, SQL Server can’t use the OpenJSON function.

In the below example, the OpenJSON function is simply parsing the JSON content and returning two rows.

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'E:\Temp\Data\Orders.JSON', SINGLE_CLOB) as j

Select @JSON

If (ISJSON(@JSON)=1)
Print 'Valid JSON'
Using OpenJSON for JSON file - Description: Using OpenJSON for JSON file

The above script can be expanded further by defining the column names with their datatype to generate a result set. The column definition will be mentioned in the WITH clause.

Now we are able to read and convert the JSON content into a meaningful result set. This result set can be inserted into a table or temporary table for further processing.

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'E:\Temp\Data\Orders.JSON', SINGLE_CLOB) as j

SELECT * FROM OPENJSON (@JSON) 
WITH(OrderID int,CustomerID int,OrderStatus char(1)) as Orders
Using With Clause in OpenJSON - Description: Using With Clause in OpenJSON

Now after evaluating this simple example, you should have basic understanding about loading and parsing the JSON file into SQL Server. Let’s challenge ourselves to learn more about JSON support in SQL Server 2016 by loading a common JSON file.

Loading UK Petition Details in JSON Format

In the UK, the parliament allows the general public to create an online petition and recommends everyone to vote for it. The details of the petition are available here to download in the form of a JSON file.

In this example, I have downloaded a sample petition JSON file and renamed as "AllState.JSON". Our aim is to load the content of the file and generate a result set for analysis and reporting.

Loading Petition JSON file

The below mentioned script will help you to load the downloaded JSON file into SQL Server.

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'E:\Temp\Data\AllState.JSON', SINGLE_CLOB) as j

SELECT * FROM OPENJSON (@JSON)
Using UK Petition JSON file for analysis - Description: Using UK Petition JSON file for analysis

It appears that the JSON file has two elements "links" and "data" at the high level. We need to query the "links" element to understand the actual data within.

This can be achieved by passing additional path parameter to the OpenJSON function. The path has to have the prefix "$." and the name of element you want to navigate.

The below script will help you to navigate the subset "Links" and will display the contents.

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'E:\Temp\Data\AllState.JSON', SINGLE_CLOB) as j

SELECT * FROM OPENJSON (@JSON,'$.links') 
Go
Querying the Links element - Description: Querying the Links element

Now we can navigate the subset "data" by passing "$.data" as a path to the OpenJSON function as mentioned in the below script.

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'E:\Temp\Data\AllState.JSON', SINGLE_CLOB) as j

SELECT * FROM OPENJSON (@JSON,'$.data')
Go
Querying data element - Description: Querying data element

It is observed that the subset "data" has all the details of every petition being made. However, the detail of a single petition is available as a nested element. So, to get the individual columns, we need to provide the column name and the datatype.

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'E:\Temp\Data\AllState.JSON', SINGLE_CLOB) as j

SELECT * FROM OPENJSON (@JSON,'$.data') 
With (type varchar(50),id int, Link varchar(4000),attributes varchar(4000)) as Dataset
Go
Querying the petition details with WITH clause - Description: Querying the petition details with WITH clause

From the above script, it is observed that the column details such as type and ID have been listed successfully. However, the script hasn’t provided the column details for link and attributes.

Let’s have a quick look at the JSON file content and understand how the elements "link" and attributes have been represented.

The below image represents the data and attributes subset of a single petition in the supplied JSON file.

Petition JSON file - Description: Petition JSON file

It is noted that the elements "links" and "attributes" are further nested in the JSON. Hence it is mandatory to provide the complete path in the WITH clause of the OpenJSON function.

The below script will allow us to generate a result set for all the listed columns in the JSON.

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'E:\Temp\Data\AllState.JSON', SINGLE_CLOB) as j

SELECT * FROM OPENJSON (@JSON,'$.data') 
With (type varchar(50),id int, 
Link varchar(4000) '$.links.self',
action varchar(4000) '$.attributes.action', 
background varchar(4000) '$.attributes.background', 
PetitionStatus varchar(50) '$.attributes.state', 
signature_count int '$.attributes.signature_count', 
CreatedDate varchar(50) '$.attributes.created_at'
) as Dataset
Go
UK petition JSON file has been transformed into relational format - Description: UK petition JSON file has been transformed into relational format

From the above image it is observed that the root level elements can be referenced with the prefix '$.'. For example, the columns, type and id are represented in the root and they can be queried directly or optionally they can be represented with the prefix '$.'. As the column "self" is a subset of link, it has to be represented as "$.links.self". In the same way the columns action, background, state, signature_count and created_at are the subset of attributes element. Hence all these columns have been represented with the prefix "$.attributes."

Summary

In this tip, we have learned about loading JSON files into SQL Server. In these examples, we have also learned about using OpenRowset and OpenJSON functions to manage JSON data effectively.

Next Steps
  • Read more about OPENJSON here
  • Learn JSON basics with this tip
  • Challenge your JSON knowledge with this tip


Last Updated: 2018-03-01


get scripts

next tip button



About the author
MSSQLTips author Nat Sundar Nat Sundar is working as an independent SQL BI consultant in the UK with a Bachelors Degree in Engineering.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Sunday, February 24, 2019 - 5:29:08 AM - Salam Back To Top

 In fact Nat, I discovered the issue of bad json formatted for the allstate.json. Whenever there is  adash " – ", sql parsing fails indicating bad json format. I removed all " – " dashes occurences, around 17 and data were parsed correctly in sql. Did you have the same issue?


Monday, August 06, 2018 - 4:30:38 PM - Mike Fisher Back To Top

 Ignore error. comment My database was in 2008 compatiblity mode, not 2016


Monday, August 06, 2018 - 3:59:05 PM - Mike Fisher Back To Top

 
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.


Friday, April 20, 2018 - 2:15:54 PM - vanvan Back To Top

 Thanks ... Excelent 😆

 


Monday, April 09, 2018 - 1:14:05 PM - jblankson Back To Top

 

 

Hi  can you kindly help me out?  I am trying to get data from a web url into ms-sql without having to use excel.  I can produce the data in json (displayed on the web browser screen or in csv file format that downloads a file to my pc.  Is there anyway I can get the data into an ms sql table without having to use excel?  I am running ms sql 2017 and the latest version of visual studio & SSMS. 


Tuesday, March 20, 2018 - 12:05:02 PM - Sam Back To Top

 Nat,

Thanks; very helpful.

 


Learn more about SQL Server tools