Indexer des propriétés JSON typées dans SQL Server

Conserver un document JSON est pratique lorsqu'une intégration fournit des attributs variables. La difficulté apparaît quand un filtre fréquent analyse le document sur un grand nombre de lignes. Une colonne calculée étroite et typée expose la propriété utile à un index classique tout en conservant le document original.

Distinguez attribut flexible et clé métier. Si l'identité client pilote jointures, autorisations et requêtes, elle exige un contrat explicite. La placer dans JSON ne supprime pas les exigences de type, présence et plage de valeurs.

Extraire le type attendu

L'exemple utilise les fonctions JSON de SQL Server 2016 ou ultérieur et crée des objets permanents uniquement dans une base de pratique.

-- 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;

La première recherche retourne les documents 1 et 3. CustomerId est dérivé en bigint: les clés indexées sont numériques plutôt que de longs résultats textuels. Sur trois lignes, un scan peut rester moins coûteux; l'exemple fournit un accès possible sans forcer le plan.

TRY_CONVERT transforme une valeur non convertible en NULL. Le second CHECK interdit explicitement NULL et les valeurs non positives. CustomerId > 0 seul accepterait UNKNOWN pour NULL et ne rendrait donc pas la propriété obligatoire.

ISJSON vérifie la syntaxe, pas tout le schéma métier. Un tableau JSON valide ou un objet sans customerId n'est pas une commande valable ici. Le contrôle calculé détecte la clé numérique absente; les autres attributs requis demandent leurs propres règles.

La conversion accepte un nombre JSON et une chaîne numérique convertible en bigint. Si l'API doit les distinguer, validez les types de tokens, par exemple avec OPENJSON. Définissez cette politique plutôt que de la laisser découler accidentellement d'une conversion.

Aligner recherche et extraction

Interroger CustomerId directement clarifie l'accès et le type des paramètres. SQL Server peut reconnaître certains calculs équivalents, mais de petites différences d'expression peuvent changer cette possibilité. Préférez une interface de requête stable à des expressions JSON réécrites par chaque consommateur.

Les noms de propriétés JSON sont sensibles à la casse dans les chemins. customerId et CustomerId ne sont pas équivalents simplement parce que la collation ignore la casse dans d'autres comparaisons. Testez les conventions des sérialiseurs utilisés par les producteurs.

JSON_VALUE possède aussi un comportement de longueur propre. Avec le chemin nvarchar traditionnel, un scalaire dépassant 4 000 caractères peut donner NULL en mode lax ou une erreur en mode strict. Ce n'est pas l'outil pour une description sans limite. OPENJSON peut exposer de grands scalaires avec un schéma adapté.

Évitez d'indexer une expression nvarchar(4000) pour une propriété réellement numérique ou courte. Limites des clés et coût des comparaisons existent toujours. Inversement, réduire arbitrairement une chaîne peut tronquer une valeur significative; validez sa longueur avant ce choix.

Évaluer écritures et évolutions

Modifier le document recalcule la valeur et entretient l'index.

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

Le document 1 appartient maintenant au client 43. Aucun second changement applicatif d'une colonne miroir n'est nécessaire. Cela retire un risque de désynchronisation, mais analyse JSON, contrôles, stockage persistant et maintenance de l'index ont toujours un coût.

PERSISTED est un choix de conception, pas une condition universelle pour tout index calculé. Déterminisme, précision, types supportés et options SET doivent respecter les règles d'indexation. Comparez le surcoût d'écriture et d'espace aux lectures évitées.

Si le producteur renomme customerId ou change son type, traitez-le comme une migration de schéma. Coordonnez validation, extraction, lignes existantes et lecteurs. Un déploiement ne doit pas transformer silencieusement les anciennes propriétés en NULL ou bloquer des écritures attendues.

Testez clés absentes, casse différente, tableaux, JSON mal formé, texte non numérique, dépassement numérique et modifications normales. Mesurez les lectures représentatives et le débit d'ingestion. Un index utile conserve la signification de sa propriété pendant toute l'évolution des documents.

Références techniques: Microsoft Learn: Index JSON data · Microsoft Learn: JSON_VALUE · Microsoft Learn: ISJSON.