Verschachtelte Daten extrahieren: jsonb_to_recordset()

Jetzt gehen wir einen Schritt weiter und schauen uns komplexere Szenarien mit JSONB-Daten an – nämlich wie man verschachtelte Daten extrahiert und sie in Tabellenzeilen umwandelt. Du fragst dich, warum das überhaupt nötig ist? Ganz einfach! Stell dir vor, du bekommst ein JSON-Objekt mit einer Liste von Einkäufen und sollst die Gesamtsumme berechnen oder alles tabellarisch für einen Report ausgeben. Genau das machen wir jetzt zusammen!

Warum kann man nicht einfach mit JSON als Text oder Struktur arbeiten? Lass uns ein Beispiel anschauen. In vielen echten Anwendungen werden Daten als JSON-Arrays gespeichert:

[
  { "id": 1, "product_name": "Laptop", "price": 1200 },
  { "id": 2, "product_name": "Smartphone", "price": 800 },
  { "id": 3, "product_name": "Tablet", "price": 400 }
]

Das ist zwar praktisch, aber bei der Analyse will man das Array oft in eine Tabelle umwandeln, um zu filtern, sortieren oder zu aggregieren. Stell dir vor: „Alle Bestellungen mit einem Wert über 500 Dollar“. Mit JSONB allein ist das nicht so komfortabel möglich. Genau hier kommt jsonb_to_recordset() ins Spiel.

Arbeiten mit jsonb_to_recordset()

Die Funktion jsonb_to_recordset() macht aus einem JSONB-Objekt-Array Tabellenzeilen. Sie wandelt jedes Element des Arrays in eine Zeile um und die Keys werden zu Spalten. Diese Funktion ist super praktisch, wenn deine Daten tief verschachtelt sind oder Arrays von Objekten enthalten.

Syntax

SELECT *
FROM jsonb_to_recordset('[ JSONB-Array ]') AS alias(spalte1 TYP, spalte2 TYP, ...);

• [ JSONB-Array ]: Das Array von JSON-Objekten, aus dem wir Daten holen.
• AS alias: Wir geben der Ergebnistabelle einen temporären Namen.
• spalte1 TYP, spalte2 TYP: Hier bestimmst du, wie die Spalten heißen und welche Datentypen sie haben (z.B. INTEGER, TEXT, NUMERIC).

Beispiel: JSONB-Array in Zeilen umwandeln

Angenommen, wir haben folgende Tabelle:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT,
    products JSONB
);

Und in der Tabelle liegen diese Daten:

Jetzt die Aufgabe: Gib alle Produkte aus allen Bestellungen als Tabelle aus. So geht's mit jsonb_to_recordset():

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC);

Ergebnis:

Beispiel: Daten filtern

Machen wir's etwas spannender. Wir wollen nur die Produkte ausgeben, die mehr als 100 Dollar kosten:

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
WHERE
    p.price > 100;

Ergebnis:

Beispiel: Daten aggregieren

Wie wäre es, die Gesamtsumme aller Produkte pro Bestellung zu berechnen? Einfach Aggregatfunktionen nutzen:

SELECT
    o.customer_name,
    SUM(p.price) AS total_amount
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
GROUP BY
    o.customer_name;

Ergebnis:

Wichtige Hinweise

Stell sicher, dass die Struktur des JSON-Arrays bei allen Objekten gleich ist. Wenn ein Objekt andere Keys oder verschachtelte Strukturen hat, kann es zu Fehlern oder unerwartetem Verhalten kommen.

Gib die Datentypen für die extrahierten Spalten korrekt an. Wenn ein Key z.B. ein Datum enthält, nimm DATE, für Zahlen NUMERIC oder INTEGER.

Denk dran: jsonb_to_recordset() funktioniert nur mit JSONB-Arrays; mit einzelnen Objekten klappt das nicht.

Typische Fehler und wie du sie vermeidest

Falsche Datentypen verwenden: Wenn im JSONB-Array Werte mit unterschiedlichen Typen stehen (z.B. String statt Zahl), gibt's einen Fehler. Am besten bringst du die Daten vorher ins richtige Format.

Falsche Keys ansprechen: Wenn ein Key in einem der Array-Objekte fehlt, gibt's einen Fehler. Check die Datenstruktur vorher!

Keine Daten vorhanden: Wenn die JSONB-Spalte leer ist (NULL), gibt die Funktion keine Ergebnisse zurück. In solchen Fällen nutze z.B. COALESCE() für Checks.

Praxiseinsatz

jsonb_to_recordset() wird in echten Projekten oft genutzt, z.B. für Bestellverarbeitung, Report-Analyse, User-Log-Tracking oder das Verarbeiten von externen APIs. Zum Beispiel:

• Im Online-Shop kannst du Produkt-Arrays easy in Tabellen umwandeln und Reports bauen.
• REST APIs liefern oft Daten als JSON, die du mit PostgreSQL super analysieren kannst.
• Analytics-Apps nutzen die Funktion oft, um komplexe, mehrstufige Daten zu verarbeiten.