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
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
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'
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
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
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
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
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
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.
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. She has considerable years of experience with the UK Health Services Industry.
She has worked for various industries in both large and small environments all with different needs. Amidst working in a wide range of technologies, one thing which has not changed is her love for SQL Server.
