Monday, June 27, 2016

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.

Friday, June 17, 2016

Whats New in SSIS 2016 - Expose SSIS Package Data Externally as a View

Introduction

SQL 2016 has brought in lots of new and exciting features on the plate. One of the pretty cool feature that was added to SSIS in SQL 2016 is the ability to expose the data directly as a SQLServer database view object which can be accessed using T-SQL queries.

This is made possible by the introduction of two new components
1. A new linked server provider SSISOLEDB which is capable of exposing the SSIS data to SQLServer database
2. A new data flow component called Data Streaming Destination which will stream the data to be consumed through the linked server based on the above provider

Once you've installed SQL 2016 you can connect to the instance using management studio and when you expand the Server Objects - Linked Servers - Providers you can see this new provider listed as per below



Similarly you can launch SSDT (SQLServer Data Tools) 2015 and start an Integration Services project and create a sample package with a single data flow task. Inside data flow task tab you can see the new destination component available


Now that we have seen the two components lets see how they can be used together for exposing SSIS data as a SQLServer database object.

Illustration

Consider the scenario where we have data coming from different sources which needs to be exposed to  our database as an object.
For the sake of this article lets consider the below scenario
1. Customer details coming from a SQL database
2. Customet Satisfaction score data coming from an Excel spreadsheet

The requirement is to merge the data from the above sources to be accessed within third database most often a datawarehouse for analytical and reporting needs.
For this kind of requirement the obvious way to approach is to create a SSIS package which can extract the required data from the various source and do the merge operation based on the related columns.
Such a package for the above case would look like this


The data flow tasks will look like below


The sources involved here are SQL database which has the Customer details as per below

CREATE TABLE OrderDetails
(
OrderID int IDENTITY(1,1),
CustomerName varchar(100),
CustomerEMail varchar(100),
OrderDesc varchar(100),
OrderPrice Decimal(10,2),
OrderDate datetime
)
INSERT OrderDetails (CustomerName,CustomerEMail,OrderDesc,OrderPrice,OrderDate)
VALUES ('Customer 1','abc@xyz.com','Order ABC',130,'20151104'),
('Customer 2','kytrtetet@treby.com','Order PQR',2150.50,'20151203'),
('Customer 5','887rtert767@hakii.co.hk','Order for DEF and Co',1785,'20160112'),
('Customer 7','hjgjh@kaz.co.in','M/S Bhavya Comp Ltd',890,'20160120'),
('Customer 9','ewrewrr@oiuiiuo.co.in','XYZ',1125,'20160205'),
('Customer 1','abc@xyz.com','DEF',3400,'20160322'),
('Customer 9','ewrewrr@oiuiiuo.co.in','MNU',215,'20160312'),
('Customer 14','ytrytr@terry.co.in','PUK',345,'20160415'),
('Kirby Stevens','kirby.stevens@jackoss.co.eu','WKP',2300,'20160420'),
('Kerney Michelle','kerney@hkrky.co.ae','VER',485,'20160512'),
('Customer 1','abc@xyz.com','RTU',1320,'20160514'),
('Mark Bowerman','mark.2134@kayko.com','BJY',1130,'20160603'),
('Customer 9','ewrewrr@oiuiiuo.co.in','SAR',765,'20160610')

And the query used is as below to get the count and total value of orders per customer

SELECT CustomerName,
CustomerEMail,
COUNT(OrderDesc) AS OrderCount,
SUM(OrderPrice) AS TotalOrderPrice
FROM OrderDetails
GROUP BY CustomerName,
CustomerEMail
ORDER BY CustomerEMail

Source 2 is an excel spreadsheet with customer satisfaction data as below


And for this illustration we use the above query to get the average customer satisfaction score and  response count

SELECT email,
AVG(satscore) AS AvgSatScore,
COUNT(*) AS responseCount
FROM `Sheet1$`
GROUP BY email
ORDER BY email

From these two sources of data would be merged based on the common columns between the two resultsets as shown below



Once this is done we would get the required resultset. Now inorder to stream the resultset we will use Data Streaming Destination component and include the required columns to be accessed through the exposed view.


Data Streaming Destination will include the required columns as shown above
Once this is done we need to deploy the package to the Integration Services Catalog. 
The package will get deployed to the Integration Services Server as shown below


Once this is done we need to stream the package data as a view within a database in the associated instance.
This can be done using data feed publishing wizard which comes with the SQL 2016 installation. 


We can launch data feed publishing wizard and it will come up with the below window


Click Next and it will ask you to select the server and package to be published

Once selected it will also give you the option to modify the default values set for the connection managers and parameters in the package which I'm not changing for this example.



Clicking Next will bring it to Publish settings screen where we will specify the view name through which we need to expose the package data. 


One thing to note here is that Use 32Bit Runtime is set to True as one of the sources involved here is an Excel spreadsheet and the ACE driver installed is of 32 bit version in the server. In case you've the 64 bit version installed then you don't need to tweak this setting.
On clicking Next it goes to the validation screen and validates the various steps involved


Click on Next and finally click Finish to publish the package as a view


Once publishing is done you can connect to the server instance through SQL Management Studio and go to the specified database and expand views to see the view you published



Right click and Select data from the view and it will execute the package on the background and will provide you with the below result



You can confirm this by expanding Integration Services Catalog and finding the package within your deployed project and then right clicking and choosing the All Executions report which will show the log for successful execution of the package when view gets referred in the query



Conclusion

As shown by the illustration above you can use the new data feed publishing wizard available in SQL 2016 for exposing a SSIS package resultset as a view object within an associated relational database. This is a very useful feature which can be used for publishing a resultset consisting of data extracted from a variety of sources.




Thursday, June 2, 2016

SQL Server 2016 Available for General Availability Now

SQL 2016 has been released for general availability now
You can find the download links from MSDN

The evaluation period is for 180 days

SQL 2016 comes with lots of useful and cool features including

  • Native JSON support
  • Always Encrypted to protect sensitive data
  • Integration of R analytics scripts in T-SQL
  • Dynamic Data Masking
  • Polybase querying for integration with Hadoop, blob data etc
  • QueryStore providing historical execution plan information
  • Temporal tables with data versioning


Check out the announcement in official SQLServer blog for more details


One more exciting news is that SQLServer developer edition is now free starting from 2014 onwards. 


This provides us with one more compelling reason for trying out this exciting new release