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