SQL-Ex blog

JSON в SQL Server 2017 и более поздних версиях

Когда в SQL Server 2005 был введен XML, это привело к реализации типа данных XML. В релизе SQL Server 2016, первой версии с поддержкой JSON, не было нативного типа данных JSON. Microsoft объяснило это решение парой причин, включая проблему миграции. Разработчики уже начали использовать JSON с пользовательскими библиотеками еще до SQL Server 2016 при отсутствии поддержки на стороне клиента (большинство клиентов по-прежнему рассматривали его как строковый тип данных).

Даже если принять эти аргументы, нативные типы данных JSON могли бы дать значительные преимущества в производительности и упрощении кода. Поскольку тип данных JSON не поддерживается в версиях SQL Server 2017 и 2019, сообщество все же надеется, что она появится в последующих релизах. Пока же данные JSON хранятся в столбцах типа NVARCHAR как текст. Вы могли бы использовать недавно добавленную функцию COMPRESS для сжатия хранимых текстовых данных и преобразования их (внутри) в двоичный формат.

Валидация данных JSON

Если вы хотите проверить строку JSON, вы можете использовать встроенную функцию ISJSON, которая принимает один строковый параметр любого типа, за исключением TEXT or NTEXT, возвращает 0 или 1, в зависимости от допустимости входного параметра. Вот пример:

SELECT

  ISJSON ('JSON_test'),

  ISJSON ('{}'), -- Правильно

  ISJSON (''),

  ISJSON ('{JSON_test}'),

  ISJSON ('{"item"}'),

  ISJSON ('{"item":"10}'),

  ISJSON ('{"item":10}'); -- Правильно

Важно отметить, что эта функция не проверяет, являются ли ключи одного уровня уникальными, вы должны проверять это в своем коде:

SELECT ISJSON ('{"item":"10", "item":"test"}') AS JSON_TEXT

Так как мы используем текстовые столбцы для хранения типов данных, одним из способов протестировать валидность данных JSON является создание таблицы с ограничением CHECK, которое использовало бы функцию ISJSON для проверки входных данных до их сохранения в таблице.

Модификация данных JSON

Вам может понадобиться обновить часть данных JSON, хранящихся в столбце SQL Server. В SQL Server 2016 появилась функция JSON_MODIFY, которая может использоваться для обновления значения отдельного свойства, добавления элемента в массив, вставки новой пары свойство/значение или удаления свойства для заданных значений. Функция имеет три обязательных входных параметра - выражение (столбец, содержащий текст JSON), путь (выражение пути JSON) и новое значение (значение элемента, указанного параметром "путь"). Функция возвращает обновленный текст JSON. На нескольких примерах продемонстрируем использование этой функции.

Пример 1. Мы обновляем значение свойства JSON - обновление значения товара в существующем файле JSON. Первый аргумент содержит исходный текст, второй - путь к свойству, которое мы обновляем, и третий - новое значение:

SELECT JSON_MODIFY('{"Class":"","Method":"TEST_METHD"}', '$.Method', 'TEST_METHOD') AS 'JSON';

Пример 2. Мы хотим увидеть значения до и после обновления для журнализации или сравнения. Следующий запрос объявляет переменную и сохраняет в ней текст JSON, который позже обновляется с помощью функции JSON_MODIFY():

DECLARE @OriginalJSON NVARCHAR(4000), @newvalue varchar(30),@path varchar(20)

Set @OriginalJSON='{"Class":"C#","Method":"TEST_METHD"}'

Set @newvalue='TEST_METHOD'

set @path='$.Method'

Select

        @OriginalJSON as 'Before Update',

        JSON_MODIFY(@OriginalJSON,@path, @newvalue) AS 'Updated JSON';

Пример 3. Мы хотим добавить свойство в строку JSON. Для этого мы указываем новое свойство и его значение. Функция JSON_MODIFY() в её поведении по умолчанию вставляет это свойство, поскольку оно не существует в исходном тексте JSON:

DECLARE @OriginalJSON NVARCHAR(4000)

Set @OriginalJSON='{"Class":"C#","Method":"TEST_METHD"}'

Select

        @OriginalJSON as 'Before Update',

        JSON_MODIFY(@OriginalJSON,'$.ScreenSize',17) AS 'Updated JSON';

Если мы не хотим использовать поведение по умолчанию, мы можем использовать строгий режим. В этом случае функция JSON_MODIFY() выбросит исключение, если свойство не существует в тексте JSON:

DECLARE @OriginalJSON NVARCHAR(4000)

Set @OriginalJSON='{"Class":"C#","Method":"TEST_METHD"}'

Select

        @OriginalJSON as 'Before Update',

        JSON_MODIFY(@OriginalJSON,'strict$.ScreenSize',17) AS 'Updated JSON';

Пример 4. Мы хотим удалить существующее свойство JSON. Для удаления узла или свойства необходимо передать NULL-значение в качестве третьего аргумента:

DECLARE @OriginalJSON NVARCHAR(4000), @newjson VARCHAR(100);

SET @OriginalJSON = '{"Class":"C#","Class":"MTHD","Properties":["Inp","Out","Oth"]}';

SELECT @OriginalJSON AS 'Before Update', 

       JSON_MODIFY(@OriginalJSON, '$.Properties[0]', NULL) AS 'Updated JSON';

Если выполнить код выше, он заменит элемент массива на NULL. Это, вероятно, не то, что мы хотели. Один из способов сделать это - заменить значения массива новыми значениями с тем, чтобы исключить значения NULL из вывода.

DECLARE @OriginalJSON NVARCHAR(4000), @newjson VARCHAR(100);

SET @OriginalJSON = '{"Class":"C#","Class":"MTHD","Properties":["Inp","Out","Oth"]}';

set @newjson='["Inp2","Out2"]'

SELECT @OriginalJSON AS 'Before Update', 

       JSON_MODIFY(@OriginalJSON, '$.Properties', JSON_Query(@newjson)) AS 'Updated JSON';

Пример 5. Мы хотим переименовать ключ. Мы сделаем это в том же стиле, что и переименование существующих столбцов в базе данных. Мы используем вложенные функции JSON_MODIFY() в сочетании с функцией JSON_VALUE, создавая новый ключ и удаляя существующий ключ после копирования его значения в новый ключ.

DECLARE @OriginalJSON NVARCHAR(4000)

SET @OriginalJSON = '{"Class":"C#","Class":"Method"}';

SELECT @OriginalJSON AS 'Before Update', 

   JSON_MODIFY(

   JSON_MODIFY(@OriginalJSON, '$.OptionalClass',  JSON_VALUE(@OriginalJSON,'$.Class')),

                              '$.Class',NULL);

Эти примеры демонстрируют основные варианты манипуляции данными JSON. Для дальнейших подробностей обратитесь к официальной документации.

Лучшая практика в оптимизации производительности

В отличие от XML, JSON сохраняется в текстовых столбцах. В то время как мы можем хранить XML как объекты BLOB и использовать пользовательские индексы на этих столбцах, данные JSON хранятся как простой текст UNICODE, и требуется интерпретация для их обработки. Это может повлиять на производительность чтения и записи больших документов JSON. Имеется один вариант для смягчения этой проблемы, на столбцах JSON, которые не превышают 1700 байт, может быть создан некластеризованный индекс, или они могут использоваться как включенные столбцы (в этом случае нет ограничения на значение). Пока SQL Server не предоставит специальный формат данных, возможности улучшения производительности ограничены. Вы можете создать вычисляемые столбцы и проиндексировать их, или использовать полнотекстовые индексы, но при увеличении размера документа JSON производительность будет деградировать.

Давайте начнем с примера индекса на вычисляемых столбцах. Сначала мы создадим простую таблицу и наполним ее некоторыми данными, а затем проанализируем производительность с помощью планов выполнения.

Создание исходной таблицы и заполнение данными (при использовании базы данных AdventureWorks2019):

DROP TABLE IF EXISTS dbo.JSON_PERF;

CREATE TABLE dbo.JSON_PERF

(

   PK_ID NVARCHAR(1000) PRIMARY KEY,

   JSON_DATA NVARCHAR(4000) NOT NULL

);



INSERT INTO dbo.JSON_PERF( PK_ID, JSON_DATA )

SELECT LoginID,

       ( SELECT EInner.LoginID, EInner.HireDate 

		     FROM HumanResources.Employee EInner 

			WHERE EInner.LoginID = EOuter.LoginID FOR JSON AUTO

      )	   

  FROM HumanResources.Employee EOuter;

Теперь выполним простой оператор SELECT:

SELECT * FROM dbo.JSON_PERF 

                 WHERE JSON_VALUE(JSON_DATA, '$.HireDate') = '2009-02-08'

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

ALTER TABLE dbo.JSON_PERF 

                        ADD COMPUT_COL AS JSON_VALUE(JSON_DATA, '$.HireDate');

CREATE INDEX IDX_1 ON dbo.JSON_PERF(COMPUT_COL);

SELECT * FROM dbo.JSON_PERF 

              WHERE JSON_VALUE(JSON_DATA, '$.HireDate') = '2009-02-08';

Этот подход будет работать только для данного атрибута (в нашем случае “HireDate”). Для других вам потребуется создавать дополнительные вычисляемые столбцы и индексы. По поводу индексов JSON важно отметить, что они учитывают коллацию, т.е. результат функции является текстовым значением, которое наследует коллацию входной переменной. Это так же применимо к значениям в индексе, которые будут сортироваться в соответствии с правилами коллации, определенными для исходных столбцов.

Заключение

В данной статье мы продолжили знакомиться с возможностями JSON, которые показывают, что хотя поддержка не такая строгая, как для XML, улучшения происходят с каждой новой версией SQL Server. Пока этого достаточно, чтобы использовать его в большинстве реальных приложений. Чего не хватает и следовало бы добавить в следующих версиях - это нативный тип данных JSON. Это позволило бы создавать индексы на столбцах JSON с целью улучшения производительности. Было показано, как легко интегрировать текст JSON в приложения SQL Server и как выполнять наиболее распространенные операции, включая синтаксический анализ, проверку и изменение данных. В заключение мы рассказали о лучших методах оптимизации производительности, но отметили ограничения текущей реализации. Надеюсь, вам понравится использовать JSON в ваших будущих проектах.