Types de données avancés dans PostgreSQL

Introduction

Dans ce laboratoire, vous explorerez les types de données avancés de PostgreSQL, en vous concentrant sur JSON/JSONB, les tableaux et les UUID. Vous apprendrez à stocker, interroger et manipuler des données au sein de ces types.

Le laboratoire commence par démontrer comment stocker et interroger des données JSON et JSONB, notamment en créant une table avec une colonne JSONB, en insérant des données JSON et en utilisant des opérateurs comme -> et ->> pour extraire des valeurs spécifiques. Vous apprendrez ensuite à utiliser les colonnes de type tableau et les UUID.

Stocker et interroger des données JSON et JSONB

Dans cette étape, vous apprendrez à stocker et interroger des données JSON et JSONB dans PostgreSQL. PostgreSQL propose deux types de données pour stocker du JSON : JSON et JSONB. Le type JSON stocke une copie exacte du texte JSON saisi, tandis que le type JSONB stocke les données JSON dans un format binaire décomposé. JSONB est généralement préféré car il offre de meilleures performances pour les requêtes et l'indexation.

Commençons par ouvrir le shell PostgreSQL. Connectez-vous d'abord à la base de données labex :

sudo -u postgres psql -d labex

Vous devriez voir l'invite de commande PostgreSQL :

labex=#

Maintenant, créons une table pour stocker des données JSONB :

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

Cette commande SQL crée une table nommée products. La table possède deux colonnes : id (une clé primaire entière auto-incrémentée) et data (une colonne JSONB pour stocker les données JSON).

Vous devriez voir un résultat similaire à celui-ci :

CREATE TABLE

Insérons maintenant quelques données dans la table products :

INSERT INTO products (data) VALUES ('{"name": "Laptop", "price": 1200, "features": ["16GB RAM", "512GB SSD"]}');
INSERT INTO products (data) VALUES ('{"name": "Keyboard", "price": 75, "features": ["Mechanical", "RGB Backlight"]}');

Ces commandes insèrent deux lignes dans la table products. Chaque ligne contient un objet JSON avec des informations sur un produit.

Vous devriez voir un résultat similaire à celui-ci pour chaque insertion :

INSERT 0 1

Pour interroger les données JSON, vous pouvez utiliser les opérateurs -> et ->>. L'opérateur -> renvoie un objet JSON, tandis que l'opérateur ->> renvoie une valeur JSON sous forme de texte.

Par exemple, pour récupérer le nom du premier produit, vous pouvez utiliser la requête suivante :

SELECT data ->> 'name' FROM products WHERE id = 1;

Cette commande sélectionne la valeur associée à la clé name dans la colonne data de la table products, là où l' id est 1. L'opérateur ->> garantit que le résultat est renvoyé sous forme de texte.

Vous devriez voir un résultat similaire à celui-ci :

  ?column?
----------
 Laptop
(1 row)

Vous pouvez également interroger des objets JSON imbriqués. Par exemple, pour récupérer la première fonctionnalité du premier produit, vous pouvez utiliser la requête suivante :

SELECT data -> 'features' ->> 0 FROM products WHERE id = 1;

Cette commande sélectionne d'abord le tableau features depuis la colonne data, puis sélectionne l'élément à l'index 0 du tableau. L'opérateur ->> garantit que le résultat est renvoyé sous forme de texte.

Vous devriez voir un résultat similaire à celui-ci :

  ?column?
----------
 16GB RAM
(1 row)

Vous pouvez également utiliser l'opérateur @> pour vérifier si un objet JSON contient une paire clé-valeur spécifique. Par exemple, pour trouver tous les produits dont le prix est de 75, vous pouvez utiliser la requête suivante :

SELECT data ->> 'name' FROM products WHERE data @> '{"price": 75}';

Cette commande sélectionne le nom de toutes les lignes de la table products où la colonne data contient un objet JSON avec une clé price et une valeur de 75.

Vous devriez voir un résultat similaire à celui-ci :

  ?column?
----------
 Keyboard
(1 row)

Parfait ! Nous avons créé avec succès notre première table avec des données JSONB et appris à les interroger. Nous continuerons à travailler avec cette table à l'étape suivante pour ajouter des fonctionnalités plus avancées.

Ajouter des colonnes de type tableau à une table existante

Dans cette étape, vous apprendrez à ajouter et manipuler des colonnes de type tableau dans PostgreSQL. Nous allons étendre notre table products existante en ajoutant une colonne de type tableau pour stocker des étiquettes (tags). Les colonnes de type tableau permettent de stocker plusieurs valeurs du même type de données dans une seule colonne, ce qui est utile pour stocker des listes d'éléments tels que des étiquettes, des catégories ou des fonctionnalités.

Puisque nous sommes déjà connectés à la base de données et que nous avons notre table products de l'étape précédente, ajoutons des colonnes de type tableau à notre table existante :

ALTER TABLE products ADD COLUMN name VARCHAR(255);
ALTER TABLE products ADD COLUMN tags TEXT[];

Ces commandes ajoutent deux nouvelles colonnes à notre table products existante : name (une chaîne de caractères) et tags (un tableau de chaînes). Le type de données TEXT[] spécifie que la colonne tags est un tableau de valeurs textuelles.

Vous devriez voir un résultat similaire à celui-ci pour chaque commande ALTER :

ALTER TABLE

Maintenant, mettons à jour nos données existantes et insérons de nouvelles données avec les nouvelles colonnes :

UPDATE products SET name = data ->> 'name' WHERE id = 1;
UPDATE products SET name = data ->> 'name' WHERE id = 2;
UPDATE products SET tags = ARRAY['electronics', 'computers', 'portable'] WHERE id = 1;
UPDATE products SET tags = ARRAY['electronics', 'accessories', 'input'] WHERE id = 2;

Ces commandes mettent à jour nos produits existants avec les informations de nom et d'étiquettes extraites des données JSONB et des nouvelles valeurs de tableau.

Vous devriez voir un résultat similaire à celui-ci pour chaque mise à jour :

UPDATE 1

Pour interroger les données de type tableau, vous pouvez utiliser l'indexation de tableau. Les index de tableau dans PostgreSQL commencent à 1.

Par exemple, pour récupérer la première étiquette du premier produit, vous pouvez utiliser la requête suivante :

SELECT tags[1] FROM products WHERE id = 1;

Cette commande sélectionne l'élément à l'index 1 du tableau tags de la table products, là où l' id est 1.

Vous devriez voir un résultat similaire à celui-ci :

   tags
-----------
 electronics
(1 row)

Vous pouvez également utiliser la fonction UNNEST pour développer un tableau en un ensemble de lignes.

Par exemple, pour récupérer toutes les étiquettes de tous les produits, vous pouvez utiliser la requête suivante :

SELECT name, UNNEST(tags) AS tag FROM products;

Cette commande sélectionne le name et chaque tag individuel du tableau tags, en créant une nouvelle ligne pour chaque étiquette.

Vous devriez voir un résultat similaire à celui-ci :

  name   |     tag
---------+-------------
 Laptop  | electronics
 Laptop  | computers
 Laptop  | portable
 Keyboard| electronics
 Keyboard| accessories
 Keyboard| input
(6 rows)

Vous pouvez utiliser l'opérateur @> pour vérifier si un tableau contient une valeur spécifique.

Par exemple, pour trouver tous les produits avec l'étiquette 'electronics', vous pouvez utiliser la requête suivante :

SELECT name FROM products WHERE tags @> ARRAY['electronics'];

Cette commande sélectionne le nom de toutes les lignes de la table products où le tableau tags contient la valeur 'electronics'.

Vous devriez voir un résultat similaire à celui-ci :

  name
----------
 Laptop
 Keyboard
(2 rows)

Vous pouvez également utiliser l'opérateur && pour vérifier si deux tableaux ont des éléments en commun.

Par exemple, pour trouver tous les produits qui partagent des étiquettes avec le premier produit, vous pouvez utiliser la requête suivante :

SELECT p2.name FROM products p1, products p2 WHERE p1.id = 1 AND p1.tags && p2.tags AND p2.id != 1;

Cette commande sélectionne le nom de toutes les lignes de la table products (alias p2) qui ont au moins une étiquette en commun avec les étiquettes du premier produit (alias p1), en excluant le premier produit lui-même.

Vous devriez voir un résultat similaire à celui-ci :

  name
----------
 Keyboard
(1 row)

Parfait ! Nous avons ajouté avec succès des colonnes de type tableau à notre table existante et appris à travailler avec les tableaux. Notre table products contient désormais à la fois des types de données JSONB et tableau, ce qui la rend prête pour l'étape suivante.

Ajouter une colonne UUID et apprendre la génération d'UUID

Dans cette étape, vous apprendrez à générer et à utiliser des UUID (Universally Unique Identifiers) dans PostgreSQL. Les UUID sont des nombres de 128 bits conçus pour être uniques dans l'espace et le temps. Ils sont souvent utilisés comme identifiants uniques dans les tables de base de données pour éviter les conflits lors de la fusion de données provenant de différentes sources.

Comme nous poursuivons avec notre session et notre table existantes, nous allons ajouter une colonne UUID à notre table products actuelle pour démontrer la fonctionnalité UUID.

Tout d'abord, activons l'extension UUID qui fournit les fonctions de génération d'UUID :

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Cette commande crée l'extension uuid-ossp si elle n'existe pas déjà.

Vous devriez voir un résultat similaire à celui-ci :

CREATE EXTENSION

Maintenant, ajoutons une colonne UUID à notre table products existante :

ALTER TABLE products ADD COLUMN uuid_id UUID;

Cette commande ajoute une nouvelle colonne UUID à notre table products existante.

Vous devriez voir un résultat similaire à celui-ci :

ALTER TABLE

Nous pouvons maintenant mettre à jour nos lignes existantes avec des valeurs UUID en utilisant la fonction uuid_generate_v4() :

UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 1;
UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 2;

Ces commandes mettent à jour nos produits existants avec des valeurs UUID uniques. La fonction uuid_generate_v4() génère un nouvel UUID pour chaque ligne.

Vous devriez voir un résultat similaire à celui-ci pour chaque mise à jour :

UPDATE 1

Pour interroger les données en utilisant les UUID, vous pouvez utiliser les valeurs UUID dans vos clauses WHERE. Comme les UUID sont générés de manière aléatoire, visualisons d'abord nos données actuelles :

SELECT id, name, uuid_id FROM products;

Cette commande sélectionne toutes les colonnes de la table products pour voir les UUID générés aux côtés de nos autres données.

Vous devriez voir un résultat similaire à celui-ci (votre UUID sera différent) :

 id |   name   |               uuid_id
----+----------+--------------------------------------
  1 | Laptop   | 8f14e45f-ea7b-4f9f-a2b0-73f9c3f85a9b
  2 | Keyboard | c9f0f895-fb98-4635-bd31-4f7f4d8f9e7a
(2 rows)

Maintenant, utilisez cet UUID dans la commande suivante, en remplaçant <YOUR_UUID_HERE> par l'UUID réel que vous avez récupéré :

SELECT name FROM products WHERE uuid_id = '<YOUR_UUID_HERE>';

Cette commande sélectionne le name de la table products là où l' uuid_id correspond à l'UUID spécifié.

Vous devriez voir un résultat similaire à celui-ci (selon l'UUID que vous avez choisi) :

  name
----------
 Laptop
(1 row)

Gardez la table products et l'extension uuid-ossp en place, car l'étape suivante continue de les utiliser.

Extraire des données de types avancés

Dans cette dernière étape, vous vous entraînerez à extraire des données de tous les types de données avancés que nous avons ajoutés à notre table products tout au long de ce laboratoire. Notre table contient désormais des colonnes JSONB, tableau et UUID, offrant un exemple complet des types de données avancés de PostgreSQL.

Comme nous poursuivons avec notre session et notre table existantes, nous pouvons immédiatement commencer à travailler avec les données que nous avons construites au cours des étapes précédentes.

Tout d'abord, ajoutons des données JSONB supplémentaires pour rendre nos exemples d'extraction plus complets :

UPDATE products SET data = '{"brand": "Dell", "model": "XPS 13", "specs": {"ram": "16GB", "storage": "512GB SSD"}, "warranty": "3 years"}' WHERE id = 1;
UPDATE products SET data = '{"brand": "Logitech", "model": "G915", "specs": {"type": "Mechanical", "backlight": "RGB"}, "warranty": "2 years"}' WHERE id = 2;

Ces commandes mettent à jour nos données JSONB existantes avec des informations plus détaillées, y compris des objets imbriqués et des champs supplémentaires.

Vous devriez voir un résultat similaire à celui-ci pour chaque mise à jour :

UPDATE 1

Entraînons-nous maintenant à extraire des données de tous nos types de données avancés. Pour extraire des données de la colonne JSONB data, vous pouvez utiliser les opérateurs -> et ->>. Par exemple, pour extraire la marque de nos données mises à jour :

SELECT data ->> 'brand' FROM products WHERE id = 1;

Cette commande récupère la valeur associée à la clé brand depuis la colonne data.

Vous devriez voir un résultat similaire à celui-ci :

 ?column?
----------
 Dell
(1 row)

Pour extraire des données imbriquées de la colonne JSONB, vous pouvez enchaîner les opérateurs -> et ->>. Par exemple, pour extraire la spécification RAM :

SELECT data -> 'specs' ->> 'ram' FROM products WHERE id = 1;

Cette commande récupère la valeur associée à la clé ram au sein de l'objet specs.

Vous devriez voir un résultat similaire à celui-ci :

 ?column?
----------
 16GB
(1 row)

Pour extraire des données de la colonne tags (tableau), vous pouvez utiliser l'indexation de tableau, comme appris à l'étape 2. Par exemple, pour extraire la première étiquette du premier produit :

SELECT tags[1] FROM products WHERE id = 1;

Cette commande récupère l'élément à l'index 1 du tableau tags de la table products là où l' id est 1.

Vous devriez voir un résultat similaire à celui-ci :

   tags
-----------
 electronics
(1 row)

Créons maintenant une requête complète qui extrait des données de tous nos types de données avancés - JSONB, tableaux et UUID :

SELECT
    id,
    name,
    data ->> 'brand' AS brand,
    data -> 'specs' ->> 'ram' AS ram,
    tags[1] AS first_tag,
    uuid_id
FROM products;

Cette commande récupère des données de tous les types de données avancés avec lesquels nous avons travaillé : l'entier id, le name, la brand depuis la colonne JSONB data, la spécification ram imbriquée, le premier élément du tableau tags, et l' uuid_id.

Vous devriez voir un résultat similaire à celui-ci :

   name   |  ?column?  |   tags
----------+------------+-----------
 Laptop   | Dell       | electronics
 Keyboard | Logitech   | electronics
(2 rows)

Excellent ! Vous avez travaillé avec succès avec les trois types de données avancés de PostgreSQL dans une seule table. Cet exemple complet démontre comment JSONB, les tableaux et les UUID peuvent être utilisés ensemble pour créer des schémas de base de données flexibles et puissants.

Lorsque vous avez terminé toutes les étapes, vous pouvez quitter le shell PostgreSQL en tapant :

\q

Vous pouvez également choisir de conserver la table pour approfondir vos expérimentations avec les types de données avancés de PostgreSQL.

Résumé

Dans ce laboratoire, vous avez progressivement construit et travaillé avec une table products complète qui illustre les types de données avancés de PostgreSQL en action. Vous avez commencé par créer une table avec des colonnes JSONB et avez appris à stocker et interroger des données JSON en utilisant des opérateurs comme -> et ->>.

Vous avez ensuite étendu la table en ajoutant des colonnes de type tableau, en apprenant à stocker plusieurs valeurs dans une seule colonne et à les interroger en utilisant l'indexation de tableau et des fonctions comme UNNEST. Ensuite, vous avez ajouté la fonctionnalité UUID en activant l'extension uuid-ossp et en ajoutant une colonne UUID pour générer des identifiants uniques.

Enfin, vous avez pratiqué des techniques d'extraction de données complètes qui combinaient les trois types de données avancés - JSONB, tableaux et UUID - dans des requêtes sophistiquées. Cette approche étape par étape a démontré comment ces types de données peuvent fonctionner ensemble dans des schémas de base de données réels, offrant flexibilité et capacités de requête puissantes pour les applications modernes.