How SQL Server 2016 handles JSON

Inspired by a project I recently worked on, I decided to write a short post on a subject many people will find useful, that is how SQL Server 2016 handles JSON. This post shows also some simple examples of how SQL Server 2016 can query and extract values from JSON strings.

1 – About JSON

JSON (JavaScript Object Notation) is a data-interchange format. It can be easily read and written and can be easily parsed and generated by machines. JSON is a text format that is completely language independent and uses conventions programmers of the C-family are familiar with.

JSON is built on two structures:

• a collection of name/value pairs;
• an ordered list of values.

Most, if not all the modern programming languages support these data structures. Compared to XML, JSON is less verbose, smaller, simpler and someone claims better in CPU performance. Yet, while XML is a mark-up language, JSON is only a data format, they are often linked as they are typically used to accomplish the same task, data interchange.

2 – JSON in SQL Server 2016

This is in short what can be said about how SQL Server 2016 Community Technology Preview (CTP3) handles JSON.

– SQLS 2016 CTP3 offers built-in JSON support, but not native JSON data type and this is an important difference with XML. SQLS 2016 keeps storing JSON data as NVARCHAR or, more often, as NVARCHAR(MAX).

– While with SQLS 2016 CTP2 we were able just to format and export data as JSON string, with CTP3 we can also extract values from JSON text, load JSON text in tables, index properties in JSON text…

– Some useful functions: JSON_VALUE extracts a scalar value from a JSON string; JSON_QUERY instead extracts an object or an array; OPENJSON allows you to convert JSON data into a relational format (rows and columns) and, after that, you can query this data by using T-SQL, as SQL Server does not provide a custom query language for querying JSON; FOR JSON formats query results as JSON text.

3 – Some examples

SQL Server 2016 does not have custom JSON indexes but, however, for complex queries it is possible to optimize performances by using standard non-clustered indexes, full-text search indexes or creating indexed computed columns.

JSON_VALUE

The first example is very simple and shows the JSON_VALUE function extracting scalar values from within variable containing JSON text. The syntax is:

JSON_VALUE(expression, path)

where expression is the column/variable name and path specifies the path of the value to extract.

The variable has been set to NVARCHAR(MAX).

The query extracts the four values from the JSON text.

JSON_QUERY

When it comes to entire objects (in curly braces) or arrays (in square brackets), the right function is JSON_QUERY. The syntax is:

JSON_QUERY(expression, path)

where expression is the column/variable name and path specifies the path of the object or array to extract. If the value is not an object or an array, JSON_QUERY returns NULL.

In this example we extract the entire array contained in the column.

The query returns 31465 rows each one containing the JSON array.

OPENJSON

OPENJSON can be considered as a table-valued function able to parse JSON text and to return objects and properties in JSON as rows and columns.

For instance, let’s use OPENJSON to transform a JSON string into a relation format.

SQL Server 2016 will return four rows, one row for each property, and three columns (key, value, type). The type column will change depending on the type (string, number, Boolean, array, object).

SQL Server 2016 allows you also to import content from JSON files into relational tables combining OPENJSON and OPENROWSET.

FOR JSON

This function enables you to export data from SQL Server as JSON or to format result of a query as JSON. There are two modes, very similar to XML:

– FOR JSON PATH, with full control over the output format;

– FOR JSON AUTO, automatically formatted.

4 – Conclusion

Compared to the past, with SQL Server 2016 CTP3 now it is a lot easier to deal with JSON, a format that keeps increasing its popularity. The tools offered by SQL Server 2016 allow us to work with JSON without thinking too often that this is not a native data type as it happens in other DBMSs (PostgreSQL). A lot has been done, a few relevant improvements have been provided, the features are relatively easy to use and then, being positive, it is better than no support at all.