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:
| Value | JSON data type |
|---|---|
| 0 | null |
| 1 | string |
| 2 | int |
| 3 | true/false |
| 4 | array |
| 5 | object |
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
Latest posts by Sergey Olontsev (see all)
- SQL Server 2016: JSON, Part 6 – JSON vs XML Performance Comparison - May 12, 2017
- SQL Server 2016: JSON, Part 5 – Storage and Indexation - May 11, 2017
- SQL Server 2016: JSON, Part 4 – Data Modification - May 8, 2017