Esat Erkec
How to read a file of JSON in SQL Server with OPENJSON function

How to parse JSON in SQL Server

September 15, 2020 by

In this article, we will learn how to parse and query JSON in SQL Server with the help of the OPENJSON function. Firstly, we will briefly look at the data structure of the JSON and then we will learn details of the parsing and querying JSON data using the OPENJSON function.

What is JSON?

JSON (Java Script Object Notation) is a lightweight text-based data format that helps to interchange data easily between modern applications. At the same time, JSON is used by the NoSQL (Microsoft Azure Cosmos DB, CouchDB, etc.) databases to store the unstructured data. The small data size is the main feature of the JSON and this property enables to an interchange of the data easily between the applications. At the same time, JSON offers the following advantages:

  • The JSON data structure is easily readable by humans and machines
  • JSON has a compact data structure and it does not include unnecessary data notations
  • JSON has extensive usage. All modern programming languages and application platforms support working with JSON
  • JSON has a straightforward syntax

The JSON data structure is essentially based on a key-value pair format. The keys must be string data type and the values data types must be in JSON data type. JSON supports the following data types :

  • string
  • number
  • boolean
  • null
  • object
  • array

At the same time, a JSON object can contain two different data structures:

  • An object type can contain multiple key-value pairs inside the JSON text
  • An array can contain single or multiple values

The following JSON represents some attributes of a car brand. The color attribute represents an array and the Model attribute represents an object in this JSON object.

OPENJSON() function parses JSON in SQL Server

As we mentioned in the previous section, JSON is used widely by the applications. In this context, the conversion of the JSON data into the relational format is becoming more important.

OPENJSON is a table-valued function that helps to parse JSON in SQL Server and it returns the data values and types of the JSON text in a table format. Now, we will look at the syntax of this function.

The jsonExpression is an input parameter that specifies the JSON text that will be parsed by the OPENJSON function.

The jsonPath is an optional parameter and it is used to seek a specified JSON expression in the JSON text and the OPENJSON function parses only this part of the JSON text.

The WITH clause is an optional parameter that can be used to explicitly specifies the schema.

In the following example, we can see a very simple usage of the OPENJSON function. It will parse JSON in SQL Server and return the parsed JSON in the default schema format.

Parsing JSON data with help of the OPENJSON function

In this result set, we can observe that the OPENJSON function is executed with the default schema and it has returned three columns:

  • The key column indicates the name of the key
  • The value column shows the value of the key
  • The type column indicates the data types of the key column through the numbers. The following table illustrates the possible values of the type column and their data type explanations

Type column

JSON data type

0

null

1

string

2

int

3

true/false

4

array

5

object

  • Tip: OPENJSON function does not work in the databases with compatibility level less than 130 and returns the following error. So we can not use the OPENJSON function to parse JSON in SQL Server

The error message can return because of the database compatibility

Using OPENJSON with explicit schema

The returning columns of the JSON result set can be defined by the users. In this usage method, we need to specify output columns and their types and then we can pass this user-defined schema to OPENJSON through WITH keyword.

Parsing JSON in SQL using the user-defined schema.

We need to use AS JSON keyword to specify JSON objects or arrays which are contained in the JSON text. In addition, the column data type must be NVARCHAR(MAX).

AS JSON keyword usage details when parsing JSON in SQL Server

How to parse the JSON arrays with OPENJSON?

JSON arrays are used to store multiple key values. The JSON arrays are specified with square brackets ([ ]) in the JSON text and values are separated with a comma. When we parse a JSON text which includes an array through OPENJSON, the result set will look like as below.

Parsing array of the JSON in SQL

As we can see, the OPENJSON function has parsed the root level objects but the array has returned as JSON text. When we want to convert this JSON array data into the relational format, we need to use the OPENJSON function again and join to the root level table.

CROSS APPLY join type usage details

In another way, we can use the jsonPath parameter to covert the JSON array into a relational format.

jsonPath parameter usage details

  • Tip: The lax and strict are the two JSON path mode. In the lax mode, if the specified JSON path expression does not find in the JSON text it does not return an error and it is the default path mode. For example, there is not any Unknown attribute in the JSON text but the OPENJSON function does not return any error

lax path mode usage detail

In the strict mode, if the specified JSON path expression does not find in the JSON text it returns an error.

strict path mode usage detail

How to parse a JSON file with OPENJSON?

OPENROWSET function is used to read data from files from the file system. The below query will read the JSON file contents in the specified file path.

How to read a file of  JSON in SQL Server with OPENJSON function

Now, we will convert the JSON data to the relational format with the help of the OPENJSON function.

Manipulating JSON in SQL Server

How to call JSON API in SQL Server?

The JSON API enables us to communicate between two application platforms in the JSON data format. These API’s results are returns in the JSON format and accept the data that’s formatted as JSON. Such as, this JSON API returns weather data information for London.

How to parse and query JSON in SQL

In order to communicate any JSON API from the SQL Server, we can use OLE Automation Stored Procedures. These procedures provides to access OLE or COM objects directly over SQL Server. By default OLE Automation Stored Procedures are disabled and we need to enable them.

The following query will get weather information result from the HTTP server and then convert the JSON text into the table.

How to call an JSON API in SQL Server

Now, we will explain the above query line by line.

As a first step, we declared the required variables and create an instance of the WinHTTP object with the help of the sp_OACreate procedure.

In this part of the query sp_OAMethod procedure opened an HTTP connection and sent an HTTP request to the server. ResponseText method retrieved the response of the web server as a text. As a last, the sp_OADestroy procedure destroyed the created instance of the object.

Before parsing the HTTP server result, we checked that the response text is a valid JSON format. The ISJSON function controls whether an expression is valid JSON text. It returns 1 if the input expression is a valid JSON otherwise it returns 0.

In the final part of the query, we used the OPENJSON function to parse the returned JSON text. The city name is stored in the name attribute so we defined a schema to obtain this value. The second schema parsed the weather array and got the description value from this array.

Conclusion

In this article, we explored the JSON notion and learned the data structure of this object. We also explored parse JSON in SQL Server through the OPENJSON function. JSON is used by modern applications to exchange data and store unstructured data. On the other hand, relational databases are an indispensable part of our software development life cycle. .JSON is not an alternative for the relational database model but it has a schema-less data structure that makes it more flexible to store unstructured data and combining this data model to relational data called a hybrid data model. All these features help in storing and processing both relational and JSON data by using T-SQL and this hybrid model opens a gate of the new data age.

Esat Erkec
Latest posts by Esat Erkec (see all)
Functions, JSON

About Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert. Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn. View all posts by Esat Erkec

14,346 Views