Индексируем свойства JSON через типизированные столбцы

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

Отделяйте гибкие атрибуты от бизнес-ключей. Если идентификатор клиента определяет соединения, авторизацию и большинство запросов, ему нужен явный контракт. Размещение внутри JSON не отменяет тип, обязательность и допустимый диапазон.

Извлекаем нужный тип

Пример использует JSON-функции SQL Server 2016 и новее и создаёт постоянные объекты только в учебной базе.

-- Create in a disposable practice database.
CREATE TABLE dbo.JsonOrderDemo (
    DocumentId int NOT NULL PRIMARY KEY,
    Payload nvarchar(max) NOT NULL,
    CustomerId AS TRY_CONVERT(bigint, JSON_VALUE(Payload, '$.customerId')) PERSISTED,
    CONSTRAINT CK_JsonOrderDemo_Json CHECK (ISJSON(Payload) = 1),
    CONSTRAINT CK_JsonOrderDemo_Customer CHECK (CustomerId IS NOT NULL AND CustomerId > 0)
);
CREATE INDEX IX_JsonOrderDemo_Customer ON dbo.JsonOrderDemo(CustomerId);
INSERT dbo.JsonOrderDemo(DocumentId, Payload) VALUES
(1, N'{"customerId":42,"status":"new"}'),
(2, N'{"customerId":43,"status":"new"}'),
(3, N'{"customerId":42,"status":"paid"}');
SELECT DocumentId FROM dbo.JsonOrderDemo WHERE CustomerId = 42;

Начальный запрос возвращает документы 1 и 3. CustomerId вычисляется как bigint, поэтому индекс содержит числовые ключи вместо длинных текстовых результатов. На маленькой таблице оптимизатор может выбрать сканирование как более дешёвое. Пример создаёт возможный доступ, а не принуждает к конкретному плану.

TRY_CONVERT возвращает NULL для неподходящего значения. Второй CHECK явно запрещает NULL и неположительные числа. Одно условие CustomerId > 0 разрешало бы UNKNOWN при NULL и не обеспечивало обязательность поля.

ISJSON проверяет синтаксис, но не полную бизнес-схему. Корректный массив или объект без customerId ещё не являются допустимым заказом. Проверка вычисляемого столбца обнаруживает отсутствующий числовой ключ, а другим обязательным полям нужны отдельные правила.

Преобразование принимает JSON-число и строку с числом, подходящим для bigint. Если API обязан их различать, проверяйте тип токена, например через OPENJSON. Не оставляйте это различие случайному поведению преобразования.

Согласуем извлечение и поиск

Прямой фильтр по CustomerId делает доступ и тип параметров понятными. SQL Server способен распознать некоторые совпадающие вычисляемые выражения, но небольшие различия могут изменить такую возможность. Стабильный контракт надёжнее многократного ручного воспроизведения JSON-выражения.

Имена JSON-свойств чувствительны к регистру при сопоставлении пути. customerId и CustomerId не становятся одинаковыми из-за нечувствительной collation обычных сравнений. Проверяйте соглашения о названиях у всех сериализаторов интеграции.

У JSON_VALUE есть собственное поведение длины. Для традиционного nvarchar скаляр больше 4 000 символов может дать NULL в lax-режиме или ошибку в strict. Это не инструмент для неограниченного описания. OPENJSON позволяет извлекать большие скаляры с подходящей схемой.

Не индексируйте необоснованно nvarchar(4000), если свойство является числом или коротким кодом. Ограничения размера ключа и стоимость сравнения сохраняются. Но и произвольное сокращение текстового типа способно обрезать значение: сначала проверяйте допустимую длину.

Учитываем запись и изменение схемы

Изменение документа пересчитывает свойство и обслуживает индекс.

UPDATE dbo.JsonOrderDemo
SET Payload = JSON_MODIFY(Payload, '$.customerId', 43)
WHERE DocumentId = 1;
SELECT DocumentId, CustomerId FROM dbo.JsonOrderDemo ORDER BY DocumentId;

Документ 1 теперь относится к клиенту 43. Приложению не нужно отдельно обновлять зеркальный идентификатор. Один риск рассогласования исчезает, но разбор текста, ограничения, сохранённое значение и индексная запись продолжают расходовать ресурсы.

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

Переименование customerId или изменение его типа остаётся миграцией схемы, несмотря на гибкий формат. Согласуйте производителя, проверки, извлечение, старые строки и читателей. Развёртывание не должно незаметно превращать старые свойства в NULL или неожиданно отклонять допустимые записи.

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

Техническая документация: Microsoft Learn: Index JSON data · Microsoft Learn: JSON_VALUE · Microsoft Learn: ISJSON.