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 из уже имеющихся реляционных данных.