Microsoft SQL Server
Stocker JSON dans les tables SQL

JSON stocké en colonne de texte

JSON est un format textuel, il est donc stocké dans des colonnes NVARCHAR standard. La collection NoSQL est équivalente à la table de valeurs de clé à deux colonnes:

CREATE TABLE ProductCollection (
  Id int identity primary key,
  Data nvarchar(max)
)

Utilisez nvarchar(max) car vous ne savez pas quelle serait la taille de vos documents JSON. nvarchar(4000) et varchar(8000) ont de meilleures performances mais avec une taille limite à 8 Ko.

Assurez-vous que JSON est correctement formaté avec ISJSON

Puisque JSON est stocké dans la colonne textuelle, vous pouvez vous assurer qu'il est correctement formaté. Vous pouvez ajouter la contrainte CHECK sur la colonne JSON qui vérifie que le texte est correctement formaté JSON:

CREATE TABLE ProductCollection (
  Id int identity primary key,
  Data nvarchar(max)
       CONSTRAINT [Data should be formatted as JSON]
       CHECK (ISJSON(Data) > 0)
)

Si vous avez déjà une table, vous pouvez ajouter une contrainte de vérification à l'aide de l'instruction ALTER TABLE:

ALTER TABLE ProductCollection
    ADD CONSTRAINT [Data should be formatted as JSON]
        CHECK (ISJSON(Data) > 0)

Exposer les valeurs du texte JSON en tant que colonnes calculées

Vous pouvez exposer les valeurs de la colonne JSON en tant que colonnes calculées:

CREATE TABLE ProductCollection (
  Id int identity primary key,
  Data nvarchar(max),
  Price AS JSON_VALUE(Data, '$.Price'),
  Color JSON_VALUE(Data, '$.Color') PERSISTED
)

Si vous ajoutez une colonne calculée PERSISTED, la valeur du texte JSON sera matérialisée dans cette colonne. De cette façon, vos requêtes peuvent lire plus rapidement la valeur du texte JSON car aucune analyse n'est nécessaire. Chaque fois que JSON dans cette ligne change, la valeur sera recalculée.

Ajout d'index sur le chemin JSON

Les requêtes qui filtrent ou trient les données avec une certaine valeur dans la colonne JSON utilisent généralement l'analyse complète de la table.

SELECT * FROM ProductCollection
WHERE JSON_VALUE(Data, '$.Color') = 'Black'

Pour optimiser ce type de requêtes, vous pouvez ajouter une colonne calculée non persistante qui expose une expression JSON utilisée dans un filtre ou un tri (dans cet exemple, JSON_VALUE (Data, '$ .Color')) et créez un index sur cette colonne:

ALTER TABLE ProductCollection
ADD vColor as JSON_VALUE(Data, '$.Color')

CREATE INDEX idx_JsonColor
ON ProductCollection(vColor)

Les requêtes utiliseront l'index au lieu de l'analyse de table simple.

JSON stocké dans des tables en mémoire

Si vous pouvez utiliser des tables optimisées pour la mémoire, vous pouvez stocker JSON sous forme de texte:

CREATE TABLE ProductCollection (
  Id int identity primary key nonclustered,
  Data nvarchar(max)
) WITH (MEMORY_OPTIMIZED=ON)

Avantages de JSON en mémoire:

• Les données JSON sont toujours en mémoire, il n'y a donc pas d'accès au disque
• Il n'y a pas de verrou ni de verrou lors de l'utilisation de JSON