SQL Academy · Lektion

UNNEST und Aggregation

Arrays in Zeilen und wieder zurück umwandeln

UNNEST und Aggregation ist eine kostenlose SQL Academy-Lektion auf CoddyKit. Dies ist Lektion 3 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des SQL Academy-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.

Was ist UNNEST?

PostgreSQL ermöglicht es Ihnen, Arrays in einer einzelnen Spalte zu speichern. Manchmal müssen Sie jedoch mit jedem Element einzeln arbeiten — hier kommt UNNEST ins Spiel.

UNNEST ist eine Funktion zur Rückgabe einer Ergebnismenge, die ein Array in eine Menge von Zeilen erweitert — eine Zeile pro Element. Stellen Sie sich UNNEST als das Gegenstück zur Aggregation vor: Statt viele Zeilen zu einer zusammenzufassen, zerlegt es einen Wert in viele Zeilen.

Einfaches UNNEST-Beispiel

Die einfachste Verwendung von UNNEST besteht darin, ein Array-Literal direkt zu übergeben. Jedes Element wird zu einer eigenen Zeile in der Ergebnismenge.

Hier lösen wir ein einfaches Array aus Ganzzahlen in einzelne Zeilen auf:

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

UNNEST mit Text-Arrays

UNNEST funktioniert mit jedem Array-Typ, auch mit Text. Das ist nützlich, wenn eine Spalte Tags, Kategorien oder Daten im Stil einer kommagetrennten Liste speichert, die als PostgreSQL-Array codiert sind.

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

UNNEST aus einer Tabellenspalte

Die eigentliche Stärke von UNNEST zeigt sich, wenn Sie es auf eine tatsächliche Tabellenspalte anwenden. Jede Tabellenzeile kann ein Array mit unterschiedlicher Länge enthalten, und UNNEST löst alle Arrays in einzelne Zeilen auf.

In diesem Beispiel verfügt eine products-Tabelle über eine Text-Array-Spalte 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;

Tag-Vorkommen zählen

Sobald Sie eine Array-Spalte mit UNNEST aufgelöst haben, können Sie die Ergebnisse wie jede anderen Zeilen behandeln und Aggregatfunktionen anwenden. Hier zählen wir, wie viele Produkte jedem Tag zugeordnet sind.

Das Muster lautet: UNNEST in einer Unterabfrage oder CTE verwenden und anschließend den aufgelösten Wert mit GROUP BY gruppieren:

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 mit Ordinalität

Manchmal ist die Position eines Elements innerhalb des Arrays wichtig. PostgreSQL stellt WITH ORDINALITY bereit, um jedem erweiterten Element eine Zeilennummer zuzuweisen, sodass Sie seinen ursprünglichen Index im Array kennen (beginnend bei 1).

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

Ordinalität auf eine Tabelle anwenden

WITH ORDINALITY ist besonders hilfreich, wenn Sie geordnete Listen in einem Array speichern. Zum Beispiel bei einer Playlist-Tabelle, in der die Reihenfolge der Titel wichtig ist:

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;

Nach UNNEST filtern

Da UNNEST Array-Elemente in Zeilen umwandelt, können Sie sie mit einer normalen WHERE-Klausel filtern. So finden Sie alle Zeilen, deren Array einen bestimmten Wert enthält, ohne den Array-Enthält-Operator zu verwenden.

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

Wieder zu einem Array aggregieren

Das Gegenstück zu UNNEST ist ARRAY_AGG. Nachdem Sie Zeilen erweitert und sie transformiert oder gefiltert haben, können Sie die Ergebnisse wieder in einem Array sammeln. Dieses Hin-und-zurück-Muster — auflösen, verarbeiten, wieder aggregieren — ist ein gängiges PostgreSQL-Idiom.

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

Doppelte Array-Elemente entfernen

Eine praktische Anwendung der Unnest-Aggregat-Rücktransformation ist das Entfernen doppelter Elemente aus einem Array. Wenden Sie UNNEST auf das Array an, verwenden Sie DISTINCT und sammeln Sie die Elemente anschließend mit ARRAY_AGG wieder ein:

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

Mehrere Array-Spalten kombinieren

Sie können mehrere Arrays in der FROM-Klausel parallel mit UNNEST verarbeiten. PostgreSQL ordnet die Elemente anhand ihrer Position einander zu. Wenn die Arrays unterschiedlich lang sind, liefert das kürzere Array für die zusätzlichen Positionen des längeren Arrays NULL-Werte.

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

Wissenscheck

Testen Sie Ihr Verständnis von UNNEST und der Aggregation von Arrays in PostgreSQL.

Zusammenfassung der Lektion

In dieser Lektion haben Sie gelernt, wie Sie Arrays mithilfe der integrierten PostgreSQL-Funktionen in Zeilen umwandeln und wieder zurück:

UNNEST — erweitert ein Array zu einer Zeile pro Element und funktioniert sowohl mit Literalen als auch mit Tabellenspalten.
WITH ORDINALITY — fügt jedem entpackten Element einen Positionsindex hinzu, sodass Sie seine ursprüngliche Reihenfolge im Array kennen.
Filtern — nach dem Entpacken können Sie WHERE genauso wie bei gewöhnlichen Zeilen verwenden.
ARRAY_AGG — das Gegenstück zu UNNEST: Sammelt Zeilen wieder in einem Array, optional mit ORDER BY oder DISTINCT.
Paralleles UNNEST — mehrere Arrays in der FROM-Klausel werden nebeneinander erweitert und anhand ihrer Position einander zugeordnet.

Wenn Sie dieses Muster aus Entpacken, Verarbeiten und erneuter Aggregation beherrschen, können Sie Array-Daten mit der vollen Ausdrucksstärke von SQL verarbeiten.

Häufig gestellte Fragen

Ist die Lektion „UNNEST und Aggregation“ kostenlos?

Ja — der vollständige Text von „UNNEST und Aggregation“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des SQL Academy-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.

Was lerne ich in „UNNEST und Aggregation“?

Arrays in Zeilen und wieder zurück umwandeln Du übst SQL Academy mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um SQL Academy zu starten?

Keine Vorkenntnisse erforderlich. SQL Academy auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 3 von 4.

Wie lange dauert die Lektion „UNNEST und Aggregation“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser SQL Academy-Lektion Code schreiben und ausführen?

Ja. Jede SQL Academy-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

← Zurück zu SQL Academy