SQL Server 2016: JSON, Part 3 – Transformation into Relational Structure

In my previous publication, I described how you could generate JSON from relational data. In this article, we will explore the reverse operation: how it is possible to transform JSON into a relational structure. To do this, you can use the OPENJSON statement.

It should be noted that OPENJSON will be working only in databases with the 130-compatibility level.

There are two modes of the OPENJSON statement. The easiest one is to use it without specifying a scheme for the target selection.

In this case, it will return the result in three columns: key, value, and type. Thus, the first column will show the property name, the second one – its value, and the last one – its type. As in our example, JSON has a hierarchical structure with an array for one of the properties, then the OPENJSON statement retrieves all the properties from the first level and outputs them as a list. In addition, we can retrieve all the array components or their properties from the necessary level separately by specifying the path.

The data in the table can have the following values:

ValueJSON data type
0null
1string
2int
3true/false
4array
5object

In the second mode of the OPENJSON statement, you can describe what the target result will be: column names, the number, and where to obtain values from JSON.

If there are several objects in JSON, then a separate row will be generated for each object, as well as column values will be selected by corresponding paths.

That is all about how you can select data from the JSON object and transform it into the relational structure. In my next publication, I will describe the ways of modifying JSON files.

Sergey Olontsev

Sergey Olontsev

Software Expert at Kaspersky Lab
Sergey has been working with SQL Server for more than a decade as a database administrator, developer, consultant, and trainer, focusing on high availability and disaster recovery solutions, ETL, developing high-performance solutions, troubleshooting and performance tuning. He is an owner of prestigious Microsoft Certified Master certification on SQL Server and MVP Data Platform award and regularly speaks at various SQL Server user group meetings, SQL Saturday events and other conferences worldwide.
Sergey Olontsev

Sergey Olontsev

Sergey has been working with SQL Server for more than a decade as a database administrator, developer, consultant, and trainer, focusing on high availability and disaster recovery solutions, ETL, developing high-performance solutions, troubleshooting and performance tuning. He is an owner of prestigious Microsoft Certified Master certification on SQL Server and MVP Data Platform award and regularly speaks at various SQL Server user group meetings, SQL Saturday events and other conferences worldwide.