Fonctionnalités JSON dans Azure SQL Database
Il y a maintenant quelques mois, les fonctionnalités JSON dans Azure SQL Database sont passées en public preview : ces fonctionnalités ouvrent de nouvelles possibilités pour traiter des données stockées au format JSON dans une base SQL Azure, cela est un avantage non négligeable pour Azure SQL database puisque cela améliore son interopérabilité avec les autres services Azure qui utilisent le format json (Document DB par exemple !)
Overview des commandes T-SQL
Pour travailler sur un JSON stocké dans une colonne de type « nvarchar(max) », plusieurs commandes T-SQL sont maintenant à notre disposition :
- ISJON : permet de vérifier si une colonne contient un JSON valide
- JSON_VALUE : Permet d’extraire une donnée d’une chaine JSON
- JSON_QUERY : Permet d’extraire un objet ou un tableau d’une chaine JSON
- JSON_MODIFY : Permet de modifier une valeur d’une chaine JSON
- OPENJSON : Permet de convertir des données JSON en lignes et colonnes SQL
- FOR JSON : Permet d’exporter une requête de type SELECT au format JSON
Voyons maintenant comment utiliser ces commandes avec un cas précis.
Le cas d’étude
Pour découvrir comment utiliser ces nouvelles fonctionnalités, rien de mieux qu’un exemple applicatif simple.
Imaginons une plateforme web d’upload de vidéo, depuis laquelle de nombreux utilisateurs peuvent ajouter différents types de vidéos : Bandes annonces de films, bandes annonces d’émissions TV, spot publicitaires…
Toutes les vidéos sont caractérisées par les informations suivantes :
- Le titre
- Le type de la vidéo
- Le nom du fichier uploadé (et son extension)
- la date d’upload
En plus de ces informations “de base”, des métadonnées doivent pouvoir être associées à chaque vidéo. Selon le type de vidéo, les métadonnées diffèrent :
La base de donnée :
Pour répondre à ce besoin, il est nécessaire de mettre en place une petite base donnée (Azure SQL Database V12) composée de 3 tables :
- « Video » : Contient les informations des vidéos uploadées sur la plateforme
- « VideoType » : Définit les types de vidéos
- « ProductionCompanyReference » : Table de référence qui contient des compagnies de production
Notre table « Video » va posséder au total 5 colonnes : Id | VideoTypeId | Title | FileBaseName | UploadDate | Metadata
Voici le script de création des différentes tables :
CREATE TABLE ProductionsCompanyReference
(
[Name] NVARCHAR(200) NOT NULL,
[Location] NVARCHAR(200) NOT NULL,
PRIMARY KEY CLUSTERED ([Name] ASC)
)
CREATE TABLE VideoType
(
[Id] INT IDENTITY(1,1) PRIMARY KEY,
[Type] NVARCHAR(200) NOT NULL,
)
CREATE TABLE Video
(
[Id] INT IDENTITY(1,1) PRIMARY KEY,
[VideoTypeId] INT NOT NULL,
[Title] NVARCHAR(300) NOT NULL,
[FileBaseName] NVARCHAR(300) NOT NULL,
[UploadDate] DATETIME DEFAULT(GETDATE()),
[Metadata] NVARCHAR(MAX) NOT NULL,
CONSTRAINT [FK_VideoType] FOREIGN KEY ([VideoTypeId]) REFERENCES [VideoType]([Id])
)
-- Insert Video types
INSERT INTO [dbo].[VideoType] ([Type]) VALUES ('TrailerTv')
INSERT INTO [dbo].[VideoType] ([Type]) VALUES ('TrailerMovie')
INSERT INTO [dbo].[VideoType] ([Type]) VALUES ('Advertisement')
-- Insert Production Companies
INSERT INTO [dbo].[ProductionsCompanyReference] ([Name] ,[Location]) VALUES ('Fox Studio', 'France')
INSERT INTO [dbo].[ProductionsCompanyReference] ([Name] ,[Location]) VALUES ('Monso trailers', 'France')
INSERT INTO [dbo].[ProductionsCompanyReference] ([Name] ,[Location]) VALUES ('Studio 200', 'US')
La colonne « Metadata » de type « nvarchar(max) » va stocker un JSON qui contiendra les métadonnées de la vidéo. La structure de ce json va donc différer selon le type de vidéo.
// Movie Trailer Metadata
{
"availibityDate" : "2016-02-10T00:00:00Z",
"productor" : "MyProducteur",
"actors" : ["actor1", "actor2"],
"public" : 0,
"productionCompany" : "Fox Studio"
}
// Tv Trailer Metadata
{
"channel" :"Channel1",
"diffusionDate" : "2016-08-10T10:00:00Z",
"presenter" : "presenter1",
"productionCompany" : "Monso trailers"
}
// Advertisement Metadata
{
"brand" :"MyBrand1",
"public" : 0
}
Insertion de données :
Essayons maintenant d’insérer une ligne dans la table vidéo, la vidéo de test sera de type « TvTrailer ». Dans ce scénario, un objet JSON contenant les données de notre « video » sert de source de données et aucune déserialisation applicative ne sera nécessaire puisque c’est directement en T-SQL par l’intermédiaire de la commande “OPEN_JSON” que nous traitons la donnée au format JSON pour l’insérer dans la table « Video » :
DECLARE @json nvarchar(max);
SET @json =
N'{
"typeId" : "1",
"title" : "Incroyable Talent",
"fileName" : "Incroyable_Talent.mov",
"uploadDate" : "2016-05-11T10:00:00Z",
"infos" : {
"channel" :"channel1",
"diffusionDate" : "2016-08-10T10:00:00Z",
"presenter" : "Oliver Rozac",
"productionCompany" : "Fox Studio"
}
}'
INSERT INTO dbo.Video
SELECT *
FROM OPENJSON(@json)
WITH (
VideoTypeId int '$.typeId',
Title nvarchar(300) '$.title',
FileBaseName nvarchar(300) '$.fileName',
UploadDate Datetime '$.uploadDate',
Metadata nvarchar(max) '$.infos' AS JSON
)
Dans la clause « With » on remarque l’utilisation du mot clef « AS JSON » pour mapper la colonne « Metadata » avec le nœud « infos » de la source de données. Ce mot clef permet de stocker un bloc de donnée au format JSON.
Grâce à la colonne « Metadata », on peut ainsi ajouter de nouvelles lignes dans la table « Video » avec des types de vidéos et des métadonnées différentes, sans toucher au schéma de la base :
-- Advertisement
SET @json =
N'{
"typeId" : "2",
"title" : "Coupe du monde 2016",
"fileName" : "world_cup_2016.mov",
"uploadDate" : "2016-05-11T10:00:00Z",
"infos" : {
"brand" :"Fifa",
"public" : 0
}
}'
INSERT INTO dbo.Video
SELECT *
FROM OPENJSON(@json)
WITH (
VideoTypeId int '$.typeId',
Title nvarchar(300) '$.title',
FileBaseName nvarchar(300) '$.fileName',
UploadDate Datetime '$.uploadDate',
Metadata nvarchar(max) '$.infos' AS JSON
)
-- Movie
SET @json =
N'{
"typeId" : "3",
"title" : "Night Run",
"fileName" : "night_run.avi",
"uploadDate" : "2016-05-11T10:00:00Z",
"infos" : {
"availibityDate" : "2016-05-15T10:00:00Z",
"productor" : "Steven Spielberg",
"actors" : ["Liam Neeson", "Ed Harris"],
"public" : 0,
"productionCompany" : "Studio 200"
}
}'
INSERT INTO dbo.Video
SELECT *
FROM OPENJSON(@json)
WITH (
VideoTypeId int '$.typeId',
Title nvarchar(300) '$.title',
FileBaseName nvarchar(300) '$.fileName',
UploadDate Datetime '$.uploadDate',
Metadata nvarchar(max) '$.infos' AS JSON
)
Ci-dessous les 3 vidéos insérées dans la table :
Update des données :
Une fois les données insérées en base, nous avons besoin de mettre à jour la vidéo « Night Run ». Il est nécessaire de modifier à la fois son « FileBaseName » ainsi que le nom du producteur du film, cette propriété est présente dans le json stocké dans le champ Metadata :
UPDATE Video
SET Metadata = JSON_MODIFY(Metadata, '$.productor', 'Jaume Collet-Serra'),
FileBaseName = 'night_run.mp4'
WHERE Id = '3'
Pour effectuer la modification, on utilise la fonction JSON_MODIFY, qui prend en entrée trois paramètres :
- La colonne contenant du json
- Le path de la propriété à modifier
- La nouvelle valeur
Dans le second paramètre, il est possible d’utiliser le mot clef « append », pour ajouter une valeur dans un tableau json :
UPDATE Video SET Metadata = JSON_MODIFY(Metadata, 'append $.actors', 'Joel Kinnaman') WHERE Id = '3'
Dans ce second exemple, nous ajout un acteur nommée « Joel Kinnaman » dans le tableau « actors ».
Récupération et tri de données :
Bien que la base de données ne contienne que trois entrées, c’est suffisant pour effectuer quelques requêtes en utilisant les fonctionnalités JSON. Avec la commande JSON_VALUE, il est possible de requêter sur un nœud JSON de manière transparente :
SELECT Title, JSON_VALUE(Metadata, '$.public') AS PublicTarget FROM Video
On peut également utiliser cette commande à l’intérieur d’une clause WHERE et ORDER BY :
SELECT Title, FileBaseName, JSON_VALUE(Metadata,'$.productionCompany') as ProductionCompany FROM Video WHERE CAST(JSON_VALUE(Metadata, '$.public') AS int) = 0 ORDER BY JSON_VALUE(Metadata, '$.productionCompany') ASC
Dans cette requête, nous récupérons tous les films “tout public”.
Dans les deux exemples on remarque que si l’on tente de récupérer la valeur d’un nœud json qui n’existe pas, la ligne est tout de même remontée mais la valeur non trouvée est considérée comme NULL.
Jointures à partir d’une propriété json :
Il est tout à fait possible d’utiliser la commande JSON_VALUE() afin d’effectuer des jointures en utilisant les données stockées au sein du JSON :
SELECT TITLE, JSON_VALUE(Metadata,'$.productionCompany') as ProductionCompany, Location FROM Video JOIN ProductionsCompanyReference on CAST(JSON_VALUE(Metadata, '$.productionCompany') as nvarchar(max)) LIKE ProductionsCompanyReference.Name
Mise en place d’un index sur une propriété JSON :
Actuellement « JSON » n’est pas un type de donnée dans SQL Server / Azure, il n’existe donc pas de « JSON Index », cependant il est possible d’utiliser les index standard pour optimiser les performances lors de requêtes utilisant des propriétés JSON.
L’idée est de mettre en place une nouvelle colonne qui va pointer sur la propriété JSON que l’on veut indexer. Dans notre cas, de nombreuses requêtes vont être effectuées en utilisant le nœud « ProductionCompany » qui est présent dans les métadonnées de tous les types de vidéos.
Par exemple :
SELECT Title, FileBaseName, JSON_VALUE(Metadata,'$.productionCompany') as ProductionCompany FROM Video WHERE JSON_VALUE(Metadata, '$.productionCompany') LIKE 'Fox Studio'
Pour gagner en performance sur cette requête, nous allons créer une nouvelle colonne nommée « ProductionCompany » dans la table « Video » et placer un index sur cette dernière, on parle de “ computed column “ :
ALTER TABLE Video ADD ProductionCompany AS JSON_VALUE(Metadata, '$.productionCompany') CREATE INDEX idx_json_ProductionCompany ON Video(ProductionCompany) Lors de l’exécution d’un requête qui utilise le path $.productionCompany sql server va rechercher si une “computed column” qui utilise ce path existe, si c’est le cas l’index est appliqué.
Pour aller plus loin sur la mise en place d’index, je vous renvoie vers la documentation.
Happy coding 
Commentaires