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

Самый простой вариант – это использовать FOR JSON AUTO. В этом случае будет сгенерирован массив JSON объектов, где каждая строка в выборке будет отдельным объектом, а колонка свойством.

use tempdb;
go

drop table if exists dbo.test_table;
go

create table dbo.test_table (
	id int not null,
	name varchar(100) null,
	dt datetime null
);
go

insert into dbo.test_table (id, name, dt)
values
	(1, 'qwe', '19000101'),
	(2, 'asd', null),
	(3, null, '20000101');
go

select id, name, dt
from dbo.test_table
for json auto;
go

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}]

Как мы видим, null значения игнорируются. Если мы хотим их включать в JSON, то можем воспользоваться дополнительной опцией INCLUDE_NULL_VALUES.

select id, name, dt
from dbo.test_table
for json auto, include_null_values;
go

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”,”dt”:null},

{“id”:3,”name”:null,”dt”:”2000-01-01T00:00:00″}]

Опция WITHOUT_ARRAY_WRAPPER поможет, если мы хотим вывести JSON без квадратных скобок.

select id, name, dt
from dbo.test_table
for json auto, without_array_wrapper;
go

{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}

Если же мы хотим объединить результаты с корневым элементом, то необходимо воспользоваться опцией ROOT и передать ей нужное название.

select id, name, dt
from dbo.test_table
for json auto, root('root');
go

{“root”:

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}]

}

Ну и наконец, если мы хотим создать JSON с более сложной структурой, самим присвоить нужные название свойствам, сгруппировать их, то необходимо использовать выражение FOR JSON PATH. Я рекомендую обратиться к первой части цикла статей про JSON, чтобы узнать поподробнее про path выражения для JSON. Ниже я приведу просто пример запроса, который будет генерировать JSON с более сложной структурой.

select
	id,
	name as 'data.full_name',
	dt as 'data.add date'
from dbo.test_table
for json path;
go

[{“id”:1,”data”:{“full_name”:”qwe”,”add date”:”1900-01-01T00:00:00″}},

{“id”:2,”data”:{“full_name”:”asd”}},

{“id”:3,”data”:{“add date”:”2000-01-01T00:00:00″}}]

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