Whats New in SQL 2016 – Native JSON Function Support – Generating JSON documents using FOR JSON clause

One of the most sought after feature that got included in SQL 2016 is the native support for JSON data in Transact SQL. JSON has become kind of de-facto standard for data transfer in web applications these days. As such it makes it really awesome to know that you can generate JSON directly from the relational database using native SQL querying.
SQL 2016 has introduced  FOR JSON clause  which can be used to generate  JSON documents from relational database tables.
For people who have already worked with XML data it would be much easier as the syntax would be mostly similar to that of XML functions.
This blog article is the first in the series on the native JSON support in SQL 2016 and  explains on how JSON documents can be generated from relational tables using the FOR JSON construct.

Illustration

For the sake of this article consider the below table
declare @Items table
(
ItemID int,
ItemDesc varchar(100),
Unitprice decimal(10,2)
)

insert @Items
values(101,’Monitor CRT 18″‘,200),
(110,’Printer Catridge’,112),
(112,’Copier Ink’,20),
(123,’Wireless Mouse’,30)
There are two main options we can use with FOR JSON statement for constructing JSON documents.

1. FOR JSON PATH

The PATH option of the FOR XML construct provides flexibility to generate the JSON documents in the hierarchy we require.

Basic Syntax

Now to convert this relational data to JSON format we can use a simple query like below
SELECT *
FROM @Items
FOR JSON PATH

The result will be as shown below

[
  {
    “ItemID”: 101,
    “ItemDesc”: “Monitor CRT 18″”,
    “Unitprice”: 200
  },
  {
    “ItemID”: 110,
    “ItemDesc”: “Printer Catridge”,
    “Unitprice”: 112
  },
  {
    “ItemID”: 112,
    “ItemDesc”: “Copier Ink”,
    “Unitprice”: 20
  },
  {
    “ItemID”: 123,
    “ItemDesc”: “Wireless Mouse”,
    “Unitprice”: 30
  }
]

Adding a ROOT key

If you want to associate a root key name to it then you need to tweak the query as below
SELECT *
FROM @Items
FOR JSON PATH,ROOT(‘Items’)

And then output will become as below
{
  “Items”: [
    {
      “ItemID”: 101,
      “ItemDesc”: “Monitor CRT 18″”,
      “Unitprice”: 200
    },
    {
      “ItemID”: 110,
      “ItemDesc”: “Printer Catridge”,
      “Unitprice”: 112
    },
    {
      “ItemID”: 112,
      “ItemDesc”: “Copier Ink”,
      “Unitprice”: 20
    },
    {
      “ItemID”: 123,
      “ItemDesc”: “Wireless Mouse”,
      “Unitprice”: 30
    }
  ]
}

If you analyze the above output you can see that it returns an array of JSON objects within [] with key value as Items.
Now in case we are getting a single item from the query and want to return it as a single JSON document without the array wrapper we can use the optional clause WITHOUT_ARRAY_WRAPPER
ie like this
SELECT TOP 1 *
FROM @Items
FOR JSON PATH,WITHOUT_ARRAY_WRAPPER

And we will get output as below

{
  “ItemID”: 101,
  “ItemDesc”: “Monitor CRT 18″”,
  “Unitprice”: 200
}
Without the optional clause the output will be a single JSON object array as below
[{
  “ItemID”: 101,
  “ItemDesc”: “Monitor CRT 18″”,
  “Unitprice”: 200
}]

Handling of NULL values 

Now lets see how NULL values are handled by FOR JSON construct
Lets add two more rows to the table as per below script and check
declare @Items table
(
ItemID int,
ItemDesc varchar(100),
Unitprice decimal(10,2)
)

insert @Items
values(101,’Monitor CRT 18″‘,200),
(110,’Printer Catridge’,112),
(112,’Copier Ink’,20),
(123,’Wireless Mouse’,30),
(123,NULL,70),
(123,’LED Monitor’,NULL)

SELECT  *
FROM @Items
FOR JSON PATH,ROOT(‘Items’)

 And by default output will be below
{
  “Items”: [
    {
      “ItemID”: 101,
      “ItemDesc”: “Monitor CRT 18″”,
      “Unitprice”: 200
    },
    {
      “ItemID”: 110,
      “ItemDesc”: “Printer Catridge”,
      “Unitprice”: 112
    },
    {
      “ItemID”: 112,
      “ItemDesc”: “Copier Ink”,
      “Unitprice”: 20
    },
    {
      “ItemID”: 123,
      “ItemDesc”: “Wireless Mouse”,
      “Unitprice”: 30
    },
    {
      “ItemID”: 123,
      “Unitprice”: 70
    },
    {
      “ItemID”: 123,
      “ItemDesc”: “LED Monitor”
    }
  ]
}

As you see from above be default it ignores the elements having NULL values in each JSON document.
If you want to include NULL values also in the resultset you need to specify optional parameter INCLUDE_NULL_VALUES
SELECT  *
FROM @Items
FOR JSON PATH,ROOT(‘Items’),INCLUDE_NULL_VALUES
And the output will modify as below
{
  “Items”: [
    {
      “ItemID”: 101,
      “ItemDesc”: “Monitor CRT 18″”,
      “Unitprice”: 200
    },
    {
      “ItemID”: 110,
      “ItemDesc”: “Printer Catridge”,
      “Unitprice”: 112
    },
    {
      “ItemID”: 112,
      “ItemDesc”: “Copier Ink”,
      “Unitprice”: 20
    },
    {
      “ItemID”: 123,
      “ItemDesc”: “Wireless Mouse”,
      “Unitprice”: 30
    },
    {
      “ItemID”: 123,
      “ItemDesc”: null,
      “Unitprice”: 70
    },
    {
      “ItemID”: 123,
      “ItemDesc”: “LED Monitor”,
      “Unitprice”: null
    }
  ]
}

Which will also include the elements with NULL values as you see from above


Generating Nested JSON documents

Now lets consider the full set of tables as given below

declare @order table
(
OrderID int,
OrderDesc varchar(1000),
OrderDate datetime,
CustomerID int,
ShippedDate datetime,
WebOrder bit
)
declare @OrderItems table
(
OrderItemID int,
OrderID int,
ItemID int,
Qty int
)
declare @Items table
(
ItemID int,
ItemDesc varchar(100),
Unitprice decimal(10,2)
)
declare @Customers table
(
CustomerID int,
CustomerDesc varchar(100)
)
declare @SubItems table
(
SubItemID int,
ItemID int,
SubItemDesc varchar(100)
)
insert @Order
values (100001,’Order 1′,’20130212′,1004,’20130220′,0),
(100002,’Order 2′,’20150624′,1002,’20150630′,0),
(100005,’Order 5′,’20160517′,1012,’20160522′,1)
insert @OrderItems
values (10005,100001,123,10),
(10007,100001,112,8),
(10009,100002,110,12),
(10011,100005,101,28)
insert @Items
values(101,’Monitor CRT 18″‘,200),
(110,’Printer Catridge’,112),
(112,’Copier Ink’,20),
(123,’Wireless Mouse’,30)
insert @SubItems 
values (118,110,’SP1′),
(123,110,’SP3′),
(130,112,’SP5′),
(140,112,’SP12′),
(144,123,’SP17′)
insert @Customers
values (1002,’ABC inc’),
(1004,’KBC Associates’),
(1012,’AAM & Co’)
There are two ways to generate nested JSON structure from the above data
The straightforward way is to specify the nesting level  as an alias in the FOR JSON query.
The query will look like this


SELECT o.OrderDesc,o.OrderDate,o.ShippedDate,o.WebOrder,
oi.Qty AS ‘OrderItem.OrderQty’,
i.ItemDesc AS ‘OrderItem.Item.ItemDesc’,
i.Unitprice AS ‘OrderItem.Item.UnitPrice’,
s.SubItemDesc AS ‘OrderItem.Item.SubItem.SubItemDesc’,
c.CustomerDesc AS ‘Customer.CustomerDesc’
FROM @order o
INNER JOIN @OrderItems oi
ON oi.OrderID = o.OrderID
INNER JOIN @Items i
ON i.ItemID = oi.ItemID
INNER JOIN @Customers c
ON c.CustomerID = o.CustomerID
INNER JOIN @SubItems s
ON s.ItemID = i.ItemID
FOR JSON PATH,ROOT(‘Orders’)


And the resulting JSON will be as below

{
  “Orders”: [
    {
      “OrderDesc”: “Order 1”,
      “OrderDate”: “2013-02-12T00:00:00”,
      “ShippedDate”: “2013-02-20T00:00:00”,
      “WebOrder”: false,
      “OrderItem”: {
        “OrderQty”: 10,
        “Item”: {
          “ItemDesc”: “Wireless Mouse”,
          “UnitPrice”: 30,
          “SubItem”: {
            “SubItemDesc”: “SP17”
          }
        }
      },
      “Customer”: {
        “CustomerDesc”: “KBC Associates”
      }
    },
    {
      “OrderDesc”: “Order 1”,
      “OrderDate”: “2013-02-12T00:00:00”,
      “ShippedDate”: “2013-02-20T00:00:00”,
      “WebOrder”: false,
      “OrderItem”: {
        “OrderQty”: 8,
        “Item”: {
          “ItemDesc”: “Copier Ink”,
          “UnitPrice”: 20,
          “SubItem”: {
            “SubItemDesc”: “SP5”
          }
        }
      },
      “Customer”: {
        “CustomerDesc”: “KBC Associates”
      }
    },
    {
      “OrderDesc”: “Order 1”,
      “OrderDate”: “2013-02-12T00:00:00”,
      “ShippedDate”: “2013-02-20T00:00:00”,
      “WebOrder”: false,
      “OrderItem”: {
        “OrderQty”: 8,
        “Item”: {
          “ItemDesc”: “Copier Ink”,
          “UnitPrice”: 20,
          “SubItem”: {
            “SubItemDesc”: “SP12”
          }
        }
      },
      “Customer”: {
        “CustomerDesc”: “KBC Associates”
      }
    },
    {
      “OrderDesc”: “Order 2”,
      “OrderDate”: “2015-06-24T00:00:00”,
      “ShippedDate”: “2015-06-30T00:00:00”,
      “WebOrder”: false,
      “OrderItem”: {
        “OrderQty”: 12,
        “Item”: {
          “ItemDesc”: “Printer Catridge”,
          “UnitPrice”: 112,
          “SubItem”: {
            “SubItemDesc”: “SP1”
          }
        }
      },
      “Customer”: {
        “CustomerDesc”: “ABC inc”
      }
    },
    {
      “OrderDesc”: “Order 2”,
      “OrderDate”: “2015-06-24T00:00:00”,
      “ShippedDate”: “2015-06-30T00:00:00”,
      “WebOrder”: false,
      “OrderItem”: {
        “OrderQty”: 12,
        “Item”: {
          “ItemDesc”: “Printer Catridge”,
          “UnitPrice”: 112,
          “SubItem”: {
            “SubItemDesc”: “SP3”
          }
        }
      },
      “Customer”: {
        “CustomerDesc”: “ABC inc”
      }
    }
  ]
}

As you see from above result the JSON document is generated in nested order with each level coming under corresponding parent level as specified by the alias names used.
But one thing to note here is that the entire parent hierarchy gets repeated for each instance of the child document. If you want to avoid this then you need to return the child elements as an array within parent element.
Inorder to get it in array format the query will look like this
SELECT o.OrderDesc,o.OrderDate,o.ShippedDate,o.WebOrder,
(SELECT Qty AS OrderQty,
(SELECT ItemDesc,Unitprice,
(SELECT SubItemDesc
FROM @SubItems
WHERE ItemID = i.ItemID
FOR JSON PATH) AS SUbItem
FROM @Items i
WHERE ItemID = oi.ItemID
FOR JSON PATH) AS Item
FROM @OrderItems oi
WHERE oi.OrderID = o.OrderID
FOR JSON PATH) AS OrderItems,
(SELECT CustomerDesc
FROM @Customers
WHERE CustomerID = o.CustomerID
FOR JSON PATH)AS Customer
FROM @order o
FOR JSON PATH,ROOT(‘Orders’)

As you see the query itself will include nested subqueries in that case
Check the result and you will see child documents coming in array format without parent getting repeated as below
{
  “Orders”: [
    {
      “OrderDesc”: “Order 1”,
      “OrderDate”: “2013-02-12T00:00:00”,
      “ShippedDate”: “2013-02-20T00:00:00”,
      “WebOrder”: false,
      “OrderItems”: [
        {
          “OrderQty”: 10,
          “Item”: [
            {
              “ItemDesc”: “Wireless Mouse”,
              “Unitprice”: 30,
              “SUbItem”: [
                {
                  “SubItemDesc”: “SP17”
                }
              ]
            }
          ]
        },
        {
          “OrderQty”: 8,
          “Item”: [
            {
              “ItemDesc”: “Copier Ink”,
              “Unitprice”: 20,
              “SUbItem”: [
                {
                  “SubItemDesc”: “SP5”
                },
                {
                  “SubItemDesc”: “SP12”
                }
              ]
            }
          ]
        }
      ],
      “Customer”: [
        {
          “CustomerDesc”: “KBC Associates”
        }
      ]
    },
    {
      “OrderDesc”: “Order 2”,
      “OrderDate”: “2015-06-24T00:00:00”,
      “ShippedDate”: “2015-06-30T00:00:00”,
      “WebOrder”: false,
      “OrderItems”: [
        {
          “OrderQty”: 12,
          “Item”: [
            {
              “ItemDesc”: “Printer Catridge”,
              “Unitprice”: 112,
              “SUbItem”: [
                {
                  “SubItemDesc”: “SP1”
                },
                {
                  “SubItemDesc”: “SP3”
                }
              ]
            }
          ]
        }
      ],
      “Customer”: [
        {
          “CustomerDesc”: “ABC inc”
        }
      ]
    },
    {
      “OrderDesc”: “Order 5”,
      “OrderDate”: “2016-05-17T00:00:00”,
      “ShippedDate”: “2016-05-22T00:00:00”,
      “WebOrder”: true,
      “OrderItems”: [
        {
          “OrderQty”: 28,
          “Item”: [
            {
              “ItemDesc”: “Monitor CRT 18″”,
              “Unitprice”: 200
            }
          ]
        }
      ],
      “Customer”: [
        {
          “CustomerDesc”: “AAM & Co”
        }
      ]
    }
  ]
}

2. FOR JSON AUTO

As the name implies AUTO mode of the FOR JSON construct generates the JSON documents automatically based on the hierarchy we specify in the query
To see this in action lets use the same query we used above in AUTO mode
The query will look like this
SELECT o.OrderDesc,o.OrderDate,o.ShippedDate,o.WebOrder,
oi.Qty,
i.ItemDesc,
i.Unitprice,
s.SubItemDesc,
c.CustomerDesc
FROM @order o
INNER JOIN @OrderItems oi
ON oi.OrderID = o.OrderID
INNER JOIN @Items i
ON i.ItemID = oi.ItemID
INNER JOIN @Customers c
ON c.CustomerID = o.CustomerID
INNER JOIN @SubItems s
ON s.ItemID = i.ItemID
FOR JSON AUTO,ROOT(‘Orders’)
The resulting JSON would be as below
{
  “Orders”: [
    {
      “OrderDesc”: “Order 1”,
      “OrderDate”: “2013-02-12T00:00:00”,
      “ShippedDate”: “2013-02-20T00:00:00”,
      “WebOrder”: false,
      “oi”: [
        {
          “Qty”: 10,
          “i”: [
            {
              “ItemDesc”: “Wireless Mouse”,
              “Unitprice”: 30,
              “s”: [
                {
                  “SubItemDesc”: “SP17”,
                  “c”: [
                    {
                      “CustomerDesc”: “KBC Associates”
                    }
                  ]
                }
              ]
            }
          ]
        },
        {
          “Qty”: 8,
          “i”: [
            {
              “ItemDesc”: “Copier Ink”,
              “Unitprice”: 20,
              “s”: [
                {
                  “SubItemDesc”: “SP5”,
                  “c”: [
                    {
                      “CustomerDesc”: “KBC Associates”
                    }
                  ]
                },
                {
                  “SubItemDesc”: “SP12”,
                  “c”: [
                    {
                      “CustomerDesc”: “KBC Associates”
                    }
                  ]
                }
              ]
            }
          ]
        }
      ]
    },
    {
      “OrderDesc”: “Order 2”,
      “OrderDate”: “2015-06-24T00:00:00”,
      “ShippedDate”: “2015-06-30T00:00:00”,
      “WebOrder”: false,
      “oi”: [
        {
          “Qty”: 12,
          “i”: [
            {
              “ItemDesc”: “Printer Catridge”,
              “Unitprice”: 112,
              “s”: [
                {
                  “SubItemDesc”: “SP1”,
                  “c”: [
                    {
                      “CustomerDesc”: “ABC inc”
                    }
                  ]
                },
                {
                  “SubItemDesc”: “SP3”,
                  “c”: [
                    {
                      “CustomerDesc”: “ABC inc”
                    }
                  ]
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

As you see from the above the JSON structure is automatically generated based on the query sequence. We dont have much control over JSON structure generated.
Now lets see how AUTO mode behaves for the second query
{
  “Orders”: [
    {
      “OrderDesc”: “Order 1”,
      “OrderDate”: “2013-02-12T00:00:00”,
      “ShippedDate”: “2013-02-20T00:00:00”,
      “WebOrder”: false,
      “OrderItems”: [
        {
          “OrderQty”: 10,
          “Item”: [
            {
              “ItemDesc”: “Wireless Mouse”,
              “Unitprice”: 30,
              “SUbItem”: [
                {
                  “SubItemDesc”: “SP17”
                }
              ]
            }
          ]
        },
        {
          “OrderQty”: 8,
          “Item”: [
            {
              “ItemDesc”: “Copier Ink”,
              “Unitprice”: 20,
              “SUbItem”: [
                {
                  “SubItemDesc”: “SP5”
                },
                {
                  “SubItemDesc”: “SP12”
                }
              ]
            }
          ]
        }
      ],
      “Customer”: [
        {
          “CustomerDesc”: “KBC Associates”
        }
      ]
    },
    {
      “OrderDesc”: “Order 2”,
      “OrderDate”: “2015-06-24T00:00:00”,
      “ShippedDate”: “2015-06-30T00:00:00”,
      “WebOrder”: false,
      “OrderItems”: [
        {
          “OrderQty”: 12,
          “Item”: [
            {
              “ItemDesc”: “Printer Catridge”,
              “Unitprice”: 112,
              “SUbItem”: [
                {
                  “SubItemDesc”: “SP1”
                },
                {
                  “SubItemDesc”: “SP3”
                }
              ]
            }
          ]
        }
      ],
      “Customer”: [
        {
          “CustomerDesc”: “ABC inc”
        }
      ]
    },
    {
      “OrderDesc”: “Order 5”,
      “OrderDate”: “2016-05-17T00:00:00”,
      “ShippedDate”: “2016-05-22T00:00:00”,
      “WebOrder”: true,
      “OrderItems”: [
        {
          “OrderQty”: 28,
          “Item”: [
            {
              “ItemDesc”: “Monitor CRT 18″”,
              “Unitprice”: 200
            }
          ]
        }
      ],
      “Customer”: [
        {
          “CustomerDesc”: “AAM & Co”
        }
      ]
    }
  ]
}

Conclusion

As you see from all the above illustrations FOR JSON provides a convenient way of generating JSON documents from relational table data.
Based on flexibility you need, you can use PATH or the AUTO mode along with FOR JSON for generating the JSON documents in the required format.
See you all with the next article in the series which will explain on how to extract data from JSON documents to relational tables. Till then happy coding with the sample scripts above. Feel free to revert for any clarifications.

Published by dataplatformcentral