Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - The Cloud won't fix that (click for more info)
 

Transforming JSON Data to Relational Data in SQL Server 2016


By:   |   Read Comments (2)   |   Related Tips: More > SQL Server 2016

Problem

How do I transform JSON data to relational data in SQL Server 2016?

Solution

Let's assume we have a table with a column containing data in JSON format. In this tip, I will walkthrough several examples to transform JSON format to relational format.

The below examples have been generated based on the SalesOrderHeader and SalesOrderDetail table in the AdventureWorks database. I recommend you to install the AdventureWorks database before proceeding with these examples.

Creating a table with JSON column

In the below example, I am creating a table "OrderHeaderJSON". This table has two columns SalesOrderNumber and JSONValue. The SalesOrderNumber is a unique number to identify an order. The JSONValue column has other order details such as CustomerID, OrderDate, TotalDue, ShipMethodID, TerritoryID, SalesPersonID in JSON format.

The below script will create the table and load the data.

--#1 prepare data with JSON values
if object_id('OrderHeaderJSON') is not null
Drop table OrderHeaderJSON
 
SELECT SalesOrderNumber,
(SELECT CustomerID,OrderDate,TotalDue,ShipMethodID,TerritoryID,SalesPersonID FOR JSON PATH,INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER) JSONValue
into OrderHeaderJSON
FROM [Sales].[SalesOrderHeader]
 
Select SalesOrderNumber,JSONValue from OrderHeaderJSON
			
Data Preparation - Description: Data Preparation

In order to transform the JSON data into relational, we have to use the JSON_VALUE scalar function. This function extracts scalar value from a JSON string.

The below syntax can be used to extract the value from a JSON string.

JSON_VALUE (expression, path) 

The JSON_VALUE function will return an error, if the supplied JSON string is not a valid JSON. The JSON_VALUE function doesn’t support, if the return value is greater than 4000 characters. In addition, it will return an error if the return value is not a scalar value.

The JSON_VALUE function accepts two arguments the JSON expression as the first parameter and the JSON path as the second parameter. The JSON path starts with '$' and followed by the key. The below query will help transform the JSON data into relational format.

--#2 JSON Query to transform JSON into columns
select SalesOrderNumber,
JSON_Value(JSONValue,'$.CustomerID') as CustomerID ,
JSON_Value(JSONValue,'$.OrderDate') as OrderDate ,
JSON_Value(JSONValue,'$.TotalDue') as TotalDue ,
JSON_Value(JSONValue,'$.ShipMethodID') as ShipMethodID ,
JSON_Value(JSONValue,'$.TerritoryID') as TerritoryID ,
JSON_Value(JSONValue,'$.SalesPersonID') as SalesPersonID 
from OrderHeaderJSON
			
SalesOrderHeader Transformed into relational data - Description: SalesOrderHeader Transformed into relational data

In addition to using the JSON_VALUE in the SELECT clause, it can also be used in the WHERE clause to filter rows. The below script shows an example to retrieve records whose order date is greater than a specific date.

--#3 JSON Query to transform JSON into columns and apply filter
select top 100 SalesOrderNumber,
JSON_Value(JSONValue,'$.CustomerID') as CustomerID ,
JSON_Value(JSONValue,'$.OrderDate') as OrderDate ,
JSON_Value(JSONValue,'$.TotalDue') as TotalDue ,
JSON_Value(JSONValue,'$.ShipMethodID') as ShipMethodID ,
JSON_Value(JSONValue,'$.TerritoryID') as TerritoryID ,
JSON_Value(JSONValue,'$.SalesPersonID') as SalesPersonID 
from OrderHeaderJSON
Where JSON_Value(JSONValue,'$.OrderDate') >= '2007-07-18'
			
Applying filter condition using JSON_Value - Description: Applying filter condition using JSON_Value

In the above example the sample data has been derived from the SalesOrderHeader table, where the attributes will have a one to one relationship between the SalesOrderID and the other Order ID attributes. Hence, we are able to use the JSON_VALUE function on the "Select" and "Where" clause.

Now let’s have a look at little more complex example. In this example I will introduce complexity, so that we can learn different concepts in JSON.

Let’s assume we have been supplied a table with OrderHeader and OrderDetails. The relationship between SalesOrderHeader and SalesOrderDetails is one to many. Hence for a single sales order, there can be many order details. The OrderHeaderWithDetails table will have single record for each sales order. The order details are available in the JSON format as a column.

The below script will create the table and load the sales order header and details into the table. The final SQL script will retrieve the order details for a specific order.

The order header details are available as columns (SalesOrderID, OrderDate, SalesOrderNumber and CustomerID). In addition, this order has three order details which are available as a JSON data in the column "SalesOrderDetails." So, it is evident that the relationship between the SalesOrderHeader and the SalesOrderDetails are one to many.

--#4
if object_id('OrderHeaderWithDetails') is not null
Drop table OrderHeaderWithDetails
 
Select top 100 SalesOrderID,OrderDate,SalesOrderNumber,CustomerID ,
(
Select SalesOrderDetailID,ProductID ,LineTotal from [Sales].[SalesOrderDetail] SD
Where SH.SalesOrderID = SD.SalesOrderID
For JSON Path
) SalesOrderDetails
Into OrderHeaderWithDetails
from [Sales].[SalesOrderHeader] SH 
Select SalesOrderID,OrderDate,SalesOrderNumber,CustomerID,SalesOrderDetails from OrderHeaderWithDetails
Where SalesOrderID=51189
			
SalesOrder Header and Detail transformation to JSON - Description: SalesOrder Header and Detail transformation to JSON

Now let’s use the JSON_VALUE scalar value function to retrieve the SalesOrderDetailID as mentioned in the SQL script below.

 
Select SalesOrderID,OrderDate,SalesOrderNumber,CustomerID,SalesOrderDetails,
JSON_VALUE(SalesOrderDetails,'$.SalesOrderDetailID')
from OrderHeaderWithDetails
Where SalesOrderID=51189
			
Unable to transform using JSON_Value for nested JSON - Description: Unable to transform using JSON_Value for nested JSON

Because the "SalesOrderDetails" details are listed as a JSON array, the JSON_VALUE is returning a NULL value for SalesOrderDetailID.

As the JSON data is available in the form of an array, we need to provide the index value to access the key/value. The below script will help us retrieve the first SalesOrderDetailsID.

Select SalesOrderID,OrderDate,SalesOrderNumber,CustomerID,SalesOrderDetails,
JSON_VALUE(SalesOrderDetails,'$[0].SalesOrderDetailID') SalesOrderDetailID
from OrderHeaderWithDetails
Where SalesOrderID=51189
			
Retrieving first item details using array index - Description: Retrieving first item details using array index

To retrieve all the salesOrderDetails, we need to explicitly mention the position of the SalesOrderDetailID as shown in the below script.

The below script will extract three SalesOrderDetails for the supplied SalesOrderID.

Select SalesOrderID,OrderDate,SalesOrderNumber,CustomerID,SalesOrderDetails,
JSON_VALUE(SalesOrderDetails,'$[0].SalesOrderDetailID') SalesOrderDetailID_1,
JSON_VALUE(SalesOrderDetails,'$[1].SalesOrderDetailID') SalesOrderDetailID_2,
JSON_VALUE(SalesOrderDetails,'$[2].SalesOrderDetailID') SalesOrderDetailID_3
from OrderHeaderWithDetails
Where SalesOrderID=51189
			
Retrieving many item details using array index - Description: Retrieving many item details using array index

Although we are able to extract the sales order details, this is not a scalable solution. As the number of SalesOrder could increase or decrease based on the order.

Hence, we must come up with the alternative solution to dynamically generate the SalesOrder details as rows rather than columns.

In the below example, I have come up with a solution using the OPENJSON function. OPENJSON is a table-valued function that reads a JSON string and returns data in the form of rows and columns. We can explicitly specify the columns in the row set and the JSON property paths to load the columns. As OPENJSON returns a set of rows, we can use OPENJSON in the FROM clause with CROSS APPLY.

Select SalesOrderID,OrderDate,SalesOrderNumber,CustomerID 
,JSON_VALUE(S.value,'$.SalesOrderDetailID') as SalesOrderDetailID
,JSON_VALUE(S.value,'$.ProductID') as ProductID
,JSON_VALUE(S.value,'$.LineTotal') as LineTotal
from OrderHeaderWithDetails
CROSS APPLY OPENJSON(OrderHeaderWithDetails.SalesOrderDetails) S
Where SalesOrderID=51189
			
Extracting sales order details dynamically using OPENJSON and JSON_Value - Description: Extracting sales order details dynamically using OPENJSON and JSON_Value

Summary

We have learned several tips and tricks to extract and transform JSON data to relational format using SQL Server JSON native functions such as JSON_VALUE and OPENJSON.

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


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Parvathy Natraj Parvathy Natraj is an independent Agile Data Analyst in the UK with Master Degree in Business Administration. She is an ISEB Certified tester and Microsoft Certified System Administrator.

View all my tips
Related Resources





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.



    



Monday, August 13, 2018 - 5:27:48 AM - Jose Luis Castello Back To Top

Hello,

I tried the examples and they do not work, I did it in sql express 2017 maybe it is not supported. I would also like to know what I should modify in the query if my json has ROOT EJ:

{"PERSON":[{"BusinessEntityID":1,"FirstName":"Ken","LastName":"Sánchez","Title":null}]}

Thank you


Friday, March 16, 2018 - 6:05:18 AM - Uli Bethke Back To Top

 This is a great post that shows that it is valuable to convert JSON to a relational format in SQL Server. I have written up a blog post that outlines the arguments to convert JSON to a relational format https://sonra.io/2018/02/19/json-etl-noetl-big-data-question/

 


Learn more about SQL Server tools