SQL Server 2016: JSON, часть 6, сравнение производительности с XML

Мы подошли, пожалуй, к самой интересной части серии статей про работу с JSON в SQL Server 2016. Я уже упоминал, что разработчики полюбили JSON за его ясную и понятную глазу структуру, а также за меньший размер данных, чем XML. Осталось проверить самое главное, работает ли SQL Server с JSON быстрее, чем с XML. Для этого я написал небольшой тест на замер скорости работы функции JSON_VALUE и метода value() у типа XML. Для JSON я решил проверить, как работает извлечение данных как из типа varchar(max), так и из nvarchar(max). Я составил похожие по структуре JSON и XML и попробую выбирать из них разные типы данных, числовой и строковый и из разных частей документов. Ну и давайте посмотрим, что же получилось.

declare @json varchar(max) = '[
	{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"]},
	{"first name":"John","last name":"Smith","sex":"m","skills":["SQL Server 2014","In-Memory OLTP"]},
	{"first name":"Mary","last name":"Brown","age":25,"skills":["SQL Server 2016","In-Memory OLTP"]}]';
 
declare @json_u nvarchar(max) = N'[
	{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"]},
	{"first name":"John","last name":"Smith","sex":"m","skills":["SQL Server 2014","In-Memory OLTP"]},
	{"first name":"Mary","last name":"Brown","age":25,"skills":["SQL Server 2016","In-Memory OLTP"]}]';
 
declare @xml xml = N'
	32SQL Server 2016T-SQLJSON
	mSQL Server 2014In-Memory OLTP
	25SQL Server 2016In-Memory OLTP
';
 
declare
	@i int,
	@v1 int,
	@v2 varchar(100),
	@start_time datetime,
	@end_time datetime,
	@iterations int = 1000000,
	@path_expression nvarchar(1000),
	@returned_type varchar(100);
 
declare @results table (
	data_type varchar(100) not null,
	test_id tinyint not null,
	path_expression varchar(1000) not null,
	returned_type varchar(1000),
	elapsed_time_ms int not null
);
 
 
set @returned_type = 'int';
set @path_expression = '$[0].age'
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v1 = json_value(@json, '$[0].age');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json', 1, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '$[2]."first name"';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = json_value(@json, '$[2]."first name"');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json', 2, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '$[2].skills[0]';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = json_value(@json, '$[2].skills[0]');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json', 3, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'int';
set @path_expression = '$[0].age'
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v1 = json_value(@json_u, '$[0].age');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json u', 1, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '$[2]."first name"';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = json_value(@json_u, '$[2]."first name"');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json u', 2, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '$[2].skills[0]';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = json_value(@json_u, '$[2].skills[0]');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json u', 3, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'int';
set @path_expression = '(/root/rec/age)[1]';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v1 = @xml.value('(/root/rec/age)[1]', 'int');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'xml', 1, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '(/root/rec/@first_name)[3]';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = @xml.value('(/root/rec/@first_name)[3]', 'varchar(100)');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'xml', 2, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '/root[1]/rec[3]/skills[1]/skill[1]';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = @xml.value('/root[1]/rec[3]/skills[1]/skill[1]', 'varchar(100)');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'xml', 3, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
select *
from @results;

Результаты, если честно, удивили меня самого. В первую очередь стоит отметить, что выбор данных из JSON, хранящегося в типе nvarchar(max), проиходит быстрее на 5-15%, чем из обычного типа без поддержки Unicode. Хотя, должно было бы быть наоборот, т.к. этот тип занимает в 2 раза больше. Но результаты теста опровергают догадки. Выходит, что выгоднее обрабатывать JSON, который хранится в unicode формате. С чем это связано, мне пока не ясно. Ну и, что особенно радует, извлечение данных из JSON происходит от 2-3 до почти 10 раз быстрее, чем из XML. Поэтому можно смело рекомендовать использовать JSON вместо XML там, где это возможно.

SQL Server 2016: JSON, часть 5, хранение и индексирование

Продолжаем серию статей про поддержку JSON в SQL Server 2016. В этой части мы рассмотрим, как можно хранить и индексировать JSON объекты в базе данных.

Как я уже раньше упоминал, в SQL Server 2016 не был добавлен отдельный тип для JSON, в отличие от XML. Поэтому для его хранения вы можете использовать любые строковые типы данных. Лучше всего, пожалуй, подойдут varchar(max) и nvarchar(max) в зависимости от того, есть ли у вас Unicode символы в JSON документах или нет. Однако, вы можете использовать типы данных с меньше длиной, если вы точно уверены, что ваши JSON объекты не выйдут по размеру за их пределы.

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

use test;
go
 
drop table if exists dbo.test_table;
go
 
create table dbo.test_table (
	id int not null,
	json_data varchar(max) null,
 
	constraint pk_test_table primary key clustered (id)
);
go
 
insert into dbo.test_table (
	id,
	json_data
)
values
	(1, '{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"]}'),
	(2, '{"first name":"John","last name":"Smith","sex":"m","skills":["SQL Server 2014","In-Memory OLTP"]}'),
	(3, '{"first name":"Mary","last name":"Brown","age":25,"skills":["SQL Server 2016","In-Memory OLTP"]}');
go
 
alter table dbo.test_table
add v_age as json_value(json_data, '$.age') persisted;
go
 
alter table dbo.test_table
add v_skills as json_query(json_data, '$.skills') persisted;
go
 
 
create nonclustered index ix_nc_test_table_v_age on [dbo].[test_table] (v_age);
go
 
create fulltext catalog [jsonFullTextCatalog] with accent_sensitivity = on authorization [dbo];
go
 
create fulltext index on [dbo].[test_table] (v_skills)
key index pk_test_table ON jsonFullTextCatalog;
go
select *
from [dbo].[test_table] as t
where
	t.[v_age] = 32;
 
select *
from [dbo].[test_table] as t
where
	contains(t.v_skills, 'OLTP');

Вычисляемые столбцы лучше создать как persisted, иначе теряется весь смысл в индексировании этих столбцов. Также, можно создавать как обычные индексы, так и полнотекстовые, если мы хотим получить чуть более гибкий поиск по содержимому массивов или целых частей объектов. При этом полнотекстовые индексы не имеют каких-то специальных правил обработки JSON, они всего лишь разбивают текст на отдельные слова используя в качестве разделителей двойные кавычки, запятые, скобки и т.п.

Вот и все, что я хотел рассказать про хранение и индексирование JSON в SQL Server 2016. В следующей части серии я попробую провести сравнение хранения и обработки JSON и XML в SQL Server и выяснить опытным путем, какой же формат лучше использовать для хранения и передачи неструктурированных данных.

SQL Server 2016: JSON, часть 4, модификация данных

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

Для модификации данных присутствует функция JSON_MODIFY, которой на вход передается JSON документ, пусть до нужного свойства и новое значение.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":30,"skills":["SQL Server 2014","T-SQL","JSON"]}';
 
set @json = json_modify(@json, 'lax$.age', json_value(@json, '$.age') + 2);
set @json = json_modify(@json, '$.skills[0]', 'SQL Server 2016');
set @json = json_modify(@json, 'append strict$.skills', 'In-Memory OLTP');
 
select * from openjson(@json);

Как мы видим, все достаточно просто. Дополнительно хотелось бы сразу отметить, что можно использовать ключевое слово append, которое вы уже, наверное, заметили выше, если мы хотим добавить новое значение в массив данных.

Как же можно удалить какое свойство в существующем документе? Для этого нужно присвоить ему значение null в lax режиме.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":30,"skills":["SQL Server 2014","T-SQL","JSON"]}';
 
select * from openjson(json_modify(@json, 'lax$.age', null));
select * from openjson(json_modify(@json, 'strict$.age', null));


В случае же, если вы попытаетесь сделать это в strict режиме, как показано выше, то в JSON документе ему будет присвоено значение null и свойство останется. Если же вы попытаетесь присвоить null или любое другое значение в strict режиме несуществующему элементу, то получите ошибку.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":30,"skills":["SQL Server 2014","T-SQL","JSON"]}';
 
select * from openjson(json_modify(@json, 'strict$.hobby', null));

Msg 13608, Level 16, State 2, Line 3
Property cannot be found on the specified JSON path.

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

SQL Server 2016: STRING_SPLIT, описание и сравнение производительности

Свершилось! Наконец-то в SQL Server 2016 появляется встроенная функция для разбивки строк. Пожалуй, это одна из самых распространённых функций, которые, пожалуй, присутствуют на каждом SQL Server, но до текущей версии были реализованы своими силами. Это та функция, для которой существует множество самых различных реализаций, и, зачастую, не самых быстрых. Давайте поближе взглянем на возможности функции STRING_SPLIT, ее ограничения и сравним ее по скорости с самыми распространенными решениями.

Функция принимает всего 2 параметра: собственно, саму строку, которую необходимо разбить и разделитель. Существует одно очень важное и не очень понятное ограничение, разделитель может быть только длиной один символ. Поэтому, если вам требуется разбить строку по большему разделителю, то эта функция для вас не подойдет, и вам нужно искать другое решение.

На текущий момент одним из самых быстрых решений по разбивки строк считается CLR функция, написанная Adam Machanic (ссылка на статью). Также я решил привести пару других примеров функции, реализация в лоб, а также через inline функцию с соединением с таблицей чисел. Последний из этих вариантов на строках небольшой длины обычно показывает неплохие результаты по сравнению с реализацией в лоб. Ну и сравним скорость со стандартной функцией, входящей в SQL Server 2016. Для начала подготовим таблицу с числами и добавим 2 вышеуказанные функции. Как скомпилировать и добавить CLR функцию, я не привожу, т.к. надеюсь, каждый сможет это сделать самостоятельно.

use test;
go
 
if object_id('dbo.num', 'U') is not null
	drop table dbo.num;
go
 
create table dbo.num (
	n int primary key clustered
);
go
 
insert into dbo.num(n)
select top (1000000) row_number() over (order by (select 1)) as rn
from sys.all_columns as t1
cross apply sys.all_columns as t2
go
 
if object_id('dbo.fn_split', N'TF') is not null
	drop function dbo.fn_split;
go
 
create function dbo.fn_split
(
    @str nvarchar(max),
	@del nchar(1)
)
returns @returntable table 
(
	st nvarchar(4000)
)
as
begin
 
	while charindex(@del, @str) > 0
	begin
		insert into @returntable select substring(@str, 1, charindex(@del, @str) - 1)
		set @str = substring(@str, charindex(@del, @str) + 1, len(@str))
	end
	if @str <> ''
		insert into @returntable values(@str)
 
	return
end
go
 
if object_id('dbo.fn_split2', 'if') is not null
	drop function dbo.fn_split2;
go
 
create function dbo.fn_split2 (
	@str nvarchar(max),
	@del nchar(1) = ','
) returns table
as
return
	select
		substring(@str, n, case when charindex(@del, @str, n) = 0 then len(@str) else charindex(@del, @str, n) - n end) as st
	from dbo.num
	where
		n <= len(@str)
		and (n = 1 or substring(@str, n - 1, 1) = @del)
go

И, собственно, сам код для теста, в котором я сначала подготавливаю текст из 20 тысяч слов разделенных запятой, а потом проверяю его разбивку различными методами.

set statistics time on
 
declare @str varchar(max) = (select top (20000) t1.name + ',' as 'text()'
from sys.all_columns as t1
cross apply sys.all_columns as t2
for xml path(''))
 
set @str = substring(@str, 1, len(@str) - 1)
 
select *
from dbo.split_string_clr(@str, ',');
 
select *
from dbo.fn_split(@str, ',') as f;
 
select *
from dbo.fn_split2(@str, ',') as f;
 
select *
from string_split(@str, ',');

Ну и приведу один из полученных результатов.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 113 ms.

SQL Server Execution Times:
CPU time = 13468 ms, elapsed time = 13759 ms.

SQL Server Execution Times:
CPU time = 143875 ms, elapsed time = 143989 ms.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 216 ms.

Как мы видим, CLR функция и STRING_SPLIT оставляют далеко позади остальные решения, а вот выбрать, кто из них быстрее становится проблематично, т.к. их время запуска практически не отличается и варьируется от теста к тесту. В данном случае расходы на CPU одинаковые, а время выполнения медленнее. Однако, в каких-то запусках, время выполнения становится сравнимым, поэтому я не берусь отдать пальму первенства ни одной из них. Это действительно круто, что наконец-то появляется встроенное решение с превосходной производительностью. Но, если вам потребуется использовать разделитель больше чем в один символ, то рекомендую пользоваться CLR решением.

SQLSaturday в Краснодаре 4 июня 2016 г.

sqlsat503_webОсталось чуть меньше двух недель до, пожалуй, главного события на юге России, посвященного SQL Server – это, уже ставшая традиционной, конференция SQLSaturday. Если честно, я уже сбился со счета, какой год подряд она уже проходит в Краснодаре, но факт, что уже точно больше, чем в Москве.

Вы еще можете успеть зарегистрироваться, купить билеты в Краснодар, посетить эту замечательную бесплатную конференцию, а также провести выходные в этом довольно милом городе.

Официальный сайт конференции доступен по следующей ссылке. Там же вы сможете зарегистрироваться. Ознакомиться с полным списком докладов и докладчиков вы можете на этой странице. Докладов немного, но они достаточно жесткие и интересные, на мой взгляд.

Сам я выступлю с двумя докладами: по языку R в SQL Server 2016, а также с новым, улучшенным докладом про нетрадиционные и провокационные методы оптимизации запросов. Надо отметить, что этот доклад я в ближайшем будущем больше нигде читать не планирую.

Буду рад всех увидеть на своих докладах в Краснодаре. Удачи вам и приятного посещения конференции!

SQL Server 2016: JSON, часть 3, преобразование в реляционные данные

Это статья является продолжением серии про работу с 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 могут принимать следующие значения.

Значение Тип данных JSON
0 null
1 string
2 int
3 true/false
4 array
5 object

Во втором режиме работы функции 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 документы.

SQL Server 2016: JSON, часть 2, формирование данных

Это статья является продолжением серии про работу с 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 объект в реляционный набор данных.

Виртуальный Форум – «Данные. Технологии. SQL Server 2016»

Компания Майкрософт организует Виртуальный Форум Microsoft «Данные. Технологии. SQL Server 2016», который состоится 8 июня 2016 года. Форум приурочен к выходу новой версии платформы управления данными Microsoft на базе SQL Server 2016.

Успех цифровой трансформации бизнеса сегодня напрямую зависит от способности организации использовать передовые технологии и инструменты по работе с данными, которые становятся важнейшим ресурсом развития бизнеса – сравнимым, пожалуй, с ролью электричества в эпоху трансформации XIX века. В рамках форума мы обсудим, какие требования сегодня предъявляются к современным платформам по работе с данными, какие ключевые технологии must have в арсенале любой компании.

Основная программа форума состоит из трех паралельных технологических сесссий:

  • SQL Server 2016: новые стандарты в мире транзакции;
  • Бизнес-aналитика: SQL, Power BI, R, Mobile;
  • Azure: новое поколение решений для аналитики, Big Data & IoT.

Подробная программа доступна на сайте мероприятия.

Пространство Виртуальном форума орагизовано таким образом, что бы сможете почуствовать себя участником реальной конференции.
Каждый участник сможет посетить выставку партнерских решений.
Пообщаться с технологическими экспертами MVP, задать интересующие вопросы.
Задать интересующие вопросы докладчикам.
Получить все необходимые материалы и презентации.
И даже выйграть призы – самые активные получат один из 30 сертификатов на сдачу экзаменов по SQL Server 2016.

И это еще не все — увидеть технологии в действии Вы сможете в течение дня, приняв участие в тест-драйве платформы с помощью онлайн лабораторных работ под руководством тренера.

Участие в форуме бесплатное. Необходима предварительная регистрация.

SQL Server 2016: JSON, часть 1, извлечение данных

JSON сейчас является одним из самых широко распространённых форматов обмена данными. Он используется как формат хранения в нескольких NoSQL решениях, в частности, в Microsoft Azure DocumentDB. На мой взгляд, сегодня, он стал даже популярнее XML. Одной из причин, почему так сложилось, является более простая форма и читаемость по сравнению с XML. Поэтому назрела острая необходимость иметь возможность обрабатывать данные в этом формате внутри SQL Server. Поэтому в версии SQL Server 2016 появляется такая возможность.

В первую очередь стоит отметить, что нет отдельного типа для хранения JSON, в отличие от того же XML. Поэтому JSON необходимо хранить в обычных переменных или полях типов varchar или nvarchar.

Для того, чтобы извлечь данных из JSON в SQL Server 2016 добавлены 3 функции: ISJSON, JSON_VALUE, JSON_QUERY.

Первая функция позволяет просто проверить, содержит ли предложенный текст правильный JSON формат, что может быть достаточно удобно перед тем как начать с ним работать.

declare
	@json1 varchar(max) = N'{"test": 1}',
	@json2 varchar(max) = N'1',
	@json3 varchar(max) = null;
 
select
	isjson(@json1) as json1,
	isjson(@json2) as json2,
	isjson(@json3) as json3;

Функция возвращает 1, если это JSON, 0 – если нет и null, если в нее был передан null.

Для того, чтобы извлечь конкретное значение, можно воспользоваться функцией JSON_VALUE.

declare @json varchar(max) =
'{
	"info":{
		"specialization":"computer science",
		"course number":1,
		"address":{
			"town":"Moscow",
			"region":"Moscow",
			"country":"Russia"
		},
		"parents":["Anna", "Peter"]
	},
	"type":"Student"
}';
 
select
	json_value(@json, '$.info.specialization') as [specialization],
	json_value(@json, '$.info."course number"') as [course_number],
	json_value(@json, '$.info.address.town') as [town],
	json_value(@json, '$.info.parents[0]') as [mother],
	json_value(@json, '$.info.parents[1]') as [father];

Ну и наконец, извлечь какой-то фрагмент из JSON можно с помощью функции JSON_QUERY.

declare @json varchar(max) =
'{
	"info":{
		"specialization":"computer science",
		"course number":1,
		"address":{
			"town":"Moscow",
			"region":"Moscow",
			"country":"Russia"
		},
		"parents":["Anna", "Peter"]
	},
	"type":"Student"
}';
 
select
	json_query(@json, '$.info.address') as [address],
	json_query(@json, '$.info.parents') as [parents];

И еще хотелось бы сразу рассказать поподробнее про выражения пути в JSON, которые используются в функциях JSON_VALUE и JSON_QUERY. Как мы уже успели убедиться, они достаточно простые.

$ – ссылка на объект JSON в тексте

$.property1 – ссылка на свойство property1

$.array1[0] – ссылка на первый элемент массива array1 (нумерация начинается с нуля, как в JavaScript)

$.property1.property2.property3 – ссылка на свойство property3, которое является вложенными в property2 и property1. Так извлекаются объекты на нескольких уровнях вложенности.

$.”property name 1″ – если имя свойства содержит специальные символы типа пробелов, доллара и т.п., но его имя должно быть заключено в кавычки.

Также существует 2 типа таких выражений: lax и strict. По умолчанию используется lax, ничего дополнительно указывать не надо, но вы можете сделать это принудительно, указав это ключевое слово впереди выражения, например “lax$.property1” или “strict$.property1”. Отличие этих типов заключается в том, что если вы укажете несуществующие или неправильные пути для функций, то в случае lax выражения вы получите NULL, а в случае strict ошибку. Например, если вы укажете выражение, которое возвращает не скалярное значение для функции JSON_VALUE, lax выражение вернет NULL, а при использовании strict вы получите ошибку.

declare @json varchar(max) =
'{
	"info":{
		"specialization":"computer science",
		"course number":1,
		"address":{
			"town":"Moscow",
			"region":"Moscow",
			"country":"Russia"
		},
		"parents":["Anna", "Peter"]
	},
	"type":"Student"
}';
 
select
	json_value(@json, '$.property.not.exists') as [not exists], -- Свойство не существует
	json_value(@json, 'lax$.property.not.exists') as [not exists], -- Свойство не существует
	json_value(@json, '$.info.address[0]') as [address_0] -- Попытка обратиться к элементу, который не является массивом
;

В случае же использования типа strict мы получим ошибку.

declare @json varchar(max) =
'{
	"info":{
		"specialization":"computer science",
		"course number":1,
		"address":{
			"town":"Moscow",
			"region":"Moscow",
			"country":"Russia"
		},
		"parents":["Anna", "Peter"]
	},
	"type":"Student"
}';
 
select json_value(@json, 'strict$.property.not.exists') as [not exists]; -- Свойство не существует

Msg 13608, Level 16, State 5, Line 16
Property cannot be found on the specified JSON path.

Стоит также отметить, что все эти функции могут работать не только с переменными, но со столбцами таблицы.

declare @json varchar(max) =
'{
	"info":{
		"specialization":"computer science",
		"course number":1,
		"address":{
			"town":"Moscow",
			"region":"Moscow",
			"country":"Russia"
		},
		"parents":["Anna", "Peter"]
	},
	"type":"Student"
}';
 
if object_id('tempdb..#test', 'U') is not null
	drop table #test;
 
create table #test (
	json_text varchar(max) null
);
 
insert into #test (json_text) values (@json);
 
select json_value(json_text, 'strict$.info.parents[0]') as [parents]
from #test;

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

SQL Server 2016: SESSION_CONTEXT

Немногие знают, но в SQL Server присутствует механизм, когда можно сохранять определенное состояние сессии и передавать сигналы между разными запросами в пределах одной сессии, либо использовать это как дополнительный мониторинг. Ранее я уже писал об этом. Вы можете обратиться к этой статье, чтобы прочитать об этой возможности более подробно. В этой статье я бы хотел рассказать об улучшении этого механизма, особенно, когда нужно было хранить несколько значений, т.к. появляется полноценное хранилище типа key-value.

Для установки значения теперь есть новая хранимая процедура sp_set_session_context. В качестве параметров вы передаете в нее @key – название ключа типа sysname, размером до 128 байт. Также вы передаете параметр @value – значение этого ключа типа sql_variant, размером до 8000 байт. Если вы передадите в параметр @value значение null, то это освободит ранее занимаемую этим значением память. Также имеется опциональный параметр @read_only, который по умолчанию имеет значение 0. Если вы передадите в него 1, то не сможете повторно изменить значение данного параметра. В зависимости от ситуации, это может вам пригодиться.

Ниже я привожу пример установки пары значений для сессии.

exec sp_set_session_context 'rows_per_page', 1000;
exec sp_set_session_context 'user_type', 'simple', @read_only = 1;

Приложение при соединении с базой данных может установить эти настройки, в зависимости от которых будут дальше работать различные хранимые процедуры. В данном случае, в зависимости от типа пользователя возвращать разный набор полей в отчете, а также максимальное количество строк на одну страницу. Это всего лишь один из примеров, как вы можете использовать переменные сессии.

Чтобы в следующем запросе в рамках этой сессии получить значение какого-либо параметра, нужно воспользоваться функцией SESSION_CONTEXT.

select session_context(N'user_type');

А дальше вы уже можете использовать его по своему усмотрению.

Каждый пользователь может устанавливать и изменять настройки в рамках своей сессии, но есть ограничение в 256 кб на одну сессию (согласно документации). Если оно будет превышено, возникнет ошибка. Одна тест показывает, что ошибка возникает, если размер превышает 1 Мб. Так что, скорее всего, размер был расширен, но документация пока не обновлена.

Msg 15665, Level 16, State 1, Procedure sp_set_session_context, Line 1 [Batch Start Line 0]
The value was not set for key ‘125’ because the total size of keys and values in the session context would exceed the 1 MB limit.

Также с помощью системного представления sys.dm_os_memory_objects вы можете посмотреть, сколько всего памяти занято на вашем сервере переменными сессий.

select *
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SESSION_CONTEXT';

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