La CTP3 de SQL Server 2016 vient d’être annoncée. C’est donc le moment de s’intéresser aux nouvelles fonctionnalités apportées par cette version. Aujourd’hui nous allons nous intéresser à la prise en charge du JSON.

Création d’une colonne de type JSON

Contrairement aux colonnes de type XML, les colonnes JSON n’ont pas de type particulier. On utilise donc le type nvarchar.

CREATE TABLE Sample_Json (
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Column_Json] [nvarchar](500) NULL
)

Pour s’assurer que la colonne ne contienne que des chaines au format JSON on peut ajouter une contrainte en utilisant la nouvelle fonction ISJSON :

ALTER TABLE Sample_Json
ADD CONSTRAINT Json_Constraint CHECK (ISJSON(Column_Json) = 1)

Pour ajouter une ligne, la syntaxe est toujours la même :

INSERT INTO Sample_Json(Column_Json)
VALUES ('{"FirstName":"Gérald", "LastName":"Barré", "Nickname":"Meziantou"}'),
       ('{"FirstName":"John", "LastName":"Doe", "PhoneNumbers": ["0123456789", "9876543210"]}')

Utilisation du JSON

SQL Server fourni plusieurs fonctions pour utiliser les données au format JSON.

JSON_VALUE

JSON_VALUE extrait une valeur d’une chaine au format JSON :

SELECT JSON_VALUE(Column_Json, '$.FirstName') FROM Sample_Json
-- 'Gérald', 'John'

La syntaxe est la suivante :

• $ : Elément du racine du JSON
• $.name ou $."first name" : Sélectionne une propriété de l’élément
• $.phoneNumbers[1] : Sélectionne le 2ème élément du tableau
• Combinaison : $.Friends[1].FirstName

JSON_QUERY

JSON_QUERY extrait un objet ou un tableau d’une chaine au JSON :

SELECT JSON_QUERY(Column_Json, '$.PhoneNumbers') FROM Sample_Json
-- NULL, '["0123456789", "9876543210"]'

OPENJSON

OPENJSON converti un objet JSON en table :

SELECT * FROM OPENJSON('{"Name":"John", "Age":20, "DateOfBirth": null, "Tags": ["Customer"], "PhoneNumbers": { "Home": "0123456789" }, "IsActive":true}')

On voit que SQL Server ajoute automatiquement une colonne type. Cette colonne correspond au type de la donnée :

• 0 : null
• 1 : string
• 2 : nombre
• 3 : booléen
• 4 : tableau
• 5 : objet

Il se peut que l’on souhaite ouvrir un sous-élément du JSON :

SELECT * FROM
OPENJSON (@json, '$.Tags')

Il est également possible de définir le schéma de la table créée :

DECLARE @json nvarchar(1024)
SET @json = '[{"Name": "Meziantou","DateOfBirth": "2000-11-11T00:00:00","OrderCount": 10},{"Name": "John","DateOfBirth": "1990-01-31T00:00:00","OrderCount": 2 }]'
SELECT * FROM OPENJSON(@json)
WITH ( 
    [Name] varchar(200) '$.Name' ,
    [DateOfBirth] datetime '$.DateOfBirth',
    [OrderCount] int '$.OrderCount'
) 

L’opérateur CROSS APPLY peut également s”avérer utile pour convertir les données du JSON en colonnes de la ligne :

SELECT * FROM Sample_Json
CROSS APPLY OPENJSON (Column_Json)
WITH(
	FirstName nvarchar(50) '$.FirstName',
	LastName nvarchar(50) '$.LastName'
)

Générer du JSON

Tout comme il es possible de générer du XML avec la clause FOR XML, il est désormais possible de générer du JSON avec la clause FOR JSON :

INSERT INTO Customer (Name, DateOfBirth, IsActive) VALUES
('Meziantou', '2000-01-01 00:00:00', 1),
('John', NULL, 0)
SELECT * FROM Customer
FOR JSON AUTO
-- [{"Id":1,"Name":"Meziantou","DateOfBirth":"2000-01-01T00:00:00","IsActive":true},{"Id":2,"Name":"John","IsActive":false}]

Par défaut, les valeurs NULL sont exclues. Pour les inclure, il faut spécifier INCLUDE_NULL_VALUES :

SELECT * FROM Customer
FOR JSON AUTO, INCLUDE_NULL_VALUES

Pour créer des sous-objets on peut utiliser FOR JSON PATH. Pour cela chaque . dans le nom d’une colonne se transforme en sous-objet :

SELECT Id [Customer.Id], Name [Customer.Name], DateOfBirth [Customer.DateOfBirth] FROM Customer
FOR JSON PATH, ROOT('Customers'), INCLUDE_NULL_VALUES
-- {"Customers":[{"Customer":{"Id":1,"Name":"Meziantou","DateOfBirth":"2000-01-01T00:00:00"}},{"Customer":{"Id":2,"Name":"John","DateOfBirth":null}}]}

Contraintes JSON

Lors de la création de la table, nous avons ajouté une contrainte sur la colonne JSON. Cette contrainte permet de valider que le contenu est bien du JSON. Il est également possible de créer des contraintes plus spécifique. Par exemple pour vérifier que le JSON contient une propriété âge de type nombre, on peut utiliser la fonction JSON_VALUE :

ALTER TABLE Sample_Json
ADD CONSTRAINT Age_Is_Number
CHECK (ISNUMERIC(JSON_VALUE(value, '$.age')) = 1)

On peut également utiliser la fonction JSON_QUERY :

ALTER TABLE Sample_Json
ADD CONSTRAINT Tags_Exist
CHECK (JSON_QUERY(value, '$.tags') IS NOT NULL)

Création d’un index

Pour créer une index sur une ou plusieurs propriétés du JSON il faut ajouter une colonne calculée :

ALTER TABLE Sample_Json ADD FirstName AS JSON_VALUE(Column_Json, '$.FirstName')
CREATE INDEX idx_FirstName ON Sample_Json(FirstName)

On peut ensuite requêter la table en utilisant la colonne calculée ou la fonction JSON_VALUE :

SELECT FirstName FROM Sample_Json
WHERE FirstName LIKE 'm%'
SELECT FirstName FROM Sample_Json
WHERE JSON_VALUE(Column_Json, '$.FirstName') LIKE 'm%'
SELECT JSON_VALUE(Column_Json, '$.FirstName') FROM Sample_Json
WHERE JSON_VALUE(Column_Json, '$.FirstName') LIKE 'm%'

Dans les 3 cas le plan d’exécution est identique et utilise l’index :

Conclusion

SQL Server 2016 vous permet désormais d’utiliser des données au format JSON. Cela peut-être très pratique, notamment lorsque vous voulez interagir avec d’autres systèmes tels que des bases NoSQL qui utilisent principalement ce format comme stockage.