Le nouveau serveur SQL fournit des fonctions pour traiter le texte JSON. Les informations formatées en JSON peuvent être stockées sous forme de texte dans des colonnes SQL Server standard et le serveur SQL fournit des fonctions permettant de récupérer les valeurs de ces objets JSON.
DROP TABLE IF EXISTS Person
CREATE TABLE Person
( _id int identity constraint PK_JSON_ID primary key,
value nvarchar(max)
CONSTRAINT [Content should be formatted as JSON]
CHECK ( ISJSON(value)>0 )
)
Cette structure simple est similaire à la collection NoSQL standard que vous pouvez créer dans les bases de données NoSQL (par exemple Azure DocumentDB ou MongoDB) où vous avez simplement une clé qui représente l'ID et une valeur qui représente JSON.
Notez que NVARCHAR n'est pas simplement un texte brut. Le serveur SQL possède un mécanisme intégré de compression de texte qui permet de compresser de manière transparente les données stockées sur le disque. La compression dépend de la langue et peut aller jusqu'à 50% en fonction de vos données (voir la compression UNICODE ).
La principale différence entre le serveur SQL et d'autres bases de données NoSQL ordinaires est que le serveur SQL vous permet d'utiliser un modèle de données hybride dans lequel vous pouvez stocker plusieurs objets JSON dans la même "collection" et les combiner avec des colonnes relationnelles ordinaires.
À titre d'exemple, imaginez que nous savons que chaque personne de votre collection aura FirstName et LastName, et que vous pouvez stocker des informations générales sur la personne en tant qu'objet JSON unique, et les numéros de téléphone/adresses e-mail en tant qu'objets séparés. Dans SQL Server 2016, nous pouvons facilement créer cette structure sans aucune syntaxe supplémentaire :
DROP TABLE IF EXISTS Person
CREATE TABLE Person (
PersonID int IDENTITY PRIMARY KEY,
FirstName nvarchar(100) NOT NULL,
LastName nvarchar(100) NOT NULL,
AdditionalInfo nvarchar(max) NULL,
PhoneNumbers nvarchar(max) NULL,
EmailAddresses nvarchar(max) NULL
CONSTRAINT [Email addresses must be formatted as JSON array]
CHECK ( ISJSON(EmailAddresses)>0 )
)
Au lieu d'un seul objet JSON, vous pouvez organiser vos données dans cette "collection". Si vous ne voulez pas vérifier explicitement la structure de chaque colonne JSON, vous n'avez pas besoin d'ajouter la contrainte de vérification JSON sur chaque colonne (dans cet exemple, j'ai ajouté la contrainte CHECK uniquement sur la colonne EmailAddresses).
Si vous comparez cette structure à la collection NoSQL standard, vous remarquerez que vous aurez un accès plus rapide aux données fortement typées (FirstName et LastName). Par conséquent, cette solution est un bon choix pour les modèles hybrides où vous pouvez identifier certaines informations qui sont répétées dans tous les objets, et d'autres informations variables peuvent être stockées en JSON. De cette façon, vous pouvez combiner flexibilité et performance.
Si vous comparez cette structure avec le schéma de la table Personne de la base de données AdventureWorks, vous remarquerez que nous avons supprimé de nombreuses tables liées.
Outre la simplicité du schéma, vos opérations d'accès aux données seront plus simples par rapport à la structure relationnelle complexe. Vous pouvez maintenant lire une seule table au lieu de joindre plusieurs tables. Lorsque vous avez besoin d'insérer une nouvelle personne avec des informations connexes (adresses e-mail, numéros de téléphone), vous pouvez insérer un seul enregistrement dans une table au lieu d'insérer un enregistrement dans la table Personne d'AdventureWorks, en prenant la colonne identité pour trouver la clé étrangère qui sera utilisée pour stocker les téléphones, les adresses e-mail, etc. En outre, dans ce modèle, vous pouvez facilement supprimer une seule ligne de personne sans suppression en cascade en utilisant les relations de clé étrangère.
Les bases de données NoSQL sont optimisées pour des opérations simples de lecture, d'insertion et de suppression - SQL Server 2016 vous permet d'appliquer la même logique dans une base de données relationnelle.
Contraintes JSON Dans les exemples précédents, nous avons vu comment ajouter une contrainte simple qui valide que le texte stocké dans la colonne est correctement formaté. Bien que JSON ne possède pas de schéma fort, vous pouvez également ajouter des contraintes complexes en combinant des fonctions qui lisent les valeurs de JSON et des fonctions T-SQL standard :
ALTER TABLE Person
ADD CONSTRAINT [Age should be number]
CHECK ( ISNUMERIC(JSON_VALUE(value, '$.age'))>0 )
ALTER TABLE Person
ADD CONSTRAINT [Person should have skills]
CHECK ( JSON_QUERY(value, '$.skills') IS NOT NULL)
First constraint will take the value of $.age property and check is this numeric value. Second constraint will try to find JSON object in $.skills property and verify that it exists. The following INSERT statements will fail due to the violation of constraints:
INSERT INTO Person(value)
VALUES ('{"age": "not a number", "skills":[]}')
INSERT INTO Person(value)
VALUES ('{"age": 35}')
Notez que les contraintes CHECK peuvent ralentir vos processus d'insertion/mise à jour. Vous pouvez donc les éviter si vous avez besoin de performances d'écriture plus rapides.
Stockage JSON compressé Si vous avez un texte JSON volumineux, vous pouvez explicitement compresser le texte JSON en utilisant la fonction intégrée COMPRESS. Dans l'exemple suivant, le contenu JSON compressé est stocké en tant que données binaires, et nous avons calculé une colonne qui décompresse JSON en tant que texte original en utilisant la fonction DECOMPRESS :
CREATE TABLE Person
( _id int identity constraint PK_JSON_ID primary key,
data varbinary(max),
value AS CAST(DECOMPRESS(data) AS nvarchar(max))
)
INSERT INTO Person(data)
VALUES (COMPRESS(@json))
Les fonctions COMPRESS et DECOMPRESS utilisent la compression standard GZip. Si votre client peut gérer la compression GZip (par exemple, un navigateur qui comprend le contenu gzip), vous pouvez renvoyer directement le contenu compressé. Notez qu'il s'agit d'un compromis entre performance et stockage. Si vous interrogez fréquemment des données compressées, vous risquez d'avoir des performances plus faibles car le texte doit être décompressé à chaque fois.
Remarque : les fonctions JSON sont disponibles uniquement dans SQL Server 2016+ et Azure SQL Database.
Pour en savoir plus, consultez la source de cet article.
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/23/storing-json-in-sql-server/
2 votes
J'imagine que l'interrogation sur des propriétés spécifiques dans le JSON lui-même pourrait entraîner des goulots d'étranglement. S'il y a des champs spécifiques qui sont nécessaires pour l'interrogation dans le JSON, ils pourraient être des candidats pour l'extraction dans leur propre colonne. Certaines bases de données ont même des types de données "json", mais je ne sais pas quel genre d'optimisations sont faites en utilisant ce type.
0 votes
Merci de poser cette question. Vous m'avez épargné beaucoup d'ennuis, car il était difficile pour moi de passer d'une base de données NoSQL à une base de données SQL.