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