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.
- 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