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.
|
1 2 3 4 |
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.
|
1 2 3 4 5 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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