Microsoft SQL Server Index sur les propriétés JSON en utilisant des colonnes calculées


Exemple

Lors du stockage de documents JSON dans SQL Server, nous devons pouvoir filtrer et trier efficacement les résultats des requêtes sur les propriétés des documents JSON.

CREATE TABLE JsonTable
(
    id int identity primary key,
    jsonInfo nvarchar(max),
    CONSTRAINT [Content should be formatted as JSON]
    CHECK (ISJSON(jsonInfo)>0)
)
INSERT INTO JsonTable
VALUES(N'{"Name":"John","Age":23}'),
(N'{"Name":"Jane","Age":31}'),
(N'{"Name":"Bob","Age":37}'),
(N'{"Name":"Adam","Age":65}')
GO

Étant donné le tableau ci-dessus Si nous voulons trouver la ligne avec le nom = 'Adam', nous exécuterons la requête suivante.

SELECT * 
FROM JsonTable Where 
JSON_VALUE(jsonInfo, '$.Name') = 'Adam'

Cependant, cela nécessitera que SQL Server exécute une table complète qui, sur une grande table, n'est pas efficace.

Pour accélérer cela, nous aimerions ajouter un index, mais nous ne pouvons pas référencer directement les propriétés dans le document JSON. La solution consiste à ajouter une colonne calculée sur le chemin JSON $.Name , puis à ajouter un index sur la colonne calculée.

ALTER TABLE JsonTable
ADD vName as JSON_VALUE(jsonInfo, '$.Name')

CREATE INDEX idx_name
ON JsonTable(vName)

Maintenant, lorsque nous exécutons la même requête, au lieu d'une analyse de table complète, SQL Server utilise un index pour rechercher dans l'index non clusterisé et rechercher les lignes qui répondent aux conditions spécifiées.

Remarque: Pour que SQL Server utilise l'index, vous devez créer la colonne calculée avec la même expression que celle que vous prévoyez d'utiliser dans vos requêtes - dans cet exemple, JSON_VALUE(jsonInfo, '$.Name') . Cependant, vous pouvez également utiliser le nom de la colonne calculée vName