TSQL Tuesday #79 -JSON Support in SQL 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 save documents in binary form of  JSON called BSON. Our developers are excited to see JSON support in SQL 2o16.  I will give some simple examples about 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 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 structure of 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 on 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 JSON document if you need a root element you can declare that too with 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.

FacebookTwitterGoogle+Share