Advanced Techniques to Transform Relational Data to JSON in 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

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

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

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

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

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 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

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

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

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

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

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