SQL Academy · Leçon

UNNEST et agrégation

Transformez des tableaux en lignes, et inversement.

UNNEST et agrégation est une leçon SQL Academy gratuite sur CoddyKit. Ceci est la leçon 3 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce que UNNEST

PostgreSQL vous permet de stocker des tableaux dans une seule colonne. Mais vous devez parfois travailler avec chaque élément individuellement — c'est là que UNNEST intervient.

UNNEST est une fonction qui renvoie un ensemble de résultats et qui développe un tableau en un ensemble de lignes, une ligne par élément. Considérez-la comme l'inverse d'une agrégation : au lieu de regrouper plusieurs lignes en une seule, elle transforme une valeur en plusieurs lignes.

Exemple de base avec UNNEST

L'utilisation la plus simple d'UNNEST consiste à lui transmettre directement un littéral de tableau. Chaque élément devient sa propre ligne dans l'ensemble de résultats.

Ici, nous développons un simple tableau d'entiers en lignes individuelles :

SELECT UNNEST(ARRAY[10, 20, 30, 40]) AS value;

UNNEST avec des tableaux de texte

UNNEST fonctionne avec tout type de tableau, y compris le texte. Cela est utile lorsque vous avez une colonne qui stocke des étiquettes, des catégories ou des données de type liste séparée par des virgules, encodées sous forme de tableau PostgreSQL.

SELECT UNNEST(ARRAY['apple', 'banana', 'cherry']) AS fruit;

UNNEST depuis une colonne de table

La véritable puissance d'UNNEST apparaît lorsque vous l'utilisez sur une colonne de table réelle. Chaque ligne de la table peut contenir un tableau de longueur différente, et UNNEST les développera tous en lignes individuelles.

Dans cet exemple, une table products possède une colonne de tableau de texte tags :

CREATE TABLE products (
  id   SERIAL PRIMARY KEY,
  name TEXT,
  tags TEXT[]
);

INSERT INTO products (name, tags) VALUES
  ('Laptop',  ARRAY['electronics', 'computing', 'portable']),
  ('Shirt',   ARRAY['clothing', 'casual']),
  ('Blender', ARRAY['kitchen', 'electronics']);

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

Comptabiliser les occurrences d'étiquettes

Une fois une colonne de tableau décomposée, vous pouvez traiter les résultats comme n'importe quelles autres lignes et leur appliquer des fonctions d'agrégation. Ici, nous comptons le nombre de produits associés à chaque étiquette.

Le modèle est le suivant : placez UNNEST dans une sous-requête ou une CTE, puis utilisez GROUP BY sur la valeur décomposée :

SELECT tag, COUNT(*) AS product_count
FROM (
  SELECT UNNEST(tags) AS tag
  FROM products
) AS expanded
GROUP BY tag
ORDER BY product_count DESC;

UNNEST avec ORDINALITY

La position d'un élément dans le tableau est parfois importante. PostgreSQL fournit WITH ORDINALITY pour associer un numéro de ligne à chaque élément développé, afin que vous connaissiez son indice d'origine dans le tableau (commençant à 1).

SELECT val, pos
FROM UNNEST(ARRAY['first', 'second', 'third']) WITH ORDINALITY AS t(val, pos);

Utiliser ORDINALITY sur une table

WITH ORDINALITY est particulièrement utile lorsque vous stockez des listes ordonnées dans un tableau. Par exemple, une table de listes de lecture où l'ordre des pistes est important :

CREATE TABLE playlists (
  id     SERIAL PRIMARY KEY,
  title  TEXT,
  tracks TEXT[]
);

INSERT INTO playlists (title, tracks) VALUES
  ('Morning Mix', ARRAY['Song A', 'Song B', 'Song C']);

SELECT p.title, track, position
FROM playlists p,
     UNNEST(p.tracks) WITH ORDINALITY AS t(track, position)
ORDER BY p.id, position;

Filtrer après UNNEST

Comme UNNEST transforme les éléments du tableau en lignes, vous pouvez les filtrer avec une clause WHERE normale. Cela vous permet de trouver toutes les lignes dont le tableau contient une valeur donnée sans utiliser l'opérateur de contenance des tableaux.

SELECT DISTINCT name
FROM products,
     UNNEST(tags) AS tag
WHERE tag = 'electronics';

Réagréger en tableau

L'inverse de UNNEST est ARRAY_AGG. Après avoir développé les lignes et les avoir transformées ou filtrées, vous pouvez rassembler les résultats dans un tableau. Ce modèle aller-retour — décomposer, traiter, réagréger — est une construction courante de PostgreSQL.

SELECT ARRAY_AGG(tag ORDER BY tag) AS sorted_tags
FROM (
  SELECT UNNEST(ARRAY['cherry', 'apple', 'banana']) AS tag
) AS t;

Suppression des doublons dans les tableaux

Une utilisation pratique de l'aller-retour entre UNNEST et l'agrégation consiste à supprimer les éléments en double d'un tableau. Appliquez UNNEST au tableau, utilisez DISTINCT, puis reconstituez-le avec ARRAY_AGG :

SELECT ARRAY_AGG(DISTINCT tag ORDER BY tag) AS unique_tags
FROM UNNEST(ARRAY['sql', 'database', 'sql', 'postgresql', 'database']) AS tag;

Combinaison de plusieurs colonnes de tableaux

Vous pouvez appliquer UNNEST à plusieurs tableaux en parallèle dans la clause FROM. PostgreSQL associe les éléments par position. Si les tableaux ont des longueurs différentes, le plus court produit des valeurs nulles pour les positions supplémentaires du plus long.

SELECT key, value
FROM UNNEST(
  ARRAY['name',    'city',      'role'],
  ARRAY['Alice',   'Istanbul',  'DBA']
) AS t(key, value);

Vérification des connaissances

Testez votre compréhension de UNNEST et de l'agrégation des tableaux dans PostgreSQL.

Récapitulatif de la leçon

Dans cette leçon, vous avez appris à transformer des tableaux en lignes, puis à les reconstituer à l'aide des fonctions intégrées de PostgreSQL :

UNNEST — transforme un tableau en une ligne par élément et fonctionne aussi bien avec des littéraux qu'avec des colonnes de table.
WITH ORDINALITY — ajoute un index de position à chaque élément déplié afin de conserver son ordre d'origine dans le tableau.
Filtrage — après avoir déplié un tableau, vous pouvez utiliser WHERE comme avec n'importe quelles lignes ordinaires.
ARRAY_AGG — l'inverse de UNNEST ; regroupe les lignes dans un tableau, éventuellement avec ORDER BY ou DISTINCT.
UNNEST en parallèle — plusieurs tableaux de la clause FROM sont développés côte à côte et associés par position.

La maîtrise de ce schéma de dépliage, traitement et réagrégation vous permet de manipuler les données de tableaux avec toute la puissance d'expression de SQL.

Questions Fréquemment Posées

La leçon « UNNEST et agrégation » est-elle gratuite ?

Oui — le texte complet de « UNNEST et agrégation » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « UNNEST et agrégation » ?

Transformez des tableaux en lignes, et inversement. Tu pratiques SQL Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Academy ?

Aucune expérience préalable n'est requise. SQL Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 3 sur 4.

Combien de temps prend la leçon « UNNEST et agrégation » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Academy ?

Oui. Chaque leçon SQL Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

← Retour à SQL Academy