Introduction
Dans ce laboratoire, vous explorerez les opérations avancées utilisant le type de données JSONB de PostgreSQL. Vous apprendrez à stocker, gérer et interroger efficacement les données JSON au sein d'une base de données relationnelle.
Ce laboratoire vous guidera à travers la création d'une table avec une colonne JSONB, l'insertion de données, puis l'application d'un index GIN pour améliorer les performances de recherche. Vous pratiquerez également l'interrogation de structures JSON imbriquées, la mise à jour d'éléments spécifiques dans un document JSONB et l'exécution de calculs agrégés sur vos données JSON.
Créer une table et indexer les champs JSONB
Dans cette étape, vous allez créer une table pour stocker les informations sur les produits en utilisant le type de données JSONB, puis créer un index GIN pour optimiser les requêtes sur ces données. JSONB stocke les données JSON dans un format binaire décomposé, ce qui est légèrement plus lent à l'entrée mais beaucoup plus rapide à traiter. Un index GIN (Generalized Inverted Index) est idéal pour indexer des valeurs composites comme celles d'une colonne JSONB.
Tout d'abord, ouvrez un terminal et connectez-vous à la base de données PostgreSQL en utilisant le shell interactif psql :
sudo -u postgres psql
Vous verrez maintenant l'invite PostgreSQL, qui ressemble à postgres=#.
Ensuite, créez une table nommée products avec une colonne id et une colonne data de type JSONB.
CREATE TABLE products (
id SERIAL PRIMARY KEY,
data JSONB
);
Maintenant, insérez des données d'exemple de produits dans la table products.
INSERT INTO products (data) VALUES
('{"name": "Laptop", "price": 1200, "tags": ["electronics", "computer"]}'),
('{"name": "Keyboard", "price": 75, "tags": ["electronics", "accessory"]}'),
('{"name": "Mouse", "price": 30, "tags": ["electronics", "accessory"]}'),
('{"name": "Monitor", "price": 300, "tags": ["electronics", "display"]}');
Pour accélérer considérablement les recherches dans la colonne data, créez un index GIN sur celle-ci.
CREATE INDEX idx_products_data ON products USING GIN (data);
Vous pouvez vérifier que l'index a été créé avec succès en utilisant la commande \di, qui liste tous les index.
\di
Vous devriez voir idx_products_data dans la liste des relations, similaire à la sortie ci-dessous :
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+---------------------+-------+----------+-----------+------------+-------------
public | idx_products_data | index | postgres | products | 16 kB |
public | products_pkey | index | postgres | products | 16 kB |
(2 rows)
Vous avez configuré avec succès votre table et indexé la colonne JSONB. Vous resterez dans le shell psql pour l'étape suivante.
Interroger les structures JSON imbriquées
Dans cette étape, vous apprendrez à interroger des données dans des colonnes JSONB, y compris des structures imbriquées. PostgreSQL fournit plusieurs opérateurs à cet effet.
Tout d'abord, insérons des produits avec des données plus complexes et imbriquées dans la table products.
INSERT INTO products (data) VALUES
('{"name": "Gaming PC", "price": 1500, "specs": {"cpu": "Intel i7", "ram": "32GB", "storage": "1TB SSD"}}'),
('{"name": "Office PC", "price": 800, "specs": {"cpu": "Intel i5", "ram": "16GB", "storage": "500GB SSD"}}');
Pour accéder à une clé de premier niveau, vous pouvez utiliser l'opérateur ->>, qui renvoie la valeur sous forme de texte. Trouvons le prix de l'« Laptop ».
SELECT data ->> 'price' FROM products WHERE data ->> 'name' = 'Laptop';
La sortie sera :
price
-------
1200
(1 row)
Pour accéder à une clé dans un objet JSON imbriqué, vous pouvez chaîner les opérateurs -> et ->>. L'opérateur -> récupère un champ d'objet JSON, tandis que ->> le récupère sous forme de texte. Récupérons la spécification du CPU pour le « Gaming PC ».
SELECT data -> 'specs' ->> 'cpu' FROM products WHERE data ->> 'name' = 'Gaming PC';
Cette commande renverra le type de CPU :
?column?
--------------
Intel i7
(1 row)
Vous pouvez également utiliser ces opérateurs dans la clause WHERE pour filtrer les résultats en fonction des valeurs imbriquées. Par exemple, trouvez tous les produits qui ont un CPU « Intel i5 ».
SELECT data ->> 'name' FROM products WHERE data -> 'specs' ->> 'cpu' = 'Intel i5';
La requête renverra le nom du produit correspondant :
name
-----------
Office PC
(1 row)
Vous êtes maintenant capable d'interroger des données de premier niveau et imbriquées dans vos colonnes JSONB.
Mettre à jour des éléments JSONB spécifiques
Dans cette étape, vous apprendrez à modifier des éléments spécifiques au sein d'une colonne JSONB en utilisant la fonction jsonb_set. C'est plus efficace que de récupérer l'intégralité de l'objet JSON, de le modifier dans votre application, puis de le réécrire.
La fonction jsonb_set a la syntaxe suivante : jsonb_set(target_jsonb, path_array, new_value_jsonb, create_if_missing).
Mettons à jour le prix du « Laptop » de 1200 à 1250. Le chemin vers le prix est '{price}', et la nouvelle valeur doit être convertie en JSONB.
UPDATE products
SET data = jsonb_set(data, '{price}', '1250'::jsonb)
WHERE data ->> 'name' = 'Laptop';
Vérifiez la mise à jour en sélectionnant les données du laptop.
SELECT data FROM products WHERE data ->> 'name' = 'Laptop';
La sortie devrait montrer le nouveau prix :
data
--------------------------------------------------------------------------
{"name": "Laptop", "price": 1250, "tags": ["electronics", "computer"]}
(1 row)
Vous pouvez également mettre à jour des valeurs imbriquées. Améliorons la RAM du « Office PC » à « 32GB ». Le chemin vers la clé imbriquée ram est '{specs,ram}'.
UPDATE products
SET data = jsonb_set(data, '{specs,ram}', '"32GB"'::jsonb)
WHERE data ->> 'name' = 'Office PC';
Maintenant, vérifiez cette mise à jour imbriquée.
SELECT data -> 'specs' FROM products WHERE data ->> 'name' = 'Office PC';
Le résultat confirmera la modification de la valeur ram :
?column?
------------------------------------------------------------
{"cpu": "Intel i5", "ram": "32GB", "storage": "500GB SSD"}
(1 row)
Vous avez maintenant appris à effectuer des mises à jour ciblées sur les données JSONB.
Agréger les données JSON
Dans cette dernière étape, vous effectuerez des calculs d'agrégation sur les données extraites des colonnes JSONB. Ceci est utile pour le reporting et l'analyse.
Pour effectuer des calculs, vous devez souvent extraire une valeur et la convertir en un type numérique. Calculons le prix moyen de tous les produits.
SELECT AVG((data ->> 'price')::numeric) FROM products;
La requête extrait le price sous forme de texte, le convertit en numeric, puis calcule la moyenne. La sortie sera un seul nombre :
avg
------------------------
659.1666666666666667
(1 row)
Vous pouvez également utiliser des fonctions d'agrégation avec une clause GROUP BY. Trouvons le nombre total de produits pour chaque tag. Pour ce faire, nous devons d'abord déplier le tableau tags en lignes séparées en utilisant jsonb_array_elements_text.
SELECT tag, COUNT(*)
FROM products, jsonb_array_elements_text(data -> 'tags') AS tag
GROUP BY tag;
Cette requête produit un décompte pour chaque tag unique :
tag | count
-------------+-------
accessory | 2
electronics | 4
computer | 1
display | 1
(4 rows)
Enfin, trouvons la valeur totale de tous les produits qui ont le tag « electronics ».
SELECT SUM((data ->> 'price')::numeric)
FROM products
WHERE data -> 'tags' @> '"electronics"'::jsonb;
L'opérateur @> vérifie si la valeur JSONB de gauche contient la valeur JSONB de droite. Ici, nous vérifions si le tableau tags contient l'élément « electronics ».
Le résultat est la somme des prix des quatre produits électroniques :
sum
--------
1655
(1 row)
Vous avez maintenant appris à utiliser des fonctions d'agrégation sur vos données JSONB. Pour quitter le shell psql, tapez :
\q
Résumé
Dans ce laboratoire, vous avez appris plusieurs opérations avancées pour la gestion des données JSONB dans PostgreSQL. Vous avez commencé par créer une table avec une colonne JSONB et avez constaté l'importance de l'utilisation d'un index GIN pour optimiser les performances des requêtes. Vous vous êtes ensuite exercé à interroger des données JSONB de niveau supérieur et imbriquées en utilisant les opérateurs -> et ->>. De plus, vous avez appris à effectuer des modifications ciblées sur des documents JSONB avec la fonction jsonb_set et à réaliser des analyses de données puissantes à l'aide de fonctions d'agrégation telles que AVG, COUNT et SUM sur vos données JSON.
