Friday, August 26, 2016

SSRS Tips : Fixing Format Issues in MHTML Rendered SSRS Reports

Impetus

The impetus for writing this blog came from a recent issue I faced while setting up email based subscription for one of my MIS reports for enterprise wide distribution. The report content had to be embedded within the email in HTML format so I had set the rendering option as MHTML for the subscription. When report is delivered using subscription it can be noticed that none of the formatting, cosmetic settings on the original report gets applied. 
This article explains how you can overcome this issue to preserve the native format settings you applied to the report while rendering it through automated subscriptions in MHTML format.


Illustration

Consider the below report created in SSRS


The requirement was to schedule an automated delivery of the above report through email to a list of users. The report has to be embedded within the email body rather than being as a separate attachment.
The best way to implement the above requirement is to set an email based subscription for the report and configure the render format as MHTML. This would ensure that the report will be embedded within the body of the email
As per the above approach subscription is configured for the report as below

.

When the subscription got triggered the email obtained was as below

As you see from the above, the rendered report was stripped off its format settings when rendered in MHTML format
This would cause difficulty in interpreting the report especially when amount of data shown in the report is huge. Question was how to make sure the format remains intact while rendering through sunscription as above
I was able to arrive at the solution after some analysis. The solution is to use expression mode to set the format properties for the report cells
As an example see the below illustration


Once you set this as an expression and set it for subscription you can see the rendered report as below


As you see from the above the report gets rendered with all the format settings correctly applied. All the property values have been set using expressions in the report designer and MHTML format will preserve the values while rendering through the subscription.

Conclusion

As seen from the illustration above, the format properties of the report can be preserved intact by using expressions while rendering in MHTML format.

Tuesday, August 2, 2016

What's New in SQL 2016 - Temporal Tables

Introduction

Most of us are familiar with CDC tables in SQL Server. CDC or Change Data Capture is used to track and capture the DML changes happening in a table and makes use of log entries for capturing the information. SQL 2016 provides similar kind of functionality for capturing the history of the data related to memory optimized tables. These historical data capturing feature is referred to as Temporal Tables.

Memory  Optimized Tables were introduced in SQLServer 2014 and they provide an efficient way to store and manipulate data using natively compiled procedures, They provide two levels of durability Schema Only and Schema and Data. Schema Only will only make sure schema is preserved in case of a server restart whereas in the latter case both the schema as well as the data is persisted as in the case of a normal table (disk based).
Temporal tables are only supported in the case where durability option is set to schema and data. These tables will be persisted to disk along with data. When we created a temporal table it will preserve the history and will provide data easily based on our point in time analysis.
The temporal tables makes use of a history table internally to track the history of data changes happening in the memory optimized table. The main table will have two datetime2 type fields which are referred to as period columns. This is used by the table to determine the validity of each record to provide an effective point in time analysis. The date fields determine the validity of a record ie period for which record is(was) valid

Illustration

Consider the below case where we have a database to which we are going to add a memory optimized table and make it a temporal table.

For creating a memory optimized table we need to first have a file group in the database that supports memory optimized data. This can be done by using a series of ALTER DATABASE statements as shown below (Assumption is that SQL2016LocalTest is an already existing database in your SQLServer instance)

ALTER DATABASE [SQL2016LocalTest] ADD FILEGROUP mem_opt CONTAINS MEMORY_OPTIMIZED_DATA   
ALTER DATABASE [SQL2016LocalTest] ADD FILE (name='mem_opt1', filename='') TO FILEGROUP mem_opt   
ALTER DATABASE [SQL2016LocalTest] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON  
GO  


Once the filegroup is setup as above we shall create the memory optimized table on it using CREATE TABLE statement as below

CREATE TABLE dbo.HotelBooking
(
BookingID int,
RoomNo int,
CustomerName varchar(100),
BookingDate datetime,
ValidFrom datetime2  generated always as row start not null default ('19000101'),
ValidTo datetime2    generated always as row end not null default ('99991231'),
PRIMARY KEY CLUSTERED (BookingID),
period for system_time(ValidFrom, ValidTo)
)
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.BookingHistory));


Once the above statement is executed we will have a temporal table setup with an internal history table which will used for tracking the history of data from the main table.
You can check this by expanding tables under the corresponding databases node to see the tables as per below



As you see the table will be represented with a small clock icon indicating that it is a temporal table and will also have the history table specified under it. The history table will be an exact replica of the main table in schema and will have clustered index on the key column.
Now lets insert some data to this table. For evaluating the history capture its best if you do the data manipulation operation over a period of time.

INSERT dbo.HotelBooking
(
BookingID,
RoomNo,
CustomerName,
BookingDate
)
VALUES
(
1001,
101,
'Peter',
'20150923'
)

UPDATE dbo.HotelBooking 
SET CustomerName = 'Ramu' 
WHERE RoomNo = 101

INSERT dbo.HotelBooking
(
BookingID,
RoomNo,
CustomerName,
BookingDate
)
VALUES
(
1002,
115,
'John',
'20160314'

)

INSERT dbo.HotelBooking
(
BookingID,
RoomNo,
CustomerName,
BookingDate
)
VALUES
(
1005,
212,
'Mary',
'20160416'

)

UPDATE  dbo.HotelBooking
SET BookingDate = '20160420'
WHERE BookingID = 1005

INSERT dbo.HotelBooking
(
BookingID,
RoomNo,
CustomerName,
BookingDate
)
VALUES
(
1011,
307,
'Khader',
'20160617'

)

UPDATE dbo.HotelBooking 
SET CustomerName = 'Michelle' 
WHERE RoomNo = 101

INSERT dbo.HotelBooking
(
BookingID,
RoomNo,
CustomerName,
BookingDate
)
VALUES
(
1015,
115,
'Mohsin',
'20160622'

)

DELETE FROM dbo.HotelBooking
WHERE BookingID = 1002

Once you've done the above data addition/modification you can check the main and history table and you will get the below data snapshot




I have done the data modification over a period of time (around 2 days) to indicate the effect on history table.
If you analyze the above data you can see how the progression of data is getting captured in the history
Everytime a new insert happens it will be captured in the main table HotelBooking with ValidFrom set to the corresponding system time and ValidTo set to the maxdate value (9999-12-31 23:59:59.99999)
Similarly every update operation is carried out by means of two internal operations a delete of the old entry followed by insertion of new entry. This can be seen from resultset above where you will have an entry in both the tables (see records with BookingID 1001 and 1005 in the above case). The history table will have the entry with the values prior to the modification with ValidFrom as actual ValidFrom value from the main entry and ValidTo as the corresponding system time. The main table entry will have the modified values with ValidFrom as the corresponding system time and ValidTo as the max date value.
In the case of delete operations the record will be removed from the main table and there will be an entry added to the history table with ValidFrom as the original ValidFrom and ValidTo as the corresponding system time.
Now that we have got an idea of what happens on the background and how data will get captured in the two tables lets now see some functions which are associated to Temporal tables and which will help us to query temporal data for time based analysis.

AS OF time

AS OF time function gives the snapshot of temporal table data at an instant of time i.e the data that is valid at that instance

Lets try it on our sample table and illustration is below

SELECT * FROM dbo.HotelBooking FOR SYSTEM_TIME AS OF '2016-07-25 05:15'



Now see the last resultset and compare it with the original table vs history entries and you will notice that there are only 3 entries in the resultset.
If you check the ValidFrom and ValidTo values for the resultset entries its pretty evident that those were the entries which were valid as on specified snapshot time (ie ValidFrom < 2016-07-25 05:15 < ValidTo)
 The ignored records were those which were either expired before snapshot date (ex: 1001 Peter) or the ones which became valid after the snapshot time (ex: 1015,1011)
So effectively what AS OF function does is to apply a query logic as below

SELECT * FROM dbo.HotelBooking WHERE '2016-07-25 05:15' BETWEEN ValidFrom  AND ValidTo
UNION ALL
SELECT * FROM dbo.BookingHistory WHERE '2016-07-25 05:15' BETWEEN ValidFrom  AND ValidTo  

Execute this and you will get the same resultset as returned by AS OF

Graphically this can be depicted using the three scenarios as shown below


FROM time1 TO time2

FROM time1 TO time2 function returns all records whose validity overlaps the period specified by time1 to time2. 
The illustration for this can be shown as below

SELECT * FROM dbo.HotelBooking FOR SYSTEM_TIME  FROM '2016-07-24 10:34' TO '2016-07-25 05:16:55.4510553'



The resultset in this consists of all records with the interval ValidFrom to ValidTo overlapping between interval specified by time1 and time2. One thing to note here is that record with BookingID 1011 was not included as it became valid only from the end boundary value whereas function considers only the cases where interval starts or ends within the boundary value ie the equivalent query can be written as

SELECT * FROM dbo.HotelBooking WHERE   ValidFrom < '2016-07-25 05:16:55.4510553' AND ValidTo > '2016-07-24 10:34'
UNION ALL
SELECT * FROM dbo.BookingHistory WHERE    ValidFrom < '2016-07-25 05:16:55.4510553' AND ValidTo > '2016-07-24 10:34'

The corresponding scenarios can be represented graphically as below


BETWEEN time1 AND time2


BETWEEN function works quite similar to the FROM function to get the resultset where record interval of ValidFrom to ValidTo  overlaps with the interval specified by time1 to time2. The only difference between BETWEEN and FROM is that BETWEEN considers the end boundary value (time2) also as a part of the interval unlike the FROM function.
So if you see the illustration for BETWEEN it looks like below

SELECT * FROM dbo.HotelBooking FOR SYSTEM_TIME  BETWEEN '2016-07-24 10:34' AND '2016-07-25 05:16:55.4510553'




Check the result and you can see that its very much similar to resultset for FROM with only addition being the record with BookingID 1011 which starts at the same time as the end time of the interval
So equivalent query in this case can be given as

SELECT * FROM dbo.HotelBooking WHERE   ValidFrom <= '2016-07-25 05:16:55.4510553' AND ValidTo > '2016-07-24 10:34'
UNION ALL
SELECT * FROM dbo.BookingHistory WHERE    ValidFrom <= '2016-07-25 05:16:55.4510553' AND ValidTo > '2016-07-24 10:34'
ORDER BY ValidFrom

Corresponding graphical representation is shown below



CONTAINED IN (time1,time2)

CONTAINED IN function returns a resultset where the record validity falls within the interval from time1 to time2 ie ValidFrom and ValidTo both lying within the interval

Illustration is given below

SELECT * FROM dbo.HotelBooking FOR SYSTEM_TIME  CONTAINED IN ( '2016-07-24 10:32','2016-07-25 05:18:35.3157322') ORDER BY ValidFrom


The resultset on analysis reveals that only those records whose validity falls fully within the interval time1 to time2 are returned by CONTAINED IN function.

The equivalent query is as below

SELECT * FROM dbo.HotelBooking WHERE ValidFrom >= '2016-07-24 10:32' AND ValidTo <= '2016-07-25 05:18:35.3157322'
UNION ALL
SELECT * FROM dbo.BookingHistory WHERE ValidFrom >= '2016-07-24 10:32' AND ValidTo <= '2016-07-25 05:18:35.3157322' ORDER BY ValidFrom

Graphically this can be represented as below


ALL


The ALL function simply combines the results from both the main and history table in the resultset

Illustration below

SELECT * FROM dbo.HotelBooking FOR SYSTEM_TIME ALL   ORDER BY ValidFrom



The resultset includes all records from both the tables

The equivalent query is as below

SELECT * FROM dbo.HotelBooking 

UNION ALL

SELECT * FROM dbo.BookingHistory  ORDER BY ValidFrom

See the corresponding graphical representation below

Conclusion

The above illustrations gives a clear idea about how temporal functions can be used to generate time based results from a temporal table. 
This shows that Temporal tables provide a much convenient way of capturing history and using it for effective time based analysis
Hope you this article was informative enough. Feel free to revert with any comments you may have.

Wednesday, July 13, 2016

Whats New in SQL 2016 - Native JSON Function Support - Performing Data Manipulations in JSON Documents


This is the last and the final article in the series which discusses on the native JSON support in SQL 2016.
Previously I had blogged on how JSON documents can be generated from relational data using FOR JSON construct here

http://visakhm.blogspot.ae/2016/06/whats-new-in-sql-2016-native-json.html

and also how JSON documents can be parsed and relational data can shred from them using OPENJSON here
http://visakhm.blogspot.ae/2016/07/whats-new-in-sql-2016-native-json.html

The article explains on some of the JSON functions which is available in Transact SQL  and can be utilized for manipulating the data within JSON documents.

ISJSON()

The ISJSON function is used to check if a document passed has a valid JSON data. It accepts an expression which would be a string and validates if it contains aproper JSON data. The return value would be a boolean result (1 or 0) based on whether the string contains a valid JSON result or not.
Lets see the small illustration below to understand the ISJSON function



declare @x nvarchar(max)='{
  "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
    }
  ]
}'

SELECT ISJSON(@x)

The result will be 1 as @x contains valid JSON data

If at all the passed document can be considered as a valid JSON, ISJSON function returns 1 as seen from the below examples



SELECT *
FROM
(
VALUES ('{}'),('[]'),('{"item":1}'),('{"q":[1]}'),('[0,1,2]')
)t(v)
WHERE ISJSON(v)=1

and the result



The first two values represents blank JSON object and array documents whereas the next three represents JSON document with single object/array object and all of them are valid.
One more thing you may note from the above illustration is how we can use ISJSON function as a filter condition in the WHERE clause to check for the rows with valid JSON data in the column and then to do some manipulations on them.

JSON_VALUE()

JSON_VALUE is another very useful utility function which can be used to parse a JSON document and return a single element value. JSON_VALUE takes as argument a JSON expression and a path and traverses the JSON document as per the path to return a single scalar value.

Lets see a simple example of how JSON_VALUE can be applied to get values from a JSON document

declare @x nvarchar(max)='{
  "Item": 
    {
      "ItemID": 101,
      "ItemDesc": "Monitor CRT 18\"",
      "Unitprice": 200
    }
}'

The above shows a very simple JSON document with a single JSON element having three key value pairs.
The values from the above JSON document can be parsed out using JSON_VALUE function as per below code

SELECT JSON_VALUE(@x,'$.Item.ItemID') AS ItemID,
JSON_VALUE(@x,'$.Item.ItemDesc') AS ItemDesc,
JSON_VALUE(@x,'$.Item.Unitprice') AS Unitprice
The result is as below



This is a simple document with all key values in the same level. Now lets see how we can use JSON_VALUE in the case where JSON document has key values in multiple levels

Consider the below JSON example

declare @x nvarchar(max) = '{
     "Order": {
      "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"
                }
            }
         }
}'

Now to get the data from the various levels within the document we can apply JSON_VALUE function as per below

SELECT JSON_VALUE(@x,'$.Order.OrderDesc') AS OrderDesc,
JSON_VALUE(@x,'$.Order.OrderDate') AS OrderDate,
JSON_VALUE(@x,'$.Order.ShippedDate') AS ShippedDate,
JSON_VALUE(@x,'$.Order.WebOrder') AS WebOrder,
JSON_VALUE(@x,'$.Order.OrderItems.OrderQty') AS OrderQty,
JSON_VALUE(@x,'$.Order.OrderItems.Item.ItemDesc') AS ItemDesc,
JSON_VALUE(@x,'$.Order.OrderItems.Item.Unitprice') AS Unitprice,
JSON_VALUE(@x,'$.Order.OrderItems.Item.SUbItem.SubItemDesc') AS SubItemDesc

The corresponding result is given below


One thing to note here is that JSON_VALUE can return only a scalar value from within a single key value pair in the JSON data. So in case you're referring to a JSON array object which includes multiple key value pairs as per the path specified it will return NULL under the default mode.
This can be illustrated using the below example

declare @x nvarchar(max) = '{
  "Order": 
    {
      "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"
                },
{
 "SubItemDesc": "SP22"
}
    ]
            },
{
              "ItemDesc": "Monitor",
              "Unitprice": 22,
              "SUbItem": [
                {
                  "SubItemDesc": "SP27"
                }
]
            }
]
        }
   }
}'

SELECT JSON_VALUE(@x,'$.Order.OrderDesc') AS OrderDesc,
JSON_VALUE(@x,'$.Order.OrderDate') AS OrderDate,
JSON_VALUE(@x,'$.Order.ShippedDate') AS ShippedDate,
JSON_VALUE(@x,'$.Order.WebOrder') AS WebOrder,
JSON_VALUE(@x,'$.Order.OrderItems.OrderQty') AS OrderQty,
JSON_VALUE(@x,'$.Order.OrderItems.Item.ItemDesc') AS ItemDesc,
JSON_VALUE(@x,'$.Order.OrderItems.Item.Unitprice') AS Unitprice,
JSON_VALUE(@x,'$.Order.OrderItems.Item.SUbItem.SubItemDesc') AS SubItemDesc

If you try the above illustration the result obtained will be something like below


Analyze the result and you will find that the code is not able to retrieve any keys at the Item and SUbtem level using JSON_VALUE function.
If you check the JSON document carefully you can see that Item and SUBItem consists of actually an array object (notice the [] around them) rather than a single JSON element. This is why JSON_VALUE cant extract value from them. As previously explained the JSON_VALUE can only extract a single scalar value which is why it fails in the above scenario.
For the above case if we want to get the values we need to tweak the query as below to get the Item and SUbItem level key values

The modified query would be like this

SELECT JSON_VALUE(@x,'$.Order.OrderDesc') AS OrderDesc,
JSON_VALUE(@x,'$.Order.OrderDate') AS OrderDate,
JSON_VALUE(@x,'$.Order.ShippedDate') AS ShippedDate,
JSON_VALUE(@x,'$.Order.WebOrder') AS WebOrder,
JSON_VALUE(@x,'$.Order.OrderItems.OrderQty') AS OrderQty,
JSON_VALUE(@x,'$.Order.OrderItems.Item[0].ItemDesc') AS ItemDesc1,
JSON_VALUE(@x,'$.Order.OrderItems.Item[0].Unitprice') AS Unitprice1,
JSON_VALUE(@x,'$.Order.OrderItems.Item[0].SUbItem[0].SubItemDesc') AS SubItemDesc11,
JSON_VALUE(@x,'$.Order.OrderItems.Item[0].SUbItem[1].SubItemDesc') AS SubItemDesc12,
JSON_VALUE(@x,'$.Order.OrderItems.Item[1].ItemDesc') AS ItemDesc2,
JSON_VALUE(@x,'$.Order.OrderItems.Item[1].Unitprice') AS Unitprice2,
JSON_VALUE(@x,'$.Order.OrderItems.Item[1].SUbItem[0].SubItemDesc') AS SubItemDesc21

And the result below


But this method is not scalable as we have to be certain on the number of elements at query time to apply the position number based static logic. In such cases we would require logic which will iterate through the JSON elements to return corresponding key values. This will be explained in detail later in this article.

strict and lax modes

There are two modes under which the path expression works in the case of JSON functions. 
The default is the lax mode where the parser doesn't enforce strict checking for the JSON path specified. In case the path is valid it returns the value as per the path. Otherwise it will return a NULL value. lax mode being the default option you dont need to explicitly specify it within the path. So all the previous examples uses lax mode under the hood.
In contrast strict mode the JSON path passed is strictly checked against and in case it is an invalid path it returns an error.
To illustrate this lets use a modified version of the last query in the two modes and see the difference

lax mode

SELECT JSON_VALUE(@x,'lax $.Order.OrderDesc') AS OrderDesc,
JSON_VALUE(@x,'lax $.Order.OrderDate') AS OrderDate,
JSON_VALUE(@x,'lax $.Order.ShippedDate') AS ShippedDate,
JSON_VALUE(@x,'lax $.Order.WebOrder') AS WebOrder,
JSON_VALUE(@x,'lax $.Order.OrderItems.OrderQty') AS OrderQty,
JSON_VALUE(@x,'lax $.Order.OrderItems.Item[0].ItemDesc') AS ItemDesc1,
JSON_VALUE(@x,'lax $.Order.OrderItems.Item[0].Unitprice') AS Unitprice1,
JSON_VALUE(@x,'lax $.Order.OrderItems.Item[0].SUbItem[0].SubItemDesc') AS SubItemDesc11,
JSON_VALUE(@x,'lax $.Order.OrderItems.Item[0].SUbItem[1].SubItemDesc') AS SubItemDesc12

JSON_VALUE(@x,'lax $.Order.OrderItems.Item[1].ItemDesc') AS ItemDesc2,
JSON_VALUE(@x,'lax $.Order.OrderItems.Item[1].Unitprice') AS Unitprice2,
JSON_VALUE(@x,'lax $.Order.OrderItems.Item[1].SUbItem[0].SubItemDesc') AS SubItemDesc21,
JSON_VALUE(@x,'lax $.Order.OrderItems.Item[1].SUbItem[1].SubItemDesc') AS SubItemDesc22


and the result


Notice the NULL value for the nonexistent key coming at the end. This query works without any error because of lax mode
Now lets see what happens with the same query in the strict mode

SELECT JSON_VALUE(@x,'strict $.Order.OrderDesc') AS OrderDesc,
JSON_VALUE(@x,'strict $.Order.OrderDate') AS OrderDate,
JSON_VALUE(@x,'strict $.Order.ShippedDate') AS ShippedDate,
JSON_VALUE(@x,'strict $.Order.WebOrder') AS WebOrder,
JSON_VALUE(@x,'strict $.Order.OrderItems.OrderQty') AS OrderQty,
JSON_VALUE(@x,'strict $.Order.OrderItems.Item[0].ItemDesc') AS ItemDesc1,
JSON_VALUE(@x,'strict $.Order.OrderItems.Item[0].Unitprice') AS Unitprice1,
JSON_VALUE(@x,'strict $.Order.OrderItems.Item[0].SUbItem[0].SubItemDesc') AS SubItemDesc11,
JSON_VALUE(@x,'strict $.Order.OrderItems.Item[0].SUbItem[1].SubItemDesc') AS SubItemDesc12,
JSON_VALUE(@x,'strict $.Order.OrderItems.Item[1].ItemDesc') AS ItemDesc2,
JSON_VALUE(@x,'strict $.Order.OrderItems.Item[1].Unitprice') AS Unitprice2,
JSON_VALUE(@x,'strict $.Order.OrderItems.Item[1].SUbItem[0].SubItemDesc') AS SubItemDesc21,
JSON_VALUE(@x,'strict $.Order.OrderItems.Item[1].SUbItem[1].SubItemDesc') AS SubItemDesc22

Now the result


As seen from the above example it throws an error when it finds an non existent key as per the specified path in the strict mode.


JSON_QUERY()

JSON_QUERY function works similar to JSON_VALUE with the exception that it accepts a JSON path and returns a JSON document as the result

See the below example to understand how JSON_QUERY works


declare @x nvarchar(max) = '{
  "Order": 
    {
      "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"
                }
            }
        }
   }
}'

SELECT JSON_QUERY(@x,'$.Order.OrderItems') AS OrderItems,
JSON_QUERY(@x,'$.Order.OrderItems.Item') AS Item,
JSON_QUERY(@x,'$.Order.OrderItems.Item.SUbItem') AS SubItem


The result will contain three JSON documents as shown below


JSON_QUERY function similar to JSON_VALUE works in two supported modes lax and strict with lax being the default. 
The functionality remains the same with strict mode throwing an error in case of passing an invalid path as compared to lax mode returning a NULL value
Here is an illustration on the same 

declare @x nvarchar(max) = '{
  "Order": 
    {
      "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"
                }
            }
        }
   }
}'

SELECT JSON_QUERY(@x,'lax $.Order.OrderItems') AS OrderItems,
JSON_QUERY(@x,'lax $.Order.OrderItems.Item') AS Item,
JSON_QUERY(@x,'lax $.Order.OrderItems.Item.SUbItem') AS SubItem,
JSON_QUERY(@x,'lax $.Order.Customer') AS Customer

Result is as given below


Now strict mode will be as below

SELECT JSON_QUERY(@x,'strict $.Order.OrderItems') AS OrderItems,
JSON_QUERY(@x,'strict $.Order.OrderItems.Item') AS Item,
JSON_QUERY(@x,'strict $.Order.OrderItems.Item.SUbItem') AS SubItem,
JSON_QUERY(@x,'strict $.Order.Customer') AS Customer

with the result


Parsing JSON Array Objects And Nested Levels

Now lets look at how JSON array objects and JSON documents with nested levels can be parsed.
Both the functions JSON_QUERY and JSON_VALUE can only parse a single JSON element at a time. Hence when we have to have to use a construct which helps us to iterate through each object in the array before we apply JSON_VALUE or JSON_QUERY over it.
If you have read my earlier article on JSON support you would be able to make out that we can use OPENJSON construct to iterate through the JSON document array.
Once OPENJSON is applied it returns as a elements of JSON as a resultset over which we can apply the JSON_VALUE or the JSON_QUERY function to return us a single scalar value or a JSON element depending on the case.
Now lets see an illustration to understand this

Consider the example given below

declare @x nvarchar(max) = '{
  "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"
        }
      ]
    }
  ]
}'

If you analyze the above example you can see that JSON document consist of array of JSON objects each with nested JSON array objects within them. Since it involved JSON array objects we would require OPENJSON function to parse them. Once we get individual JSON elements we can then apply JSON_VALUE or JSON_QUERY over them
So the query will look like this

SELECT JSON_VALUE(value,'$.OrderDesc') AS OrderDesc,
TRY_CONVERT(datetime,JSON_VALUE(value,'$.OrderDate')) AS OrderDate,
TRY_CONVERT(datetime,JSON_VALUE(value,'$.ShippedDate')) AS ShippedDate,
JSON_VALUE(value,'$.WebOrder') AS WebOrder,
JSON_VALUE(oi,'$.OrderQty') AS OrderQty,
JSON_VALUE(i,'$.ItemDesc') AS ItemDesc,
JSON_VALUE(i,'$.Unitprice') AS UnitPrice,
JSON_VALUE(si,'$.SubItemDesc') AS SubItemDesc,
JSON_VALUE(c,'$.CustomerDesc') AS CustomerDesc
FROM OPENJSON(@x,'$.Orders') t
CROSS APPLY (SELECT JSON_QUERY(t.value,'$.Customer[0]')) m(c)
CROSS APPLY (SELECT value FROM OPENJSON(t.value,'$.OrderItems'))n(oi)
CROSS APPLY (SELECT value FROM OPENJSON(oi,'$.Item'))p(i)
OUTER APPLY (SELECT value FROM OPENJSON(i,'$.SUbItem'))q(si)

The resultset for the query is as given below



As seen from the above case, for iterating through JSON array we make use of OPENJSON. Within the array each  individual JSON document can be fetched using  JSON_QUERY function. In the above case since there is only single customer element we retrieve it using JSON_QUERY function and then apply series of JSON_VALUE function calls to return each key value within it. For the other documents which holds an array object we use further correlated OPENJSON calls by using APPLY operator and for each of the returned JSON documents we apply JSON_VALUE functions to return individual key element values.
One thing to note here is that path being passed is case sensitive so you need to make sure you pass path in the exact case as how it appears within the JSON document
If you want a filter to be applied on above query you can make use of JSON_VALUE to make sure you retrieve only the documents where scalar value returned matches a given condition
Like for example

SELECT JSON_VALUE(value,'$.OrderDesc') AS OrderDesc,
TRY_CONVERT(datetime,JSON_VALUE(value,'$.OrderDate')) AS OrderDate,
TRY_CONVERT(datetime,JSON_VALUE(value,'$.ShippedDate')) AS ShippedDate,
JSON_VALUE(value,'$.WebOrder') AS WebOrder,
JSON_VALUE(oi,'$.OrderQty') AS OrderQty,
JSON_VALUE(i,'$.ItemDesc') AS ItemDesc,
JSON_VALUE(i,'$.Unitprice') AS UnitPrice,
JSON_VALUE(si,'$.SubItemDesc') AS SubItemDesc,
JSON_VALUE(c,'$.CustomerDesc') AS CustomerDesc
FROM OPENJSON(@x,'$.Orders') t
CROSS APPLY (SELECT JSON_QUERY(t.value,'$.Customer[0]')) m(c)
CROSS APPLY (SELECT value FROM OPENJSON(t.value,'$.OrderItems'))n(oi)
CROSS APPLY (SELECT value FROM OPENJSON(oi,'$.Item'))p(i)
OUTER APPLY (SELECT value FROM OPENJSON(i,'$.SUbItem'))q(si)
WHERE JSON_VALUE(oi,'$.OrderQty') > 20


This will only retrieve the data for the case where specified key value condition is satisfies within the JSON element and we will get the below output


JSON_MODIFY()

JSON_MODIFY function as the name implies allows us to modify the data within an existing JSON document. This function can be used in cases where we need to replace an existing value or set a new value to a key within JSON document. We can also use it to rename a given JSON document key.
The syntax of JSON_MODIFY us as below

JSON_MODIFY(JSONexpression, JSON Path, value)

Lets now see JSON_MODIFY function in action 

Consider this simple example

declare @x nvarchar(max) = '{
     "Order": {
      "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"
                }
            }
         }
     }
}'

Now lets try to update value of Unitprice key in Item to 50 using JSON_MODIFY

The code will look like this

SELECT JSON_VALUE(@x,'$.Order.OrderItems.Item.Unitprice') AS Unitprice

   SET @x = JSON_MODIFY(@x,'$.Order.OrderItems.Item.Unitprice',50)

   SELECT @x,JSON_VALUE(@x,'$.Order.OrderItems.Item.Unitprice') AS Unitprice


The corresponding result will be as below



As seen from the result the key value got updated to 50
Now lets see someother applications of JSON_MODIFY. 
As specified earlier JSON_MODIFY can also be used to create a new key within JSON element, append a value to existing object array, rename a key within a JSON element or delete a key within JSON_MODIFY.

Lets see some illustrations below


1. Create a new key to JSON element


SET @x = JSON_MODIFY(@x,'$.Order.OrderItems.Item.ItemQty',50)

SELECT @x

The result is as below


 
   "Order": 
      "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"
            },
            "ItemQty":50
         }
      }
   }
}

2. Append a value to JSON element


declare @x nvarchar(max) = '{
     "Order": {
      "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"
                }
            }
         }
}
}'


SET @x = JSON_MODIFY(@x,'append $.Order.OrderItems.Item.ItemDesc','Optical USB Enabled')

SELECT @x


The resulting JSON document is as below

 
   "Order": 
      "OrderDesc":"Order 1",
      "OrderDate":"2013-02-12T00:00:00",
      "ShippedDate":"2013-02-20T00:00:00",
      "WebOrder":false,
      "OrderItems": 
         "OrderQty":10,
         "Item": 
            "ItemDesc": 
               "Wireless Mouse",
               "Optical USB Enabled"
            ],
            "Unitprice":"30",
            "SUbItem": 
               "SubItemDesc":"SP17"
            }
         }
      }
   }
}

As seen above it appends the value passed to the ItemDesc array object.

3. Rename a key within JSON document


Now lets see an example of the case where we need to rename a key within a JSON array

SET @x = JSON_MODIFY(JSON_MODIFY(@x,'$.Order.OrderItems.Item.SubItem',JSON_QUERY(@x,'$.Order.OrderItems.Item.SUbItem')),'$.Order.OrderItems.Item.SUbItem',NULL)

SELECT @x

The result as below

 
   "Order": 
      "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"
            }
         }
      }
   }

Analyse the result and you can see that the JSON document key element has been renamed from SUbitem to SubItem.


4. Delete a key within JSON document

declare @x nvarchar(max) = '{  
   "Order":{  
      "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",
            "ItemQty":50,
            "SubItem":{  
               "SubItemDesc":"SP17"
            }
         }
      }
   }
}'

The code is as given below

SET @x = JSON_MODIFY(@x,'$.Order.OrderItems.Item.ItemQty',NULL)

SELECT @x

The result will look like this

 
   "Order": 
      "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"
            }
         }
      }
   }
}


If you notice the result you will see that the ItemQty key is missing in the document which indicates that key has been deleted from the document.

Please note that all the above queries are using the default lax mode. While using strict mode it will throw the standard error indicating JSON path not found if you pass a non existing key name.

Conclusion

The examples given above illustrate the options available in SQL 2016 using standard JSON functions to do the data manipulations within the JSON data.
One or more of these functions can be effectively used in cases where data within the JSON needs to be modified to get the desired result.