Category Archives: Microsoft SQL Server

JSON in SQL Server 2016 – The Good, The Bad, and The Ugly

JSONonSQL

Microsoft introduced support for JSON data in SQL Server 2016 and in Azure SQL Database. I was excited to see this functionality coming. As an early user of XML when it was introduced, my expectations were high. Microsoft has done some really good work with JSON support, but I find that is not really as comprehensive as I had hoped.

The Good: Functions to Work with JSON Data

SQL Server now has some built in functions that support working with JSON. These are on par with XML data type methods. Here is the rundown on what they are and what they do.

OPENJSON

OPENJSON allows us to convert JSON to a tabular format. This function supports two output formats: default and explicit. Default returns key value pairs, where as explicit lets us define the context. Here is a sample of the syntax that can be used.

select Restaurant.RestaurantName, Restaurant.City, Restaurant.SeatsJSON, Seats.*
from dbo.Restaurant
cross apply

openjson(Restaurant.SeatsJSON, '$.Seats')
with (SeatNumber int '$."Seat Number"'
 , SeatCode varchar (50) '$.SeatCode'
 , TableNumber int '$.TableNumber' 
) as Seats

FOR JSON

FOR JSON works in reverse. This allows us to convert tabular data into JSON. AUTO automatically formats the results as arrays and nested arrays when joins are used. You are able to use aliases to as object names. PATH allows you to specify the JSON path you want to use for the results.

ISJSON

ISJSON can be used against a string value to determine if it is properly formatted. This can be handy when working with JSON functions or even in a CHECK constraint to make sure the column has properly formatted data.

JSON_VALUE

JSON_VALUE returns a scalar value from a JSON string using JSON path. The key here is that the value returned is scalar so working with arrays can sometimes be an issue if you cannot identify the position in the array.

JSON_VALUE (SeatsJSON, '$.Seats[0].SeatCode')

JSON_QUERY

JSON_QUERY returns a JSON formatted array or object using JSON path.

JSON_QUERY (SeatsJSON, '$.Seats')

JSON_MODIFY

JSON_MODIFY allows us to change the value based on the JSON path specified.

JSON_MODIFY(@RestJSON, '$.Restaurant.ZIP', '55337')

I know that I highlighted the basics around these functions. I plan to follow up on these later. These functions represent the good parts of the JSON implementation in SQL Server.

The Bad: Not a Datatype

My key disappointment with the implementation is the fact it is not a native data type like XML. At first I did not think this would be an issue, but after working with the new functionality, it feels incomplete. We can add a constraint with the ISJSON function to make sure our data is of the right type, but XML is just a data type. The biggest miss around the data type is likely concerning indexes as I talk about next.

The Ugly: Indexes

This is the worst part of the JSON solution. Because it is not a data type, no native indexing is supported. The current recommendation is to create a computed column using the JSON_VALUE function. However, this does not work with arrays, making the indexes of limited value. In a simple set of data, such as seats in a restaurant shown below, you cannot index the seats, only the restaurants.

{
    "Restaurants": [
        {
            "Restaurant": {
                "Restaurant ID": 1,
                "RestaurantName": "Sensational Servings MSP",
                "Seats": [
                    {
                        "SeatCode": "SSMSP-1-1",
                        "TableType": "Bar",
                        "Seat Number": 1
                    },
                    {
                        "SeatCode": "SSMSP-1-2",
                        "TableType": "Bar",
                        "Seat Number": 2
                    }                ]
            }
        },
        {
            "Restaurant": {
                "Restaurant ID": 2,
                "RestaurantName": "Sensational Servings LAS",
                "Seats": [
                    {
                        "SeatCode": "SSLAS-1-101",
                        "TableType": "Bar",
                        "Seat Number": 101
                    },
                    {
                        "SeatCode": "SSLAS-1-102",
                        "TableType": "Bar",
                        "Seat Number": 102
                    }                ]
            }
        }
    ]
}

So if I am looking for seats with the TableType Bar, we would not be able to do that with an index without storing the JSON file differently because each restaurant contains an array of seats. With the attribute as part of the array, we are unable to return every instance of the seats in the index. This will result in a table scan in most cases.

We can add indexes to simple JSON snippets, but complex JSON will result in table scans due to the nature of the functions. You should test your solutions to determine if the index is sufficient to support your query pattern.

The Moral of the Story

The JSON functionality is similar to XML data type methods. The lack of real index support will likely cause issues with the functionality at scale. Use the functions to help make JSON more usable in your environment, but be aware of its limitations as well.

SQL Saturday #492 Follow Up – A Window into Your Data

sqlsat492_web

Thanks for attending my session on window functions in TSQL. I hope you learned something you can take back and use in your projects or at your work. You will find an link to the session and code I used below. If you have any questions about the session post them in comments and I will try to get you the answers.

Questions and Comments

  1. Does RATIO_TO_REPORT exist in SQL Server? It is in Oracle.
    • Currently this function is not available in SQL Server
    • Here is the equivalent functionality using existing functions in SQL Server:
      • OrderAmt / SUM(OrderAmt) OVER (PARTITION BY OrderDate)
      • This example can use the source code I have referenced below. It uses the current value as the numerator and the sum by partition as the denominator. While not a simple function, the equivalent is still fairly simple using window functions to help.
  2. Demo issues with Azure SQL Database
    • During the session I ran into an issue with Azure SQL Database. It turns out that the following two functions are not supported there.
      • PERCENTILE_CONT
      • PERCENTILE_DISC

Slides, Code, and Follow Up Posts

The presentation can be found here: A Window into Your Data

The code was put into a Word document that you can get here: TSQL Window Function Code

This session is also backed by an existing blog series I have written.

T-SQL Window Functions – Part 1- The OVER() Clause

T-SQL Window Functions – Part 2- Ranking Functions

T-SQL Window Functions – Part 3: Aggregate Functions

T-SQL Window Functions – Part 4- Analytic Functions

Microsoft Resources:

SQL Saturday #486 Richmond – A Window Into Your Data

 

sqlsat486_web

Thanks for attending my session on window functions in TSQL. I hope you learned something you can take back and use in your projects or at your work. You will find an link to the session and code I used below. If you have any questions about the session post them in comments and I will try to get you the answers.

Questions

  1. Can an OVER clause be used in the WHERE clause?
    • No. The OVER clause can only be used in SELECT and ORDER BY clauses.
  2. Some follow up on ROWS and RANGE with context to CURRENT ROW.
    • We had a lot of discussion around this. In our examples below, RANGE aggregated all the data that fit into the ORDER BY clause. ROWS only referenced the row it was in. So, RANGE looks at everything that meets the criteria established by the PARTITION BY and ORDER BY clauses. ROWS is bound to the physical row.
    • Code examples:
      • OVER (PARTITION BY CustomerName ORDER BY OrderDate RANGE CURRENT ROW)
        • Summed two rows of data for the customer with the date. Both rows had the same date.
      • OVER (PARTITION BY CustomerName ORDER BY OrderDate ROWS CURRENT ROW)
        • Each row only contained the data for the row it was in.

Slides, Code, and Follow Up Posts

The presentation can be found here: A Window into Your Data

The code was put into a Word document that you can get here: TSQL Window Function Code

This session is also backed by an existing blog series I have written.

T-SQL Window Functions – Part 1- The OVER() Clause

T-SQL Window Functions – Part 2- Ranking Functions

T-SQL Window Functions – Part 3: Aggregate Functions

T-SQL Window Functions – Part 4- Analytic Functions

Microsoft Resources:

SQL Saturday #437–Boston BI Edition 2015–You Can Still Analyze Data with T-SQL

image3

Thanks for attending my session on analyzing data with TSQL. I hope you learned something you can take back and use in your projects or at your work. You will find an link to the session and code I used below. If you have any questions about the session post them in comments and I will try to get you the answers.

The presentation can be found here: Analyzing with TSQL

The code was put into a Word document that you can get here: Code to support the analysis with TSQL Sessions

This session is also backed by an existing blog series I have written.

T-SQL Window Functions – Part 1- The OVER() Clause

T-SQL Window Functions – Part 2- Ranking Functions

T-SQL Window Functions – Part 3: Aggregate Functions

T-SQL Window Functions – Part 4- Analytic Functions

Microsoft Resources:

SQL Saturday #453–Minnesota 2015–A Window Into Your Data

image

Thanks for attending my session on window functions in TSQL. I hope you learned something you can take back and use in your projects or at your work. You will find an link to the session and code I used below. If you have any questions about the session post them in comments and I will try to get you the answers.

The presentation can be found here: A Window into Your Data

The code was put into a Word document that you can get here: TSQL Window Function Code

This session is also backed by an existing blog series I have written.

T-SQL Window Functions – Part 1- The OVER() Clause

T-SQL Window Functions – Part 2- Ranking Functions

T-SQL Window Functions – Part 3: Aggregate Functions

T-SQL Window Functions – Part 4- Analytic Functions

Microsoft Resources: