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

Если у вас есть внешние службы, которые обмениваются данными по XML с внешними сторонами и согласовали схему, рекомендуется придерживаться типа данных XML и встроенных функций. С другой стороны, если вы работаете с микросервисной архитектурой или требуется гибкое хранение метаданных и данных, использование вновь добавленной поддержки JSON – хорошая идея.

В SQL Server, если у вас есть фиксированная схема для вашего JSON-документа, лучше использовать реляционные таблицы и обычные типы данных. Вы можете запросить эти таблицы, используя опцию “FOR JSON” в вашем T-SQL скрипте, чтобы получить отформатированный вывод. Давайте рассмотрим пример, используя образец базы данных Worldwide Importers SQL Server 2016:

SELECT [CustomerID], [CustomerName], [CustomerCategoryName], [PrimaryContact], [AlternateContact], [PhoneNumber], [FaxNumber], [BuyingGroupName], [WebsiteURL], [DeliveryMethod], [CityName], DeliveryLocation.ToString() as DeliveryLocation, [DeliveryRun], [RunPosition]
FROM [WideWorldImporters].[Website].[Customers]
WHERE CustomerID = 1
FOR JSON AUTO

В приведенном выше примере у нас есть столбец типа данных географии с именем DeliveryLocation. Для работы с JSON нам нужно преобразовать его в строку и предоставить псевдоним для преобразованного значения. Это гарантирует, что JSON работает с синтаксисом пар ключ/значение. Без псевдонима вы можете столкнуться с ошибками.

JSON также может использоваться в качестве входных данных для операторов DML, таких как INSERT, UPDATE и DELETE, с использованием функции OPENJSON. Это позволяет выполнять все операции с данными с использованием JSON. Если вы не знаете структуру ваших данных или требуется гибкость, вы можете хранить их в виде строки в формате JSON в столбце с типом данных NVARCHAR.

Например, в базе данных Worldwide Importers столбец CustomFields в таблице Application.People хранит данные в формате JSON. Вы можете просмотреть содержимое этого столбца в табличном формате с помощью функции OPENJSON:

DECLARE @json NVARCHAR(MAX)
SELECT @json = [CustomFields] FROM [WideWorldImporters].[Application].[People] WHERE PersonID = 8
SELECT * FROM OPENJSON(@json)

Еще один способ запроса данных JSON, когда вы знаете структуру и имена ключей, – использовать функции JSON_VALUE и JSON_QUERY:

SELECT
   JSON_QUERY([CustomFields], '$.OtherLanguages') as OtherLanguages,
   JSON_VALUE([CustomFields], '$.HireDate') as HireDate,
   JSON_VALUE([CustomFields], '$.Title') as Title,
   JSON_VALUE([CustomFields], '$.PrimarySalesTerritory') as PrimarySalesTerritory,
   JSON_VALUE([CustomFields], '$.CommissionRate') as CommissionRate
FROM [WideWorldImporters].[Application].[People]
WHERE PersonID = 8

При запросе данных JSON важно учитывать критерии запроса и способы их индексации. Например, если вы хотите запросить всех сотрудников компании после 2011 года, вы можете использовать функцию JSON_VALUE для извлечения даты приема на работу и сравнения ее с желаемым годом:

SELECT personID, fullName, JSON_VALUE(CustomFields, '$.HireDate') as hireDate
FROM [WideWorldImporters].[Application].[People]
WHERE IsEmployee = 1
AND YEAR(CAST(JSON_VALUE(CustomFields, '$.HireDate') as date)) > 2011

Индексирование JSON-документов можно осуществить путем создания вычисляемых столбцов. Например, в таблице Sales.Invoices столбец ReturnedDeliveryData хранит данные JSON. Создав вычисляемый столбец, вы можете индексировать поля поиска JSON для улучшения производительности:

ALTER TABLE [WideWorldImporters].[Sales].[Invoices]
ADD ReadyDate AS CONVERT(datetime, CONVERT(varchar, JSON_VALUE([ReturnedDeliveryData], '$.Events[0].EventTime')), 126)

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

SELECT [InvoiceID], [CustomerID], ReadyDate
FROM [WideWorldImporters].[Sales].[Invoices]
WHERE ReadyDate BETWEEN '20160301' AND '20160331'

Индексирование вычисляемого столбца может значительно улучшить производительность запросов JSON. SQL Server Management Studio (SSMS) может даже предложить создать индекс для вычисляемых столбцов для оптимизации плана выполнения запроса.

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