Getting Started with JSON Support in SQL Server 2016 – Part 2

Introduction

SQL Server 2005 introduced native support for storing, managing and processing XML data when XML had become de-facto standard for data interchange. This feature still exists in SQL Server as XML has some inherent advantages and is still widely used but because many new applications have started using JSON for a light-weight data interchange mechanism, SQL Server 2016 introduces built-in support for storing, managing and processing JSON data. In my earlier article, I talked about JSON support in SQL Server 2016, how to format or convert tabular data to JSON format using the FOR JSON clause. We also looked at different variants of FOR JSON, to use AUTO to automatically get the structure of the JSON data based on the order of source tables and columns in the query or by using PATH to have full control in specifying the output structure of the JSON data.

In this article, I will talk about using OPENJSON to read and parse JSON data, convert it to tabular format, and different, newly introduced functions to work with JSON data.

Importing JSON Data as Tabular Data

In my earlier article, I talked about how you can transform your tabular data to a JSON document so that it can be passed to applications expecting JSON documents. We also talked about different ways for formatting JSON output before SQL Server returns it to the client.

SQL Server 2016 also includes OPENJSON table-valued row-set function to scan through JSON data and convert it to tabular (rows and columns) format. This becomes quite useful when you want to store it in SQL Server in relational format, do reporting on tabular data or pass on tabular data (converted from JSON) to applications that expect tabular data.

There are two ways to call the OPENJSON function:

• Calling OPENJSON with default schema– this way it returns a table with one row for each property of the object or for each element in the array with three columns as mentioned below:
  • Key – represents the property name of the specified property or the index of the element in the specified array
  • Value – represents the value of the property within the object or value of an element of the array specified by the index key
  • Type – represents the JSON data type of the value returned as part of the value column

DECLARE   @JSONText NVARCHAR(MAX)
SET   @JSONText = N'{
              "Order":{
                     "OrderID":43663,
                     "Status":5,
                     "PONumber":"PO18009186470"
                     },
              "Product":{
                     "ShipDate":"2011-06-07T00:00:00",
                     "ProductID":760
                     }
       }'
 
SELECT * FROM OPENJSON(@JSONText);
SELECT * FROM OPENJSON(@JSONText, '$.Order');
SELECT * FROM OPENJSON(@JSONText, '$.Product');
 

Calling OPENJSON with default schema

As you can see with the above script and result, the first query takes a JSON data as an argument to the OPENJSON function and returns the result-set with three columns, viz. Key, Value and Type (JSON data type) for the value for each property of the object or for each element in the array. The possible values for Type column are:

You can a use second parameter of the OPENJSON function to specify the path within the JSON data to pick the values from; for example in the second SELECT statement in the above query, I specified Order property (whose value is another object with three properties) and hence result shows values of these properties under Order property. Likewise, third SELECT statement in the above query, I specified Product property (whose value is another object with two properties) and hence the result shows values for these properties under Product property.

In the next example given below, I have slightly different JSON data, this time I have one object with a single property called Order, which contains another object. This object further contains five properties; four of these properties contain scalar value whereas the fifth one, called Product, contains an array of ProductID. To access these elements of the array, you need to specify the index as shown in the fourth and fifth SELECT statements.

DECLARE   @JSONText NVARCHAR(MAX)
SET   @JSONText = N'{
              "Order":{
                     "SalesOrderID":43687,
                     "Status":5,
                     "PurchaseOrderNumber":"PO4959110829",
                     "ShipDate":"2011-06-07T00:00:00",
                     "Product":[
                           {"ProductID":768},
                           {"ProductID":765}
                           ]
              }
       }'
 
SELECT * FROM OPENJSON(@JSONText);
SELECT * FROM OPENJSON(@JSONText, '$.Order');
SELECT * FROM OPENJSON(@JSONText, '$.Order.Product');
SELECT * FROM OPENJSON(@JSONText, '$.Order.Product[0]');
SELECT * FROM OPENJSON(@JSONText, '$.Order.Product[1]');

Specify the path within the JSON data

• Calling OPENJSON with an explicit schema – this way it returns a table with the schema that you define in the WITH clause, which allows you to specify the output columns, their data types, and the paths of the source properties for each of the columns being returned.

What we saw earlier is, we can read from a JSON document with default schema, which gives us information back in three columns. This might be good when analyzing data but in most cases, you would like to get data in a tabular format as you get it when you query from a relational table. To do that while reading JSON data, you can explicitly specify the schema with the WITH clause and read the data by parsing it completely in tabular format. The example below demonstrates the usage of the WITH clause, how you pick and parse properties from a JSON document by specifying the source path, rename the column if needed and change its data type while returning it back as a tabular result-set:

DECLARE   @JSONText NVARCHAR(MAX)
SET   @JSONText = N'{
              "Order":{
                     "SalesOrderID":43663,
                     "Status":5,
                     "PurchaseOrderNumber":"PO18009186470"
                     },
              "Product":{
                     "ShipDate":"2011-06-07T00:00:00",
                     "ProductID":760
                     }
       }'
 
SELECT * FROM OPENJSON(@JSONText, '$')
WITH(OrderID   INT '$.Order.SalesOrderID', 
    ShipmentStatus INT '$.Order.Status', 
    PONumber NVARCHAR(25) '$.Order.PurchaseOrderNumber', 
    ShipmentDate DATE '$.Product.ShipDate', 
    ProductID INT '$.Product.ProductID');
 

Calling OPENJSON with an explicit schema

Other JSON Functions Available in SQL Server 2016

Apart from the above mentioned OPENJSON table-valued function, SQL Server includes three more functions to work with JSON data as mentioned below:

ISJSON

We saw earlier how to parse a JSON data or document with the OPENJSON table-valued function; this works only as long as JSON data is valid and correctly formatted, if not the query will fail. To avoid such a scenario, you can use the ISJSON function to check if the JSON data is valid or not. It returns 1 if the input JSON data is valid or else it returns 0. Also, it returns NULL in cases when the input value itself is NULL. With this function, you can check the validity of the JSON data before proceeding further in the process.

JSON_VALUE

We looked at the OPENJSON function to convert JSON data to tabular format, but sometimes you might need to just pull out one scalar value from the JSON data instead of parsing and returning complete data from the JSON document. This is where you can use the JSON_VALUE function. It takes JSON data as its first argument and path that specifies the property to extract from that given JSON data as a second argument and returns a scalar value up to 4000 characters. The example below shows accessing a value for a property by specifying a path in the JSON document with the JSON_VALUE function.

DECLARE   @JSONText NVARCHAR(MAX)
SET   @JSONText = N'{
              "Order":{
                     "OrderID":43663,
                     "Status":5,
                     "PONumber":"PO18009186470"
                     },
              "Product":{
                     "ShipDate":"2011-06-07T00:00:00",
                     "ProductID":760
                     }
       }'
SELECT JSON_VALUE(@JSONText, '$.Order.PONumber');

JSON_VALUE

JSON_QUERY

The JSON_QUERY function is very similar to the JSON_VALUE function, which we saw earlier. The basic difference between these two are, the JSON_VALUE function returns a scalar value whereas JSON_QUERY returns an object or an array from the JSON data. The example below shows how to return an object from the JSON data by explicitly specifying a path in the source JSON data.

DECLARE   @JSONText NVARCHAR(MAX)
SET   @JSONText = N'{
              "Order":{
                     "OrderID":43663,
                     "Status":5,
                     "PONumber":"PO18009186470"
                     },
              "Product":{
                     "ShipDate":"2011-06-07T00:00:00",
                     "ProductID":760
                     }
       }'
SELECT JSON_QUERY(@JSONText, '$.Product');
 

JSON_QUERY

Indexes on JSON Data

Unlike XML, where you have custom index types, SQL Server 2016 does not have custom JSON indexes. But as JSON data is stored as NVARCHAR, you can create standard indexes to optimize the performance.

For example, you can create a non-persisted computed column based on values from a property in the JSON document (you can use the JSON_VALUE function as discussed above to get data for the computed column) and then create the required indexes on it to optimize the performance. You can learn more about indexing JSON data here.

Note:

1. When specifying a path for the functions discussed above, there are two modes to specify the path in the JSON document. The first one is lax, also the default, which means if the specified path does not exist it returns NULL whereas the second one is strict, which means the query should throw an error in cases when the path is not available.
2. When you encounter Invalid object name ‘OPENJSON’ exception on CTP 3.2 or later, you need to check the compatibility level of the database; it must be 130.
3. The feature mentioned and demonstrated in this article is based on SQL Server 2016 CTP 3.2 and might change when RTM is available or in future releases.

Conclusion

SQL Server 2016 introduces built-in support for storing, managing and parsing JSON data. In this article I discussed using the OPENJSON function to read and parse JSON data, and convert it to tabular format. I also demonstrated usage of different newly introduced functions to work with JSON data, etc.

Resources

JSON

JSON Data in SQL Server 2016

OPENJSON function

What’s New in SQL Server 2016

See all articles by Arshad Ali