Beispiele für komplexe Abfragen mit JSONB

In den vorherigen Vorlesungen haben wir die Grundlagen von JSONB gelernt: wie man Daten erstellt, ändert und abruft. Jetzt wird’s ernst – Zeit für komplexe Abfragen, die zeigen, wie mächtig dieser Datentyp wirklich ist.

Stell dir einen Online-Shop mit einem Produktkatalog vor. Jedes Produkt hat Basisinfos (Name, ID), aber die Eigenschaften können total unterschiedlich sein: Ein Laptop hat RAM und CPU, Kleidung hat Größen und Materialien, Bücher haben Autoren und Genres. Das alles in separaten Tabellen speichern? Unpraktisch. In JSONB? Perfekt! Aber wie findest du alle Produkte einer bestimmten Marke, sortierst sie nach Preis oder zählst die Statistik pro Kategorie? Wie arbeitest du mit Daten, die nicht in normalen Spalten, sondern tief in einer JSON-Struktur stecken?

Heute schauen wir uns echte Szenarien an: von einfacher Filterung bis zu komplexen Abfragen mit Gruppierung und Aggregation. Du wirst sehen, wie JSONB PostgreSQL zu einem flexiblen Tool für alle möglichen Daten macht.

Daten filtern in JSONB

Daten filtern ist wie ein Teesieb: Du behältst nur das, was du brauchst, und der Rest fliegt raus. Mit JSONB wird’s noch spannender, weil du nicht nur nach normalen Spalten filtern kannst, sondern auch nach Daten, die tief in der JSON-Struktur versteckt sind.

Operatoren zum Filtern von JSONB:

• @> — "JSONB-enthält". Prüft, ob das JSONB-Objekt das angegebene Subset enthält.
• ? — "Key vorhanden". Prüft, ob der angegebene Key im JSONB-Objekt existiert.
• ?| — "Einer der Keys vorhanden". Prüft, ob mindestens einer der angegebenen Keys existiert.
• ?& — "Alle Keys vorhanden". Prüft, ob alle angegebenen Keys existieren.

Beispiel: Filtern nach Key und Wert. Angenommen, wir haben eine Tabelle products mit einer Spalte details, die JSONB-Infos zu den Produkten speichert:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    details JSONB
);

Beispieldaten:

INSERT INTO products (name, details) VALUES
('Laptop', '{"brand": "Dell", "price": 1200, "specs": {"ram": "16GB", "cpu": "i7"}}'),
('Smartphone', '{"brand": "Apple", "price": 800, "specs": {"ram": "4GB", "cpu": "A15"}}'),
('Tablet', '{"brand": "Samsung", "price": 500, "specs": {"ram": "8GB", "cpu": "Exynos"}}');

Ergebnis:

Um alle Produkte mit brand "Apple" zu finden:

SELECT *
FROM products 
WHERE details @> '{"brand": "Apple"}';

Ergebnis:

Wenn du alle Produkte finden willst, die den Key specs haben, nimm den Operator ?:

SELECT *
FROM products 
WHERE details ? 'specs';

Ergebnis:

Alle Zeilen enthalten das Feld details und den Key specs.

Daten sortieren in JSONB

Manchmal willst du nicht nach normalen Spalten sortieren, sondern nach Werten, die in JSONB stecken. Dafür kannst du die Operatoren ->> (holt den Wert als Text) und CAST nutzen, um den Text in den richtigen Typ zu verwandeln.

Beispiel: Produkte nach Preis sortieren:

SELECT *
FROM products 
ORDER BY (details->>'price')::INTEGER;

Ergebnis:

Daten gruppieren in JSONB

Gruppieren hilft dir, Daten zu aggregieren und Statistiken zu bekommen. Zum Beispiel kannst du herausfinden, wie viele Produkte es pro Marke gibt.

Beispiel: Anzahl der Produkte pro Marke zählen:

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
GROUP BY details->>'brand';

Ergebnis:

Praktische Beispiele

Filtern und Gruppieren. Zählen wir, wie viele Produkte teurer als 600 pro Marke sind:

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
WHERE (details->>'price')::INTEGER > 600
GROUP BY details->>'brand';

Ergebnis:

Sortieren nach Gruppierung. Jetzt sortieren wir die Marken nach der Anzahl der Produkte:

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
GROUP BY details->>'brand'
ORDER BY product_count DESC;

Komplexe Abfrage: Filtern, Sortieren, Gruppieren

Stell dir vor, du willst Marken finden, die Produkte teurer als 600 haben, und für jede Marke das günstigste Produkt auswählen. So geht’s:

WITH filtered_products AS (
    SELECT *
    FROM products
    WHERE (details->>'price')::INTEGER > 600
)
SELECT
    details->>'brand' AS brand,
    MIN((details->>'price')::INTEGER) AS min_price
FROM filtered_products
GROUP BY details->>'brand'
ORDER BY min_price;

Ergebnis:

Typische Fehler und Tipps

Fehler: Falsche Operatoren benutzt. Verwechsele nicht -> und ->>: Der erste gibt ein Objekt zurück, der zweite einen Textwert.

Fehler: Performance-Probleme. Wenn du oft komplexe Abfragen machst, leg einen GIN-Index auf die JSONB-Spalte an.

Fehler: Typ-Probleme. Werte aus JSONB sind Strings, also vergiss nicht, CAST zu benutzen.

Beispiel für das Anlegen eines Indexes:

CREATE INDEX idx_products_details ON products USING GIN (details);

Jetzt läuft die Filterung wie details @> '{"brand": "Apple"}' viel schneller.