Advanced Techniques to Transform Relational Data to JSON in SQL Server 2016


By:   |   Updated: 2018-02-23   |   Comments (2)   |   Related: More > SQL Server 2016

Problem

I have learned about basic JSON formatting in SQL server using in these previous tips #1 and #2. However, what are other ways JSON can be formatted in SQL Server? Also, how do I generate a nested JSON format in SQL Server?

Solution

In this tip, I will walkthrough several examples to transform relational data to JSON. Also, I have demonstrated a few tips and tricks to overcome the common challenges.

The usage of JSON has increased considerably, as many organizations tend to use JSON as a common format to exchange data. So, it is inevitable for the data developers to leverage the JSON support for SQL Server 2016 to provide the data in JSON format. In this tip, we will explore the "For JSON" clause to export data in JSON format.

As SQL Server Management Studio doesn’t format JSON properly, I will be using the JSON viewer and JSON format plugins for Notepad++ in this tip to demonstrate the data. For each example, the first image will be from SSMS and the second will be formatted JSON using Notepad++.

Let’s start with a simple example to transform data to JSON. The below examples will be using the [Sales].[SalesOrderHeader] and [Sales].[SalesOrderDetail] tables in the AdventureWorks database. It is assumed that you have installed SQL Server 2016 with the AdventureWorks database.

The SQL Server clause "FOR JSON" will help us to format the relational data into JSON format. The "For JSON" clause has two options "AUTO" and "PATH". Let’s start using the "AUTO" option first.

Using the AUTO option for JSON Formatting

In the "AUTO" option, the structure of the JSON output will be determined by a combination of the order of columns in the SELECT clause and the tables that are referenced in the SELECT clause.

Example 1: Simple transformation to JSON

The below example will help you to transform the SalesOrderHeader rows into JSON.

SELECT TOP 10 SalesOrderID ,OrderDate ,SalesOrderNumber ,CustomerID 
FROM [Sales].[SalesOrderHeader]
FOR JSON AUTO			
Simple JSON Example - Description: Simple JSON Example
JSON Output - Description: JSON Output

In the above result, a single array has been returned with each row as objects. An array has been represented by square brackets ("[" and "]") and a JSON object has been represented using curly braces "{" and "}".

Now Let’s enhance the SQL query to include the order details like ProductID and Line total. As the order details are available in a different table "[Sales].[SalesOrderDetail]", we need to JOIN the SalesOrderHeader table with the SalesOrderDetail table based on the SalesOrderID column.

SELECT TOP 100 SD.SalesOrderID,OrderDate,SalesOrderNumber,CustomerID ,
   SalesOrderDetailID ,ProductID ,LineTotal 
FROM [Sales].[SalesOrderHeader] SH 
   INNER JOIN [Sales].[SalesOrderDetail] SD on SH.SalesOrderID = SD.SalesOrderID
FOR JSON AUTO			
JSON Output with SQL JOIN - Description: JSON Output with SQL JOIN
JSON Output - Nested - Description: JSON Output - Nested

From the above results it appears that a child array with a label "SH" appears in the JSON. However, we are expecting to have an object for SalesOrderHeader and then a SalesOrderDetail object as a child of order header. As the first column is referencing the SalesOrderDetail table, the JSON output was other way around.

Now let’s update the SQL query to refer to the SalesOrderID column from the SalesOrderHeader table and see the results.

However, if we change the column order of the SELECT statement such that if it begins with a column from the SalesOrderHeader table, we get a different output. In the resultant output the child array is now based on the SalesOrderHeader table.

SELECT TOP 100 SH.SalesOrderID,OrderDate,SalesOrderNumber,CustomerID ,
   SalesOrderDetailID ,ProductID ,LineTotal 
FROM [Sales].[SalesOrderHeader] SH 
   INNER JOIN [Sales].[SalesOrderDetail] SD on SH.SalesOrderID = SD.SalesOrderID
FOR JSON AUTO
			
Nested JSON Output - SQL Query - Description: Nested JSON Output - SQL Query
Nested JSON Output - View - Description: Nested JSON Output - View

From the above result set, it is evident that the column order is vital to get a proper hierarchy in the JSON format.

From the above examples, it is evident that it is easy to use the "FOR JSON AUTO" clause to generate JSON. However most of the time it returns an inconsistent output and the JSON format is based on the order of the columns in the SELECT statement. Hence, it is recommended to use the "FOR JSON PATH" clause to generate a consistent output.

Using the PATH option for JSON Formatting

The "FOR JSON PATH" clause has two options. It can be used with a dot syntax or without a dot syntax. If the "PATH" has been used without a dot syntax, then you will get the results based on the column ordering in the SELECT statement. This is as same as the "AUTO" mode.

Let’s apply the "FOR JSON PATH" clause without the dot syntax to the same SQL query and evaluate the results.

SELECT TOP 100 SD.SalesOrderID,SalesOrderDetailID,ProductID ,LineTotal,OrderDate,SalesOrderNumber,CustomerID 
FROM [Sales].[SalesOrderHeader] SH 
   INNER JOIN [Sales].[SalesOrderDetail] SD on SH.SalesOrderID = SD.SalesOrderID
FOR JSON PATH
			
Nested JSON Output -  Using JSON Path - Description: Nested JSON Output -  Using JSON Path
Nested JSON Output -  View - Description: Nested JSON Output -  View

The above SQL script can be modified to generate a nested array for the SalesOrderDetail. The below script will generate order details for each order in the JSON format.

Using the below approach, we can generate a relational data with JSON as an additional column. In the below result set the first four columns are derived from the SalesOrderHeader table and the last JSON column contains the order details.

SELECT TOP 100SalesOrderID,OrderDate,SalesOrderNumber,CustomerID ,   (
      SELECT SalesOrderDetailID,ProductID ,LineTotal 
      FROM [Sales].[SalesOrderDetail] SD
      WHERE SH.SalesOrderID = SD.SalesOrderID
      FOR JSON PATH
   ) SalesOrderDetails
FROM [Sales].[SalesOrderHeader] SH
			
Relational with JSON Output - Description: Relational with JSON Output

After adding the "FOR JSON PATH" clause to the outer SQL, we have fully formed JSON with SalesOrderHeader and SalesOrderDetail tables.

Based on the above image, it is confirmed that the PATH mode doesn’t create the child array for the order details. All the columns from the SalesOrderHeader and SalesOrderDetail are in the same root level.

SELECT TOP 100 SalesOrderID,OrderDate,SalesOrderNumber,CustomerID ,   (
      SELECT SalesOrderDetailID,ProductID ,LineTotal 
      FROM [Sales].[SalesOrderDetail] SD
      WHERE SH.SalesOrderID = SD.SalesOrderID
      FOR JSON PATH
   ) SalesOrderDetails
FROM [Sales].[SalesOrderHeader] SH 
FOR JSON PATH
			
Using JSON Path for Nested Format - Description: Using JSON Path for Nested Format
Using JSON Path - Nested Output - Description: Using JSON Path - Nested Output

Using the dot syntax with FOR JSON PATH

We need to use the dot syntax with the "FOR JSON PATH" clause to create a child array for order details. The label before the dot represents the name of the object – in this case we have two objects named Sales and Cust.

Let’s apply the dot syntax to a simple SQL query to understand the format. The below query will generate the output from the SalesOrderHeader table.

SELECT TOP 2 SalesOrderID 'Order.SalesOrderID',OrderDate 'Order.OrderDate',
   SalesOrderNumber 'Order.SalesOrderNumber',CustomerID 'Order.CustomerID' 
FROM [Sales].[SalesOrderHeader]
FOR JSON PATH
			
Using dot Syntax for JSON Path - Description: Using dot Syntax for JSON Path
Using dot Syntax for JSON - Output - Description: Using dot Syntax for JSON - Output

Nested JSON Array

The same dot syntax can be applied to a nested sub query to generate a nested JSON array.

SELECT TOP 2 SalesOrderID 'Order.SalesOrderID',OrderDate 'Order.OrderDate',SalesOrderNumber 'Order.SalesOrderNumber',CustomerID 'Order.CustomerID',
   (
      SELECT SalesOrderDetailID 'OrderDetails.SalesOrderDetailID',ProductID 'OrderDetails.ProductID' ,
         LineTotal 'OrderDetails.LineTotal' 
      FROM [Sales].[SalesOrderDetail] SD
      WHERE SH.SalesOrderID = SD.SalesOrderID
      FOR JSON PATH
   ) SalesOrderDetails
FROM [Sales].[SalesOrderHeader] SH 
FOR JSON PATH
			
Using dot Syntax for JSON - Nested  - Description: Using dot Syntax for JSON - Nested
Using dot Syntax for JSON - Nested Output - Description: Using dot Syntax for JSON - Nested Output

The dot syntax can be applied to SQL queries with JOIN as well. However, in this case the order header details will repeat for each order detail.

SELECT TOP 2 SH.SalesOrderID 'SalesHeader.SalesOrderID' ,SH.OrderDate 'SalesHeader.OrderDate',   SH.SalesOrderNumber 'SalesHeader.SalesOrderNumber',SH.CustomerID 'SalesHeader.CustomerID,SD',
   SalesOrderDetailID 'SalesOrderDetail.SalesOrderDetailID',SD.ProductID 'SalesOrderDetail.ProductID',
   SD.LineTotal 'SalesOrderDetail.LineTotal'
FROM [Sales].[SalesOrderHeader] SH 
   INNER JOIN [Sales].[SalesOrderDetail] SD on SH.SalesOrderID = SD.SalesOrderID
FOR JSON PATH
			
Using dot Syntax for JOIN - Nested  - Description: Using dot Syntax for JOIN - Nested
Using dot Syntax for JOIN - Nested Output - Description: Using dot Syntax for JOIN - Nested Output

The JSON array indicators can be removed using the option "Without_Array_Wrapper". Let’s enhance the SQL query to remove the array indicator for both the nested SQL query and for the outer SQL.

SELECT TOP 2 SH.SalesOrderID 'SalesHeader.SalesOrderID' ,SH.OrderDate 'SalesHeader.OrderDate',SH.SalesOrderNumber 'SalesHeader.SalesOrderNumber',
   SH.CustomerID 'SalesHeader.CustomerID,SD',
   ( SELECT
        SalesOrderDetailID 'OrderDetail.SalesOrderDetailID',SD.ProductID 'OrderDetail.ProductID',SD.LineTotal 'OrderDetail.LineTotal' 
     FROM [Sales].[SalesOrderDetail] SD
     WHERE SH.SalesOrderID = SD.SalesOrderID
     FOR JSON PATH, Without_Array_Wrapper 
   ) SDetail
FROM [Sales].[SalesOrderHeader] SH 
FOR JSON PATH, Without_Array_Wrapper
			
Resultant JSON Output - Description: Resultant JSON Output

However, the result is not a valid JSON format. This is because the text returned by the inner "FOR JSON" query is escaped as plain text. You may have observed that this happens only if the clause WITHOUT_ARRAY_WRAPPER is in the inner query.

The WITHOUT_ARRAY_WRAPPER option in the inner "FOR JSON" may not generate a valid JSON all the times. Therefore, the outer "FOR JSON" assumes that the resultant inner query is plain text and escapes the string.

As we are confident that the inner JSON output is valid, we can wrap it with the JSON_QUERY function to promote it to properly formatted JSON.

SELECT TOP 2 SH.SalesOrderID 'SalesHeader.SalesOrderID' ,SH.OrderDate 'SalesHeader.OrderDate',SH.SalesOrderNumber 'SalesHeader.SalesOrderNumber',
   SH.CustomerID 'SalesHeader.CustomerID',
   JSON_QUERY(( SELECT
                   SalesOrderDetailID 'OrderDetail.SalesOrderDetailID',SD.ProductID 'OrderDetail.ProductID',SD.LineTotal 'OrderDetail.LineTotal' 
                FROM [Sales].[SalesOrderDetail] SD
                WHERE SH.SalesOrderID = SD.SalesOrderID
                FOR JSON PATH , Without_Array_Wrapper 
              )) SalesOrderDetails
FROM [Sales].[SalesOrderHeader] SH 
FOR JSON PATH
			
Fully formatted JSON - Description: Fully formatted JSON
JSON Formatted Output - Description: JSON Formatted Output

Summary

We have learned several tips and tricks to extract and transform relational data to JSON format using SQL Server JSON native support.

Next Steps
  • Learn JSON basics with this tip
  • Challenge your JSON knowledge with this tip


Last Updated: 2018-02-23


get scripts

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





Comments For This Article




Tuesday, May 26, 2020 - 9:07:27 AM - Saravanan Back To Top

hi i need out put like this

{
"Msg":"String content",
"Code":2147483647,
"Data":{
"order_list":[{
 
"order_id":"String content",
"order_status":"String content",
"order_items":[{
"images":[{
"aspect_ratio":"String content",
"url":"String content"
}],
"price":"String content",
"product_id":9223372036854775807,
"quantity":2147483647,
"sku":"String content",
"title":"String content",
"variant_id":9223372036854775807,
"weight":1.26743237E+15,
"weight_unit":"String content"
}],
 
"shipping_address":{
"address":"String content",
"address_type":"String content",
"area":"String content",
"email":"String content",
"name":"String content",
"phone":"String content",
"pincode":2147483647
},
 
"billing_address":{
"address":"String content",
"address_type":"String content",
"area":"String content",
"email":"String content",
"name":"String content",
"phone":"String content",
"pincode":2147483647
},
"order_create_time":"String content",
"sub_category_level2_id":9223372036854775807,
"sub_category_level2_type":"String content",
 
"commission_rate":1.26743233E+15,
"sub_commission":1.26743233E+15
}],
"current_page":2147483647,
"page_size":2147483647,
"user_id":"String content",
"orders_count":2147483647,
"start_date_time":"String content",
"end_date_time":"String content",
"order_amount":1.26743233E+15,
"order_commission":1.26743233E+15,
"store_name":"String content",
"store_id":"String content"
}
}

My query is here please suggest me 

SELECT 
    OrderId AS 'order_list.order_id',
CASE WHEN OrderStatus=0 THEN 'Placed' ELSE 'Initiated' END AS 'order_list.order_status',
 
   JSON_QUERY(( SELECT
                   REPLACE(image,'"','') 'images',price 'price',Productid 'product_id',
quantity 'quantity',sku 'sku',title 'title',Variantid  'variant_id',weight 'weight',weight_unit 'weight_unit',
ShippingAddress AS 'shipping_address.address',
ShippingType AS 'shipping_address.address_type',
ShippingArea AS 'shipping_address.area',
ShippingEmail AS 'shipping_address.email',
ShippingName AS 'shipping_address.name',
ShippingPhone AS 'shipping_address.phone',
ShippingPincode AS 'shipping_address.pincode',
BillingAddress AS 'billing_address.address',
BillingType AS 'billing_address.address_type', 
BillingArea AS 'billing_address.area',
BillingEmail AS 'billing_address.email',
BillingName AS 'billing_address.name',
BillingPhone AS 'billing_address.phone',
BillingPincode AS 'billing_address.pincode',
 
order_create_time AS 'order_create_time',
sub_category_level2_id AS 'sub_category_level2_id',
sub_category_level2_type AS 'sub_category_level2_type',
commission_rate AS 'commission_rate',
sub_commission AS 'sub_commission'
 
                FROM  MAS_Variants SD
                WHERE SH.Productid = SD.Productid
AND Status=1 
AND SD.Productid=560055423572410368
                FOR JSON PATH
              )) order_items FROM MAS_Order SH FOR JSON PATH

Wednesday, August 01, 2018 - 10:40:35 AM - Dan Back To Top

 cool article.  what are you using to view the object tree on the left side of the returned json?



download


Recommended Reading

New Drop If Exists Syntax in SQL Server 2016

New CREATE OR ALTER statement in SQL Server 2016 SP1

Automatic Updates for SQL Server Management Studio

SQL Server 2016 Always Encrypted

Download and Install SQL Server 2016 Sample Databases WideWorldImporters and WideWorldImportersDW





get free sql tips
agree to terms


Learn more about SQL Server tools