Продолжаем серию статей про поддержку 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 и выяснить опытным путем, какой же формат лучше использовать для хранения и передачи неструктурированных данных.

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

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

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