Beispiele für komplexe Abfragen mit Arrays: Aggregation, Filterung, Sortierung

Heute schauen wir uns an, wie du das Maximum aus Arrays in deinen Abfragen rausholst: Werte gruppieren, nach Inhalt filtern und sogar direkt in Arrays sortieren. Das ist kein Theorie-Gelaber – solche Techniken brauchst du in Reports, Analytics, Personalisierung und vielen echten Szenarien. Alles easy, wenn du das Prinzip checkst – und genau das machen wir jetzt.

Datenaggregation mit Arrays

Mit Arrays wird’s besonders spannend, wenn du Daten gruppieren willst. Anstatt mehrere Zeilen zu bekommen, sammelst du die gewünschten Werte in einem schicken Array. Das macht die Analyse einfacher, die Ergebnisse kompakter und du sparst dir oft unnötige Subqueries. Lass uns anschauen, wie das in der Praxis läuft.

Beispiel 1: Gruppieren von Daten in Arrays mit array_agg()

Wenn du Werte aus mehreren Zeilen in einer Gruppe in ein Array packen willst, hilft dir array_agg() weiter. Das ist wahrscheinlich die nützlichste Funktion für Array-Handling bei Aggregationen.

-- Wir haben eine Tabelle students mit den Feldern id, name und course
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

-- Wir fügen ein paar Zeilen ein
INSERT INTO students (name, course) VALUES
('Alisa', 'Mathematik'),
('Bob', 'Mathematik'),
('Charlie', 'Physik'),
('Dave', 'Physik'),
('Emma', 'Mathematik');

-- Wir gruppieren die Studenten nach Kursen in Arrays
SELECT course, array_agg(name) AS students
FROM students
GROUP BY course;

Ergebnis:

Werte in Arrays zu gruppieren ist praktisch, wenn du Daten in einem Format weitergeben willst, das sich easy z.B. in JSON zerlegen lässt.

Beispiel 2: Erstellen von verschachtelten Arrays

Und was, wenn wir noch eine Tabelle haben und Daten aus zwei Tabellen in Arrays sammeln wollen? Zum Beispiel eine courses-Tabelle mit Infos zu den Lehrern.

-- Wir erstellen eine Tabelle für Lehrer
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    teacher VARCHAR(100)
);

-- Wir fügen Daten ein
INSERT INTO courses (name, teacher) VALUES
('Mathematik', 'Prof. Min'),
('Physik', 'Prof. Peterson');

-- Verschachtelte Abfrage zum Erstellen von Arrays
SELECT
    c.name AS course_name,
    array_agg(s.name) AS students,
    c.teacher
FROM
    courses c
LEFT JOIN
    students s
ON
    c.name = s.course
GROUP BY
    c.name, c.teacher;

Ergebnis:

Jetzt haben wir eine praktische Tabelle, die Kurse, deren Lehrer und die Studenten als Arrays zeigt.

Daten filtern mit Arrays

Arrays sind schon für sich ein mächtiges Tool, aber die echte Magie beginnt, wenn du lernst, Daten darauf zu filtern. Du willst nur die User, die ein bestimmtes Wort in ihrer Interessenliste haben? Oder Bestellungen, bei denen jeder Preis einen bestimmten Schwellenwert übersteigt? Das alles geht direkt in SQL – ohne extra Logik in der App.

Beispiel 1: Zeilen nach Array-Elementen filtern

Angenommen, wir wollen alle Zeilen finden, bei denen das Array einen bestimmten Wert enthält, z.B. Studenten, die für den Kurs Mathematik eingeschrieben sind.

-- Wir filtern Studenten, die für Kurse eingeschrieben sind, mit `ANY`
SELECT *
FROM students
WHERE course = ANY(ARRAY['Mathematik', 'Physik']);

Hier erlaubt dir ANY, ein Array von Werten anzugeben, und die Abfrage gibt Zeilen zurück, bei denen course mindestens einem Wert im Array entspricht.

Beispiel 2: Überprüfung auf Schnittmenge von Arrays

Jetzt nehmen wir an, wir haben eine Tabelle student_interests, in der die Interessen der Studenten als Arrays gespeichert sind. Wir wollen Studenten finden, deren Interessen sich mit unseren Kriterien überschneiden.

-- Wir erstellen eine Tabelle mit Interessen der Studenten
CREATE TABLE student_interests (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    interests TEXT[]
);

-- Wir fügen Daten ein
INSERT INTO student_interests (name, interests) VALUES
('Alisa', ARRAY['programmierung', 'musik']),
('Bob', ARRAY['sport', 'programmierung']),
('Charlie', ARRAY['lesen', 'fotografie']),
('Emma', ARRAY['musik', 'sport']);

-- Wir suchen Studenten, die sich für programmierung oder musik interessieren
SELECT *
FROM student_interests
WHERE interests && ARRAY['programmierung', 'musik'];

Der Operator && prüft die Schnittmenge von zwei Arrays. Wenn mindestens ein Element aus dem linken Array im rechten Array vorkommt, kommt die Zeile durch den Filter.

Ergebnis:

Arrays sortieren

Manchmal ist die Reihenfolge der Werte im Array wichtig – besonders wenn du ein Array aus verschiedenen Zeilen baust oder die Daten fürs Anzeigen vorbereiten willst. PostgreSQL lässt dich die Elemente direkt in der Abfrage sortieren, ohne extra Nachbearbeitung.

Beispiel 1: Werte innerhalb eines Arrays sortieren

Manchmal willst du die Elemente im Array sortieren. Zum Beispiel sortieren wir das Interessen-Array der Studenten alphabetisch.

-- Wir sortieren die Array-Elemente mit der Funktion `array_sort()`
SELECT 
    name,
    array_sort(interests) AS sorted_interests
FROM 
    student_interests;

Ergebnis:

Beispiel 2: Zeilen nach Array-Länge sortieren

Und jetzt nehmen wir an, wir wollen die Studenten nach der Anzahl ihrer Interessen sortieren – von den größten Nerds bis zu den "langweiligsten".

-- Wir sortieren die Zeilen nach der Länge des Arrays
SELECT 
    name, 
    interests, 
    array_length(interests, 1) AS interests_count
FROM 
    student_interests
ORDER BY 
    interests_count DESC;

Ergebnis:

Auch wenn in diesem Beispiel alle Studenten gleich viele Interessen haben, kannst du so eine Abfrage easy für größere Tabellen anpassen.