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

Total: 1 Average: 4

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.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":32,"skills":
["SQL Server 2016","T-SQL","JSON"],"additional info":{"data1":1,"data2":2}}';
 
select * from openjson(@json);

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.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":32,"skills":
["SQL Server 2016","T-SQL","JSON"],"additional info":{"data1":1,"data2":2}}';
 
select * from openjson(@json, '$.skills');
select * from openjson(@json, 'strict$."additional info"');

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.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":32,"skills":
["SQL Server 2016","T-SQL","JSON"],"additional info":{"data1":1,"data2":2}}';
 
select *
from openjson(@json)
with (
	fisrt_name varchar(100) '$."first name"',
	last_name varchar(100) '$."last name"',
	age tinyint '$.age',
	skill1 varchar(50) '$.skills[0]',
	skill2 varchar(50) '$.skills[1]',
	data1 varchar(50) '$."additional info".data1'
);

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.

declare @json varchar(max) = '[
	{"first name":"Sergey","last name":"Olontsev","age":32,"skills":
["SQL Server 2016","T-SQL","JSON"],"additional info":{"data1":1,"data2":2}},
	{"first name":"John","last name":"Smith","age":18,"skills":
["SQL Server 2014","In-Memory OLTP"],"additional info":{"data2":4}}
]';
 
select *
from openjson(@json)
with (
	fisrt_name varchar(100) '$."first name"',
	last_name varchar(100) '$."last name"',
	age tinyint '$.age',
	skill1 varchar(50) '$.skills[0]',
	skill2 varchar(50) '$.skills[1]',
	data1 varchar(50) '$."additional info".data1'
);

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