JSON Support in SQL Server 2016

Since JSON (JavaScript Object Notation) is such a popular and lightweight data interchange format, support for it has been one of the most requested SQL Server features. Microsoft has now added this support to SQL Server 2016. I’m currently using SQL Server 2016 Release Candidate (RC) 0 to experiment with the new JSON features, and if you want to as well, you can find the latest download of SQL Server 2016 here.

Storing JSON Data
SQL Server treats JSON as text, so  it can be stored as a regular NVARCHAR column. For example, let’s say that we have the following Person table, which has a StudentJson column to hold JSON data.

Unlike XML, which received a native data type, Microsoft decided to handle JSON as a string rather than add a native JSON data type. Whether or not their reasoning for this is justified is up to you, but one of the reasons given is the fact that many projects are already storing JSON as text, and using the new JSON functionality won’t require you to make any changes, schema or otherwise. Also, the many existing features and components that already support NVARCHAR can be used for JSON data as well (i.e. standard security policies, B-tree and full text indexes, and being able to use JSON as a parameter or return value). In addition, the thinking is that in most cases even if a JSON native data type was introduced, the JSON would probably be represented and parsed as text outside of SQL Server anyway.

Although JSON is simply being stored as a string, you can now add check constraints to your tables for your JSON columns to do things like ensure that they only contain valid JSON data and to perform more complex validation like ensuring that a particular property has a numeric value or is not NULL. For indexing, you could add a computed column for a particular value contained in the JSON and then place an index on the computed column.

In the JSON Functions section later in this post, we’ll discuss the new functions that have been introduced in SQL Server 2016 to support JSON, which can be used to create these types of check constraints and computed columns.

JSON Path Expressions
Some of the new JSON functions require a JSON path expression, which is used to reference properties of a JSON object. The path expressions use a dot-notation syntax. In the examples that follow, let’s use the following sample JSON:

At the beginning of path expressions, you can optionally declare a path mode of lax or strict (the default is lax if it is not specified). In lax mode, functions will return an empty value if the path expression contains an error. In strict mode, an error is raised if the expression contains an error.

After the path mode declaration, if any, you specify the path. The dollar sign ($) represents the context item, such as a JSON object. The dot operator (.) is used to reference a member of an object. For example, to reference the studentId from the sample JSON above, you would use the following:

$.studentId

If a key name starts with a dollar sign, contains special characters or contains a space, you have to surround it with double quotes. For example, if the property was “student Id” instead of “studentId”, you would have to specify the following:

$.”student Id”

Using dot notation, you can reference nested properties. For example:

$.enrollmentInfo.campusId

When referencing array elements, use brackets. Keep in mind that arrays are zero-based. The following would return the Business Administration concentration from the sample JSON above:

$.enrollmentIInfo.concentrations[1]

JSON Functions
Now that we are familiar with the format of JSON path expressions, let’s take a look at the new functions that have been added to provide support for handling and querying JSON data in a SQL Server database.

ISJSON
The ISJSON function can be used to test whether or not a string contains valid JSON. The format is as follows:

ISJSON (expression)

where expression is some expression, such as a variable or column that contains a JSON string, to test. The function returns a 1 if it is valid JSON, a zero if it is not, and NULL if the expression is NULL. It does not return any errors.

In the following example, the query only returns records that have valid JSON in the StudentJson column:

This function can also be used in a check constraint to ensure that a column contains valid JSON.

JSON_VALUE
This function extracts a scalar value from a JSON string. The format for this function is:

JSON_VALUE(expression, path)

where expression is some expression, such as a variable or column, that contains a JSON string and the path is a JSON path representing the property to extract.

The function returns a scalar value of type NVARCHAR(4000). If the value is greater than 4000 characters, in lax mode JSON_VALUE will return NULL. In strict mode, you will get an error.

The following example returns all of the studentId values:

You could also use this to obtain and validate a value as part of a check constraint. For example, this check constraint ensures that the studentId property is numeric:

The JSON_VALUE function can be used if you wanted to extract a value from some JSON to create a computed column that can be indexed. The following statement takes our Person table example from earlier, but now a computed column has been included to hold student Id values from the JSON:

JSON_QUERY
The JSON_QUERY function returns an object or an array from a JSON string. The format for this function is:

JSON_QUERY(expression, path)

where expression is some expression, such as a variable or column, that contains a JSON string and the path is a JSON path representing the object or array to return.

The function returns a JSON fragment of type NVARCHAR(max). If the value is not an object or an array, in lax mode it will return NULL and in strict mode it will return an error.

In the first query below, the enrollmentInfo object is returned, and in the second query, the concentrations array is returned.

Format Query Results as JSON/Export SQL Server Data as JSON
The FOR  JSON clause can be used to format query results as JSON or to export data from SQL Server to JSON. You can either structure the output explicitly (PATH mode), or you can allow automatic formatting based on the structure of the SELECT statement  (AUTO mode).

With PATH mode you can use the dot operator (.) to format data as JSON, and the optional ROOT option allows you to specify the root element. For example, let’s say you had a “CourseType” table with the following columns:

  • CourseTypeId  – INT
  • Code – NVARCHAR(25)
  • Description – NVARCHAR(100)

You can use PATH mode and the FOR JSON clause as follows:

This would yield the following result:

The following uses FOR JSON with AUTO mode:

The query results in the following:

In the preceding examples there is not too much of a difference between the two, but keep in mind that in PATH mode you can dictate any structure that you need.

Convert JSON to Rows/Columns or Import JSON into SQL Server
The OPENJSON rowset function is used to convert JSON into rows and columns or to import JSON data into SQL Server. You can call OPENJSON with the default schema or with an explicit schema.

The format for OPENJSON is:

OPENJSON(expression, [, path])

where expression is some expression, such as a string literal or variable that contains a JSON string. Path is optional, and when it is not provided it returns elements starting at the root of the JSON expression. You can provide a path if you want to start at a particular array or JSON object within the JSON expression.

Calling OPENJSON with the default schema returns a table with one row for each property of the object or for each element in the array.

The above query returns a table with 3 columns: key, value, and type:

Key Value Type
studentId 23245 2
status Active 1
enrollmentInfo { “enrollmentId”: 1, “campusId”: 3453 } 5

The Type value is an integer that represents the JSON data type of the value. The possible values are as follows:

Type Value JSON Data Type
0 null
1 string
2 int
3 boolean (true/false)
4 array
5 object

When OPENJSON is used with an explicit schema, a table is returned with the schema you define using a WITH clause. As part of the WITH clause you can dictate the columns, their types, and the property paths for each column.

StudentIdentifier SchoolStatus EnrollmentId CampusId
23245 Active 1 3453

Although we used the same JSON as when we called OPENJSON with the default schema, you can see that the results are now different and match the schema that was explictly set using the WITH clause. We no longer get a row for each property of the object and no type column is included since the data types of the columns have been explicitly set.

Wrapping Up
Support for JSON in Microsoft SQL Server 2016 is a welcome feature considering the popularity and widespread use of JSON. Unlike traditional relational database management systems and pure document databases, SQL Server 2016 provides a hybrid approach in which relational and JSON data can coexist. Systems can continue to benefit from the rich and powerful functionality provided by SQL Server (i.e. handling of transactions, replication support, reporting, tooling), while at the same time utilize the ability to handle and query JSON data.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Leave a Reply

Your email address will not be published. Required fields are marked *