Это статья является продолжением серии про работу с JSON в SQL Server 2016. В предыдущей части я показал, как можно из реляционных данных сгенерировать JSON. В этой мы рассмотрим обратную операцию, как JSON преобразовать в реляционную структуру. Опять же, если вы уже когда-либо работали с XML в SQL Server, то здесь будет похожий принцип: для этой операции используется функция OPENJSON.

Сразу стоит отметить, что OPENJSON будет работать только в базах данных с уровнем совместимости 130.

Существует 2 режима работы функции OPENSON. Самый простой способ – это без указания схемы для результирующей выборки.

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);

В данном случае результат будет представлен в виде трех столбцов: key, value и type. Соответственно в первом будет выведено название свойства, во втором его значение, а в последнем – тип. Т.к. JSON в приведенном выше примере иерархический, да еще и с массивом для одного из свойств, то функция OPENJSON просто вытащила все свойства с первого уровня и вывела их в виде списка. Мы также можем вытащить отдельно все элементы массива или же свойства с нужного нам уровня документа указав необходимый путь.

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"');

Данные в столбце type могут принимать следующие значения.

Во втором режиме работы функции OPENJSON вы можете сами описать, как будет выглядеть возвращаемый результат: названия столбцов, их количество, откуда из 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'
);

Если же у вас в 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}},
	{"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'
);

Вот и все про то, как можно выбрать данные из JSON объекта и представить их в виде реляционной структуры. В следующей статье я опишу, как можно изменять JSON документы.