Opérations avancées sur JSON/JSONB dans PostgreSQL

Introduction

Dans ce laboratoire, nous explorerons les opérations avancées sur les types de données JSON/JSONB de PostgreSQL. Nous nous concentrerons sur l'amélioration des performances des requêtes et de la manipulation des données au sein des colonnes JSONB.

Vous apprendrez à créer des index GIN sur les champs JSONB pour optimiser les recherches, à interroger des structures JSON imbriquées, à mettre à jour des éléments spécifiques dans les colonnes JSONB et à agréger des données JSON pour le reporting et l'analyse.

Skills Graph

Indexer les champs JSONB avec GIN

Dans cette étape, nous allons explorer comment créer des index GIN (Generalized Inverted Index) sur les champs JSONB dans PostgreSQL. Les index GIN sont particulièrement utiles pour indexer les données JSONB car ils vous permettent de rechercher efficacement des clés et des valeurs dans les documents JSON.

Tout d'abord, connectez-vous à la base de données PostgreSQL en utilisant la commande psql en tant qu'utilisateur postgres :

sudo -u postgres psql

Maintenant, créons une table nommée products avec une colonne data de type JSONB :

CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    data JSONB
);

Ensuite, insérez des exemples de données dans la table products :

INSERT INTO products (data) VALUES
('{"name": "Laptop", "price": 1200, "features": ["16GB RAM", "512GB SSD"]}'),
('{"name": "Keyboard", "price": 75, "features": ["Mechanical", "RGB"]}'),
('{"name": "Mouse", "price": 30, "features": ["Wireless", "Ergonomic"]}'),
('{"name": "Monitor", "price": 300, "features": ["27 inch", "144Hz"]}');

Pour accélérer les requêtes sur les champs JSONB, nous pouvons créer un index GIN. Par exemple, pour indexer l'ensemble du document JSONB, exécutez la commande suivante :

CREATE INDEX idx_products_data ON products USING GIN (data);

Pour vérifier que l'index est créé, vous pouvez utiliser la commande \di dans psql :

\di idx_products_data

La sortie doit afficher l'index idx_products_data et ses détails.

Enfin, quittez l'interpréteur psql :

\q

Interroger les structures JSON imbriquées

Dans cette étape, nous allons apprendre à interroger les structures JSON imbriquées dans PostgreSQL en utilisant le type de données JSONB.

Tout d'abord, connectez-vous à la base de données PostgreSQL en utilisant la commande psql en tant qu'utilisateur postgres :

sudo -u postgres psql

Ajoutons des données imbriquées plus complexes à notre table products.

INSERT INTO products (data) VALUES
('{"name": "Gaming PC", "price": 1500, "specs": {"cpu": "Intel i7", "ram": "32GB", "storage": "1TB SSD", "gpu": "Nvidia RTX 3070"}}'),
('{"name": "Office PC", "price": 800, "specs": {"cpu": "Intel i5", "ram": "16GB", "storage": "500GB SSD", "gpu": "Integrated"}}');

Pour accéder à une clé dans une structure JSON imbriquée, vous pouvez chaîner l'opérateur ->. Par exemple, pour récupérer le CPU du "Gaming PC", vous utiliserez :

SELECT data -> 'specs' ->> 'cpu' FROM products WHERE name = 'Gaming PC';

L'opérateur ->> est utilisé pour récupérer la valeur sous forme de texte. Si vous utilisez ->, le résultat sera toujours un objet JSONB.

Vous pouvez également filtrer les lignes en fonction des valeurs dans les structures JSON imbriquées. Par exemple, pour trouver tous les produits avec un CPU Intel i5 :

SELECT * FROM products WHERE data -> 'specs' ->> 'cpu' = 'Intel i5';

Trouvons le nom du produit qui a un GPU Nvidia RTX 3070.

SELECT name FROM products WHERE data -> 'specs' ->> 'gpu' = 'Nvidia RTX 3070';

Cette requête devrait renvoyer "Gaming PC".

Enfin, quittez l'interpréteur psql :

\q

Mettre à jour des éléments JSONB spécifiques

Dans cette étape, nous allons apprendre à mettre à jour des éléments spécifiques dans une colonne JSONB dans PostgreSQL.

Tout d'abord, connectez-vous à la base de données PostgreSQL en utilisant la commande psql en tant qu'utilisateur postgres :

sudo -u postgres psql

Nous utiliserons la fonction jsonb_set pour mettre à jour des éléments spécifiques dans les données JSONB. La syntaxe de base est :

jsonb_set(target JSONB, path TEXT[], new_value JSONB, create_missing BOOLEAN)

Supposons que nous voulions mettre à jour le prix du "Laptop" à 1250.

UPDATE products
SET data = jsonb_set(data, '{price}', '1250'::JSONB)
WHERE name = 'Laptop';

Pour vérifier la mise à jour, vous pouvez exécuter la requête suivante :

SELECT data FROM products WHERE name = 'Laptop';

La sortie doit indiquer que le prix du "Laptop" a été mis à jour à 1250.

Augmentons le prix du "Office PC" de 100.

UPDATE products
SET data = jsonb_set(data, '{price}', ((data ->> 'price')::numeric + 100)::TEXT::JSONB)
WHERE name = 'Office PC';

Pour vérifier la mise à jour, vous pouvez exécuter la requête suivante :

SELECT data FROM products WHERE name = 'Office PC';

La sortie doit indiquer que le prix du "Office PC" a été augmenté de 100.

Enfin, quittez l'interpréteur psql :

\q

Agréger des données JSON

Dans cette étape, nous allons explorer comment agréger des données stockées dans des colonnes JSONB dans PostgreSQL.

Tout d'abord, connectez-vous à la base de données PostgreSQL en utilisant la commande psql en tant qu'utilisateur postgres :

sudo -u postgres psql

L'agrégation de données JSONB implique souvent l'extraction de valeurs à partir des objets JSONB, puis l'application de fonctions d'agrégation telles que SUM (SOMME), AVG (MOYENNE), MIN (MINIMUM), MAX (MAXIMUM) et COUNT (COMPTE).

Pour calculer le prix moyen de tous les produits, vous pouvez utiliser la requête suivante :

SELECT AVG((data ->> 'price')::numeric) FROM products;

Ici, nous extrayons le price (prix) sous forme de texte en utilisant ->>, nous le convertissons en un type numérique, puis nous calculons la moyenne en utilisant la fonction AVG.

Ajoutons un champ "category" (catégorie) à nos produits :

UPDATE products SET data = jsonb_set(data, '{category}', '"Electronics"'::JSONB) WHERE id IN (1,4,5);
UPDATE products SET data = jsonb_set(data, '{category}', '"Accessories"'::JSONB) WHERE id IN (2,3);
UPDATE products SET data = jsonb_set(data, '{category}', '"Computers"'::JSONB) WHERE id IN (6);

Maintenant, nous pouvons compter le nombre de produits dans chaque catégorie :

SELECT data ->> 'category', COUNT(*) FROM products GROUP BY data ->> 'category';

Cette requête extrait la valeur category (catégorie) sous forme de texte et regroupe les lignes en fonction de cette valeur.

Calculons le prix total de tous les produits dans la catégorie "Electronics" (Électronique).

SELECT SUM((data ->> 'price')::numeric) FROM products WHERE data ->> 'category' = 'Electronics';

Cette requête devrait renvoyer la somme des prix du Laptop (Ordinateur portable), du Monitor (Moniteur) et du Gaming PC (PC de jeu).

Enfin, quittez l'interpréteur psql :

\q

Résumé

Dans ce labo, nous avons exploré les opérations avancées sur les données JSONB de PostgreSQL, en nous concentrant sur l'indexation, l'interrogation (querying), la mise à jour (updating) et l'agrégation. Nous avons créé des index GIN sur les champs JSONB pour optimiser les performances des requêtes. Nous avons également démontré comment interroger des structures JSON imbriquées et mettre à jour des éléments JSONB spécifiques. Enfin, nous avons appris à agréger des données JSON à l'aide de diverses fonctions.