JSON сейчас один из самых используемых форматов данных в разработке. Большинство современных сервисов возвращают информацию в виде JSON. JSON также предпочитаемый формат для хранения структурированный информации в файлах, например. Так как очень много данных используется в JSON-формате, то поддержка JSON в SQL Server особенно становится актуальной, чтобы иметь возможность обмениваться данными с другими сервисами.
JSON стал одной из самых востребованных фич, добавленных в SQL Server 2016. Далее в статье мы рассмотрим основные механизмы работы с JSON.
Краткий обзор
Функции для работы с JSON в SQL Server позволяют анализировать и запрашивать данные JSON, преобразовывать JSON в реляционный вид и экспортировать результат SQL-запроса как JSON.
Если у вас есть JSON, вы можете достать оттуда данные или проверить их на валидность, используя встроенные функции JSON_VALUE, JSON_QUERY и ISJSON. Для изменения данных может быть использована функция JSON_MODIFY. Для более продвинутого использования функция OPENJSON позволяет преобразовывать массив JSON объектов в набор строк. Затем над этим набором можно выполнить любой SQL-запрос. Наконец, существует конструкция FOR JSON, которая преобразует результат запроса в JSON.
Посмотрим на несколько простых примеров. В следующем коде мы определим текстовую переменную, в которой будет JSON:
DECLARE @json NVARCHAR(4000)
SET @json =
N'{
"info":{
"type":1,
"address":{
"town":"Bristol",
"county":"Avon",
"country":"England"
},
"tags":["Sport", "Water polo"]
},
"type":"Basic"
}'Теперь мы можем получить отдельные значения или объекты из JSON с помощью JSON_VALUE и JSON_QUERY:
SELECT
JSON_VALUE(@json, '$.type') as type,
JSON_VALUE(@json, '$.info.address.town') as town,
JSON_QUERY(@json, '$.info.tags') as tagsЭтот запрос вернет «Basic», «Bristol» и [«Sport», «Water polo»]. Функция JSON_VALUE возвращает скалярное значение из JSON (то есть строку, число, булевское значение), которое расположено по «пути», указанному вторым параметром. JSON_QUERY возвращает объект или массив (в нашем примере это массив тегов) по «пути». Встроенные функции JSON используют похожий на JavaScript синтаксис для обращения к значениям и объектам в качестве второго параметра.
Функция OPENJSON позволяет обратиться к массиву внутри JSON и возвратить элементы этого массива:
SELECT value
FROM OPENJSON(@json, '$.info.tags')В этом примере возвращаются строковые значения из массива тегов. Кроме того, OPENJSON может возвращать любой сложный объект.
Наконец, конструкция FOR JSON может отформатировать любой результат выполнения SQL-запроса в JSON:
SELECT object_id, name
FROM sys.tables
FOR JSON PATHРассмотрим эти функции подробнее.
Хранение данных JSON в SQL Server
В SQL Server JSON хранится как текст. Вы можете использовать тип NVARCHAR для этого. В следующем примере мы будем хранить JSON в поле InfoJson:
CREATE TABLE Person (
Id int IDENTITY PRIMARY KEY NONCLUSTERED,
FirstName nvarchar(100) NOT NULL,
LastName nvarchar(100) NOT NULL,
InfoJson nvarchar(max)
) WITH (MEMORY_OPTIMIZED=ON)В SQL Server 2016 вы можете комбинировать обычные столбцы (FirstName и LastName в примере) и столбцы с JSON (InfoJSON в примере) в одной таблице. Также вы можете комбинировать столбцы JSON со столбцами с пространственными данными (spatial columns) и XML. В отличие от только реляционных или только документоориентированных хранилищ, вы можете выбирать принципы хранения для достижения большей гибкости в разработке.
Хотя JSON хранится в текстовых столбцах, это не просто обычный текст. В SQL Server есть механизм для оптимизации хранения текстовых столбцов с использованием различных механизмов сжатия, таких как сжатие UNICODE, которые позволяют экономить до 50% размера. Также вы можете хранить JSON в таблицах с columnstore индексами или сжимать их явно с использованием встроенный функции COMPRESS, которая использует алгоритм GZip.
JSON полностью совместим с любым компонентом SQL Server, который работает с типом NVARCHAR. В примере выше, JSON хранится в OLTP (Hekaton) таблице в памяти, которая предлагает суперпроизводительность. Вы можете хранить JSON в обычных таблицах, использовать columnstore индексы или FILESTREAM. Вы также можете загружать его из Hadoop используя таблицы Polybase, считывать из файловой системы, работать с ним в Azure SQL, использовать репликацию и т.д. Если вы скомбинируете таблицы, в которых хранится JSON с другими фичами SQL Server, такими как безопасность временных таблиц или безопасность на уровне строк, вы можете обнаружить мощные возможности, которые недоступны в существующих документоориентированных СУБД.
Если вы хотите обеспечить валидность хранимого JSON, вы можете добавить проверку на валидность с помощью ограничения и функции ISJSON:
ALTER TABLE Person
ADD CONSTRAINT [Content should be formatted as JSON]
CHECK ( ISJSON( InfoJSON )> 0 )Во время выполнения ваши запросы не будут работать, если JSON отформатирован неправильно.
Т.к. JSON представлен в виде текста, нет необходимости менять что-то в ваших приложениях. Вы можете работать с JSON как с обычными строками. JSON может быть загружен с помощью ORM как строка и отправлен в клиентское JavaScript-приложение. Любые утилиты извлечения данных также будут работать.
Встроенные функции для обработки JSON
SQL Server 2016 предлагает несколько функций для обработки JSON:
- ISJSON( jsonText ) проверяет валидность JSON на соответствие спецификации. С помощью этой функции вы можете накладывать ограничения на столбцы, содержащие JSON
- JSON_VALUE( jsonText, path ) разбирает jsonText и выделяет отдельные значения по определенному «пути» (см. примеры ниже)
- JSON_QUERY( jsonText, path ) разбирает jsonText и выделяет объекты или массивы по определенному «пути» (см. примеры ниже)
- JSON_MODIFY( jsonText, path, newValue) изменяет значение какого-либо свойства по определенному «пути (см, примеры ниже)
Эти функции используют „пути“ в JSON для обращения к значениям или объектам. Примеры:
'$' // ссылается на весь объект JSON в исходном тексте
'$.property1' // ссылается на property1 в объекте JSON
'$[4]' // ссылается на 5-й элемент в массиве (индексы начинаются с 0)
'$.property1.property2.array1[5].property3.array2[15].property4' // ссылается на вложенное свойство
'$.info."first name"' // ссылается на свойство "first name" в объекте. Если название свойства содержит спецсимволы (пробелы, знак доллар и т.д.), то его нужно заключить в двойные кавычкиПри использовании функции JSON_MODIFY в параметре path могут быть использованы дополнительные модификаторы. В общем случае синтаксис „пути“ выглядит как:
[append] [ lax | strict ] $.json_path
При указании модификатора append новое значение будет добавлено к массиву, на который ссылается json_path. Модификатор lax устанавливает режим работы, при котором неважно, существует свойство или нет. Если его нет, то будет добавлено. При использовании strict, если свойства нет, будет сгенерирована ошибка.
Знак доллара ($) ссылается на весь объект JSON (аналогично корневому узлу „/“ в XPath). Вы можете добавлять любое свойство после „$“ для обращения к элементам объекта. Рассмотрим простой пример:
SELECT Id, FirstName, LastName,
JSON_VALUE(InfoJSON, '$.info."social security number"') as SSN,
JSON_QUERY(InfoJSON, '$.skills') as Skills
FROM Person AS t
WHERE ISJSON( InfoJSON ) > 0
AND JSON_VALUE(InfoJSON, '$.Type') = 'Student'Этот запрос возвращает имя и фамилию из обычных столбцов, социальный номер и массив навыков из JSON столбца. Результаты фильтруются по условию, при котором столбец InfoJSON должен содержать валидный JSON и значение Type в JSON столбце равно „Student“. Как вы уже поняли, вы можете использовать значения из JSON в любой части запроса (сортировка, группировка и т.п.).
Преобразование JSON в реляционный вид – OPENJSON
Функция OPENJSON возвращает таблицу, которая определяет массив объектов, проиводит итерацию по массиву и выводит каждый элемент массива в строке.
Пример
Данные на входе (JSON)
{
"Orders":
[
{
"Order": {
"Number": "S043659",
"Date": "2011-05-31T00:00:00"
},
"Account": "Microsoft",
"Item": {
"Price": 59.99,
"Quantity": 1
}
},
{
"Order": {
"Number": "S043661",
"Date": "2011-06-01T00:00:00"
},
"Account": "Nokia",
"Item": {
"Price": 24.99,
"Quantity": 3
}
}
]
}SQL-запрос
SELECT *
FROM OPENJSON(@json, N'$.Orders')
WITH (
Number VARCHAR(200) N'$.Order.Number',
Date DATETIME N'$.Order.Date',
Customer VARCHAR(200) N'$.Account',
Quantity INT N'$.Item.Quantity'
)Результат
| Number | Date | Customer | Quantity ---------------------------------------------------------- | S043659 | 2011-05-31 00:00:00.000 | Microsoft | 1 | S043661 | 2011-06-01 00:00:00.000 | Nokia | 3
В примере выше мы определили, где будем искать массив JSON, который обрабатываем (т.е. по пути $.Orders), какие столбцы возвращаем и где в объектах JSON находятся значения, которые мы возвращаем как ячейки.
OPENJSON может быть использован в любом запросе при работе с данными. Как в примере, мы можем преобразовать массив JSON из переменной orders в набор строк и затем вставить их в обычную таблицу:
INSERT INTO Orders(Number, Date, Customer, Quantity)
SELECT Number, Date, Customer, Quantity
OPENJSON (@orders)
WITH (
Number varchar(200),
Date datetime,
Customer varchar(200),
Quantity int
) AS OrdersArray4 столбца возвращаемого OPENJSON набора данных определены с помощью конструкции WITH. OPENJSON попытается найти свойства Number, Date, Customer и Quantity в каждом объекте JSON и преобразовать значения в столбцы в результирующем наборе данных. По умолчанию, если свойство не найдено, будет возвращен NULL. Предполагаем, что в переменной orders содержится следующий JSON:
'[
{"Number":1, "Date": "8/10/2012", "Customer": "Adventure works", "Quantity": 1200},
{"Number":4, "Date": "5/11/2012", "Customer": "Adventure works", "Quantity": 100},
{"Number":6, "Date": "1/3/2012", "Customer": "Adventure works", "Quantity": 250},
{"Number":8, "Date": "12/7/2012", "Customer": "Adventure works", "Quantity": 2200}
]'Как видите, преобразование из JSON в реляционную форму очень простое. Все, что нужно, это определить имена столбцов и типы, а OPENJSON найдет свойства в JSON, которые соответствуют столбцам. В этом примере используется простой одноуровневый JSON, но OPENJSON может работать и со сложными вложенными объектами.
Также OPENJSON может быть использован, когда нужно скомбинировать реляционные данные и JSON в одном и том же запросе. Предположим, что массив JSON из предыдущего примера хранится в столбце OrdersJson. Следующий запрос вернет обычные и JSON поля:
SELECT Id, FirstName, LastName, Number, Date, Customer, Quantity
FROM Person
CROSS APPLY OPENJSON (OrdersJson)
WITH (
Number varchar(200),
Date datetime,
Customer varchar(200),
Quantity int ) AS OrdersArrayOPENJSON обработает массив в каждой ячейке и вернет одну строку для каждого объекта JSON в массиве. Синтаксис CROSS APPLY OPENJSON используется для объединения строк таблицы с данными JSON.
Индексирование данных JSON
Хотя значения в JSON хранятся как текст, вы можете индексировать их как обычные значения в столбцах. Можно использовать некластеризованные или полнотекстовые индексы.
Если нужно создать индекс на каком-либо свойстве JSON, которое часто используется в запросах, вы можете создать вычисляемый столбец, который ссылается на нужное свойство, затем создать обычный индекс по этому полю. В следующем примере мы оптимизируем запросы, которые фильтруют строки, используя свойство $.Company из столбца InfoJSON:
ALTER TABLE Person
ADD vCompany AS JSON_VALUE(InfoJSON, '$.Company')
CREATE INDEX idx_Person_1
ON Person(vCompany)SQL Server предоставляет гибридную модель, в которой вы можете комбинировать обычные столбцы и значения из JSON в одном индексе.
Т.к. JSON это просто текст, можно использовать полнотекстовый индекс. Полнотекстовые индексы могут быть созданы на массиве значений. Вы создаете полнотекстовый индекс на столбце, который содержит массив JSON, или можете создать вычисляемый столбец, который ссылается на массив и добавить полнотекстовый индекс на этот столбец:
ALTER TABLE Person
ADD vEmailAddresses AS JSON_QUERY(InfoJSON, '$.Contact.Emails')
CREATE FULLTEXT INDEX ON Person(vEmailAddresses)
KEY INDEX PK_Person_ID ON jsonFullTextCatalog;Полнотекстовый индекс полезен, если вам нужно оптимизировать запросы, которые ищут какое-либо значение в массиве JSON:
SELECT PersonID, FirstName,LastName,vEmailAddresses
FROM Person
WHERE CONTAINS(vEmailAddresses, 'john@mail.microsoft.com')Этот запрос вернет строки из Person, где массив электронных адресов содержит „john@mail.microsoft.com“. Полнотекстовый индекс не имеет специальных правил парсинга JSON. Он делит массив, используя разделители (двойные кавычки, запятые, квадратные скобки) и индексирует значения в массиве. Полнотекстовый индекс применим к массивам чисел или строк. Если у вас более сложные объекты в JSON, полнотекстовый индекс неприменим, так как он не сможет отличить ключи от значений.
В общем, одни и те же принципы создания индексов могут применяться к обычным столбцам или столбцам JSON.
Экспорт данных в JSON – FOR JSON
В SQL Server есть возможность преобразования реляционных данных в JSON с помощью конструкции FOR JSON. Если вам знакома конструкция FOR XML, то вы уже практически знаете и FOR JSON.
Пример
Исходные данные
| Number | Date | Customer | Price | Quantity | -------------------------------------------------------------------- | S043659 | 2011-05-31 00:00:00.000 | Microsoft | 59.99 | 1 | | S043661 | 2011-06-01 00:00:00.000 | Nokia | 24.99 | 3 |
SQL-запрос
SELECT Number AS [Order.Number], Date AS [Order.Date],
Customer AS [Account],
Price AS 'Item.UnitPrice', Quantity AS 'Item.Qty'
FROM SalesOrder
FOR JSON PATH, ROOT('Orders')
Результирующий JSON
{
"Orders": [
{
"Order": {
"Number": "S043659",
"Date": "2011-05-31T00:00:00"
},
"Account": "Microsoft",
"Item": {
"UnitPrice": 59.99,
"Qty": 1
}
},
{
"Order": {
"Number": "S043661",
"Date": "2011-06-01T00:00:00"
},
"Account": "Nokia",
"Item": {
"UnitPrice": 24.99,
"Qty": 3
}
}
]
}Когда вы добавляете FOR JSON в конец SELECT запроса, SQL Server форматирует результат в виде JSON. Каждая строка будет представлена как один объект JSON, значения из ячеек будут значениями JSON, а названия столбцов будут использованы как ключи. Есть 2 типа конструкций FOR JSON:
- FOR JSON PATH позволяет определить структуру JSON на выходе, используя названия столбцов. Если вы используете разделенные точкой имена как синонимы столбцов, свойства JSON будут следовать соглашениям по именованию. Это похоже на FOR XML PATH где вы можете указать путь, разделенный слешами.
- FOR JSON AUTO автоматически создает вложенные массивы на базе иерархии таблиц в запросе. Похоже на FOR XML AUTO.
Заключение
JSON функции в SQL Server позволяют запрашивать и анализировать данные в виде JSON, а также преобразовывать их в реляционный вид и наоборот. Это позволяет интегрировать SQL Server во внешние системы, которые отдают или принимают JSON без дополнительных преобразований.
SQL Server также предлагает гибридную модель хранения, когда вы комбинируете реляционные данные и данные JSON в одной таблице. Такая модель позволяет получить компромисс между высокой скоростью доступа к данным и гибкими возможностями разработки приложений.
Кроме того, вы можете индексировать значения в JSON как обычные столбцы, а также преобразовывать реляционные данные в JSON с помощью FOR JSON и обратно, используя OPENJSON.
Автор: Milovan
