Hadi Fadlallah
Using OPENJSON() function with explicit schema

SQL Server JSON functions: a bridge between NoSQL and relational worlds

April 22, 2020 by

In this article, we will explain what JSON is, what are the SQL Server JSON functions introduced in 2016, how to index JSON values, and how to import JSON data into SQL tables.

Introduction

JavaScript Object Notation (JSON) is an open universal data format that uses human-readable text to store data objects in a map format (Key/value pairs). This object notation was standardized in 2013 as ECMA-404. Many years ago, JSON was not popular, while XML was widely used in the data exchange operations. With the rise of the Big Data and NoSQL notions, JSON started slowly to replace XML since it is used and supported by the new data technologies.

Since companies and data consumers widely use traditional database management systems, these systems’ providers started supporting the new data technologies and features to keep pace with their customers emerging needs.

Regarding SQL Server, starting with the 2012 release, the database engine is not considered anymore for medium scale enterprises after adding high-end data-center management capabilities. In the 2016 version, Polybase was introduced, adding the ability to connect traditional databases with NoSQL databases and Hadoop data lakes (more capabilities were introduced in 2019 such as MongoDB and Oracle database support). Besides, JSON becomes supported, enabling developers to combine NoSQL and relational concepts by storing documents formatted as JSON text within tables storing traditional data.

In the following sections, we will explain SQL Server JSON functions and how to index JSON values and import JSON data into SQL.

SQL Server JSON functions

Based on the official Microsoft documentation, the added JSON functionalities allows developers to:

  1. Parse JSON text and read or modify values
  2. Transform arrays of JSON objects into table format
  3. Run any Transact-SQL query on the converted JSON objects
  4. Format the results of Transact-SQL queries in JSON format

In this section, we will explain each functionality added by providing some examples using the AdventureWorks database. Before describing these functionalities, we will create a new field of type NVARCHAR(MAX) within the [Person].[Person] table to be used during this guide.

FOR JSON clause

This clause is used to convert tabular data into a JSON text. There are two options to be used within this clause:

  1. FOR JSON PATH: to format and configure the output JSON text manually
  2. FOR JSON AUTO: to format the output JSON text automatically

In this guide, we will use the second option to generate JSON text. As an example, let’s convert a record from the Person table to a JSON text:

The result will be shown as following:

using FOR JSON AUTO to generate JSON from an SQL Statement

Figure 1 – Using FOR JSON AUTO to generate JSON text

To continue our experiments, we will fill the newly added NVARCHAR(MAX) column (called Details) with the JSON text created from the other columns using the following query:

If you decided to go with the first option we mentioned (FOR JSON PATH), you can refer to the following official documentation.

ISJSON() function

ISJSON() is the simplest SQL Server JSON function; it checks if a given string is a valid JSON text. It returns 1 if the string is valid, else it returns 0.

As an example, we will use check if FirstName and Details (the added NVARCHAR(MAX) column) columns contain a valid JSON text:

As shown in the image below, FirstName does not contain a valid JSON while Details does.

using ISJSON() (SQL Server JSON function) to check if a column value is JSON

Figure 2 – Using ISJSON() function to validate columns

JSON_VALUE() function

To extract a scalar value from a JSON text, we can use JSON_VALUE() function. This SQL Server JSON function takes the input JSON string and the JSON path that contains the property to extract as arguments. (Note that the context item of a JSON path is a dollar sign ($).)

In the following example, we used JSON_VALUE() function to filter the query result where the BusinessEntityID value stored within the first array of the JSON text is equal to 1:

As shown below, the query returns only one row:

Using JSON_VALUE() (SQL Server Json function) to search for a property value within a JSON string

Figure 3 – Using JSON_VALUE() function to filter a query result

More examples of the JSON_VALUE() function can be found in the official documentation.

Besides, if you are not familiar with JSON PATH expressions, you can refer to the following article.

JSON_QUERY() function

To extract an array or object from a JSON text, we must use JSON_QUERY() function. This SQL Server JSON function is similar to JSON_VALUE(); it takes the same arguments (input JSON and path) and returns a JSON text (NVARCHAR(MAX)) value.

As an example, let’s return the items stored within the first array in the root element:

As shown in the image below, the result is a JSON string.

Using JSON_QUERY() (SQL Server Json function) to search for an object within a JSON string

Figure 4 – Using JSON_QUERY() to extract JSON objects

JSON_MODIFY() function

This function modifies the value of a property within a JSON string and returns the updated JSON text. It takes three parameters; the input JSON string, the property JSON path, and the new value. As an example, If we are looking to add a leading zero to the BusinessEntityID property stored within the Details column. We should use the following query:

Now if we execute the query we provided in the JSON_VALUE() function section, it will not retun any result until we add a leading zero to the condition:

The following image shows the result of this query:

validating the update query using JSON_VALUE() function

Figure 5 – Query result after modifying the JSON text value

OPENJSON() function

If you are looking to parse a JSON text into a key-value pairs or a tabular data, you can use OPENJSON() table-valued function. To convert a JSON string to a key-value pair we must use OPENJSON() function as follows:

The result of this query is a key-value pair of all JSON array objects in addition to the value type:

Using OPENJSON() to retrun a jey-value pairs

Figure 6 – Using OPENJSON() to retrun a jey-value pairs

The following JSON value types are supported:

Type code

Type description

0

null

1

string

2

int

3

true/false

4

array

5

object

If we know the JSON text schema, we can use OPENJSON() function to convert JSON into tabular data. We need to define the schema explicitly after this SQL Server JSON function. As an example:

As shown in the image below, the query result is in tabular form.

Using OPENJSON() function with explicit schema

Figure 7 – Using OPENJSON() function with explicit schema

Indexing JSON column

If we need to filter our queries based on a property value within the JSON column, and we need that this property is indexed, we should add a computed column based on this value. Then we must create an index for this column. As an example:

Import JSON data into SQL database

There are many methods to import JSON data into SQL database:

  • Using Integration Services
  • Using OPENROWSET() with OPENJSON() function

These methods are described in detail in this article, Import JSON data into SQL Server.

Conclusion

In this article, we have illustrated the SQL Server JSON functions added with the 2016 version. We provided some examples using the AdventureWorks database. We explained how to index properties stored within the JSON column, and finally, we briefly noted the available methods to import JSON into the SQL database.

Hadi Fadlallah
JSON, T-SQL

About Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies. Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional. On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques. Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website. View all posts by Hadi Fadlallah

168 Views