TSQL Tuesday #79 -JSON Support in SQL 2016

June 14, 2016

I thank Michael J Swart (b|t) for hosting TSQL Tuesday #79. Topic for this month is to write about SQL Server 2016. This is my first time joining in TSQL Tuesday and I am excited about it.

TSQL2SDAY-300x300

In my workplace, we use MongoDB besides SQL Server. MongoDB saves documents in the binary form of  JSON called BSON. Our developers are excited to see JSON support in SQL 2o16.  I will give some simple examples of JSON support in SQL Server 2016.  Readers need to be aware that SQL Server is not supporting native JSON type.  In SQL Server 2016 JSON will be represented as NVARCHAR type.  Why SQL Server 2016 is not supporting native JSON type is described here by Jovan Popovic.

As per wikipedia JSON  is an open-standard format that uses human-readable text to transmit data objects consisting of attribute–value pairs. It is the most common data format used for asynchronous browser/server communication (AJAJ), largely replacing XML which is used by AJAX.

Here is an example of a business card in JSON format so readers have some idea how JSON looks like.

{
"name"     : "SQL Server 2016",
"title"    : "Enterprise Account Manager",
"company"  : "ABC Company",
"phone"    : "781-000-9999"
"location" : "Boston, MA"
}

How do I extract data from SQL Server tables in JSON format? Using new sample database WideWorldImproters I will extract data in JSON format.  It is the same as any other select statement with “FOR JSON AUTO”  at the end.  When you use AUTO database engine automatically determines JSON output based on the structure of the select statement.

USE WideWorldImporters;
GO
SELECT OrderID ,
 CustomerID ,
 SalespersonPersonID ,
 PickedByPersonID ,
 ContactPersonID ,
 BackorderOrderID ,
 OrderDate
FROM Sales.Orders
WHERE CustomerID = 1000
 AND OrderID = 2899
FOR JSON AUTO;
[{"OrderID":2899,"CustomerID":1000,"SalespersonPersonID":15,"PickedByPersonID":2,
"ContactPersonID":3200,"OrderDate":"2013-03-02"}]

Using a JSON formatter result will look like this.

[
   {
      "OrderID": 2899,
      "CustomerID": 1000,
      "SalespersonPersonID": 15,
      "PickedByPersonID": 2,
      "ContactPersonID": 3200,
      "OrderDate": "2013-03-02"
   }
]

Joining Orders table with OrderLines which will produce 3 records to see how JSON is formatted for 3 records.

USE WideWorldImporters;
GO
SELECT o.OrderID ,
 CustomerID ,
 SalespersonPersonID ,
 PickedByPersonID ,
 ContactPersonID ,
 BackorderOrderID ,
 ol.Description OrderDate
FROM Sales.Orders AS o
 JOIN Sales.OrderLines AS ol ON o.OrderID = ol.OrderID
WHERE CustomerID = 1000
 AND o.OrderID = 2899
FOR JSON AUTO;

I am putting the result set in regular format also so it is easy to compare between grid and JSON format.  Notice how all 3 description values become member of ol (alias for OrderLines table) array .

JsonResult01
[
   {
      "OrderID": 2899,
      "CustomerID": 1000,
      "SalespersonPersonID": 15,
      "PickedByPersonID": 2,
      "ContactPersonID": 3200,
      "ol": [
         {
            "Description": "\"The Gu\" red shirt XML tag t-shirt (White) XXL"
         },
         {
            "Description": "Ride on toy sedan car (Blue) 1/12 scale"
         },
         {
            "Description": "Shipping carton (Brown) 356x356x279mm"
         }
      ]
   }
]

Even if we change the order of select statement and use JSON AUTO output will be same.

USE [WideWorldImporters];
GO
SELECT o.OrderID ,
 CustomerID ,
 SalespersonPersonID ,
 PickedByPersonID ,
 ContactPersonID ,
 ol.Description,
 BackorderOrderID
 FROM Sales.Orders AS o
 JOIN Sales.OrderLines AS ol ON o.OrderID = ol.OrderID
WHERE CustomerID = 1000
 AND o.OrderID = 2899
FOR JSON AUTO;
[
   {
      "OrderID": 2899,
      "CustomerID": 1000,
      "SalespersonPersonID": 15,
      "PickedByPersonID": 2,
      "ContactPersonID": 3200,
      "ol": [
         {
            "Description": "\"The Gu\" red shirt XML tag t-shirt (White) XXL"
         },
         {
            "Description": "Ride on toy sedan car (Blue) 1/12 scale"
         },
         {
            "Description": "Shipping carton (Brown) 356x356x279mm"
         }
      ]
   }
]

Lets use the other option PATH instead of AUTO. Using PATH gives the user full control of the format of JSON output. JSON will output three separate document for the same select statement when using PATH option.

USE WideWorldImporters;
GO
SELECT o.OrderID ,
CustomerID ,
SalespersonPersonID ,
PickedByPersonID ,
ContactPersonID ,
BackorderOrderID ,
ol.Description
FROM Sales.Orders AS o
JOIN Sales.OrderLines AS ol ON o.OrderID = ol.OrderID
WHERE CustomerID = 1000
AND o.OrderID = 2899
FOR JSON PATH;
[
   {
      "OrderID": 2899,
      "CustomerID": 1000,
      "SalespersonPersonID": 15,
      "PickedByPersonID": 2,
      "ContactPersonID": 3200,
      "Description": "\"The Gu\" red shirt XML tag t-shirt (White) XXL"
   },
   {
      "OrderID": 2899,
      "CustomerID": 1000,
      "SalespersonPersonID": 15,
      "PickedByPersonID": 2,
      "ContactPersonID": 3200,
      "Description": "Ride on toy sedan car (Blue) 1/12 scale"
   },
   {
      "OrderID": 2899,
      "CustomerID": 1000,
      "SalespersonPersonID": 15,
      "PickedByPersonID": 2,
      "ContactPersonID": 3200,
      "Description": "Shipping carton (Brown) 356x356x279mm"
   }
]

You can also define your own object and control which values will be included in that object. For further processing of the JSON document if you need a root element you can declare that too with the PATH option using ROOT.

USE WideWorldImporters;
GO
SELECT o.OrderID AS [ID.Order] ,
 CustomerID AS [ID.Customer] ,
 SalespersonPersonID AS [ID.SalesPerson] ,
 PickedByPersonID AS [ID.PickedPerson] ,
 ContactPersonID AS [ID.ContactPerson] ,
 BackorderOrderID AS [ID.Backorder] ,
 ol.Description AS [Product.Description]
FROM Sales.Orders AS o
 JOIN Sales.OrderLines AS ol ON o.OrderID = ol.OrderID
WHERE CustomerID = 1000
 AND o.OrderID = 2899
FOR JSON PATH, ROOT ('OrderDetail');
{
   "OrderDetail": [
      {
         "ID": {
            "Order": 2899,
            "Customer": 1000,
            "SalesPerson": 15,
            "PickedPerson": 2,
            "ContactPerson": 3200
         },
         "Product": {
            "Description": "\"The Gu\" red shirt XML tag t-shirt (White) XXL"
         }
      },
      {
         "ID": {
            "Order": 2899,
            "Customer": 1000,
            "SalesPerson": 15,
            "PickedPerson": 2,
            "ContactPerson": 3200
         },
         "Product": {
            "Description": "Ride on toy sedan car (Blue) 1/12 scale"
         }
      },
      {
         "ID": {
            "Order": 2899,
            "Customer": 1000,
            "SalesPerson": 15,
            "PickedPerson": 2,
            "ContactPerson": 3200
         },
         "Product": {
            "Description": "Shipping carton (Brown) 356x356x279mm"
         }
      }
   ]
}

Hope this will help you getting started with JSON in SQL Server 2016.

2 replies on “TSQL Tuesday #79 -JSON Support in SQL 2016”

Leave a Reply to T-SQL Tuesday #079 Roundup: It’s 2016! | Michael J. Swart Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.