JSON_TABLE ist die SQL-Standard-Funktion, die ein JSON-Dokument oder einen jsonb-Wert in eine relationale Tabelle umwandelt — mit definierten Spalten, Typen und Nested-Pfaden. PostgreSQL hat das in Version 17 endlich nachgezogen. Praktisch für Reports, ETL-Jobs und alles, was JSON in tabellarischer Form weiterverarbeiten will.

01 · Abschnitt

Wozu JSON_TABLE?

Ohne JSON_TABLE musst du verschachtelte JSON-Strukturen mit Operatoren oder jsonb_array_elements aufdröseln. Das geht, wird aber bei mehreren verschachtelten Arrays unübersichtlich.

JSON_TABLE definiert das Mapping deklarativ: „nimm den Pfad X und gib mir Spalten A (vom Pfad-Y, Typ Z), B (von Pfad-Y2, Typ Z2), …“.

02 · Abschnitt

Beispiel-Daten

SQL orders mit verschachteltem JSON 
CREATE TABLE orders (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    data jsonb
);

INSERT INTO orders (data) VALUES
    ('{
        "order_no": "O-1001",
        "customer": "Alice",
        "items": [
            {"sku": "A1", "qty": 2, "price": 50.00},
            {"sku": "B2", "qty": 1, "price": 59.95}
        ]
    }'),
    ('{
        "order_no": "O-1002",
        "customer": "Bob",
        "items": [
            {"sku": "C3", "qty": 1, "price": 19.95}
        ]
    }');

Ziel: eine flache Tabelle mit einer Zeile pro Item — also drei Zeilen aus zwei Bestellungen.

03 · Abschnitt

Erstes JSON_TABLE-Beispiel

SQL Items als flache Tabelle 
SELECT t.*
FROM orders o,
     JSON_TABLE(
         o.data,
         '$.items[*]'
         COLUMNS (
             order_no text PATH '$.order_no'         /* aus Wurzel */,
             sku      text PATH '$.sku',
             qty      int  PATH '$.qty',
             price    numeric(10,2) PATH '$.price'
         )
     ) AS t;

Output:

SQL 
 order_no | sku | qty | price
----------+-----+-----+--------
 O-1001   | A1  |   2 |  50.00
 O-1001   | B2  |   1 |  59.95
 O-1002   | C3  |   1 |  19.95

Drei Zeilen, eine pro Item. JSON_TABLE iteriert über $.items[*] und projiziert jedes Item in eine Zeile.

Achtung: der order_no-Pfad ist $.order_no — das $ referenziert hier die Wurzel, also den ganzen orders.data-Wert. Innerhalb des items[*] kennt JSON_TABLE den Pfad nach oben automatisch.

04 · Abschnitt

Spalten-Definitionen

Im COLUMNS-Block definierst du Name, Typ und Pfad pro Ergebnis-Spalte:

SQL 
COLUMNS (
    -- Skalar mit Pfad und Typ
    sku      text PATH '$.sku',

    -- Mit Default bei NULL
    qty      int  PATH '$.qty' DEFAULT 1 ON EMPTY,

    -- Mit Default bei Fehler (z. B. ungültiger Wert)
    price    numeric(10,2) PATH '$.price' DEFAULT 0 ON ERROR,

    -- Boolean-Konvertierung
    in_stock boolean PATH '$.in_stock',

    -- jsonb-Spalte (kein flatten)
    tags     jsonb PATH '$.tags',

    -- Existenz-Spalte (boolean — gibt's den Pfad?)
    has_discount boolean EXISTS PATH '$.discount'
)

DEFAULT … ON EMPTY springt ein, wenn der Pfad fehlt oder NULL liefert. DEFAULT … ON ERROR greift bei Konvertierungs-Fehlern (z. B. „abc” als int).

05 · Abschnitt

NESTED — verschachtelte Strukturen

Wenn du tiefer verschachteltes JSON hast, kannst du NESTED für jede Ebene nutzen:

SQL Items mit Tags 
-- Annahme: jedes Item hat ein "tags"-Array
INSERT INTO orders (data) VALUES ('{
    "order_no": "O-1003",
    "items": [
        {"sku": "X1", "qty": 1, "tags": ["urgent", "gift"]},
        {"sku": "Y2", "qty": 2, "tags": ["bulk"]}
    ]
}');

SELECT t.*
FROM orders o,
     JSON_TABLE(
         o.data,
         '$.items[*]'
         COLUMNS (
             sku  text PATH '$.sku',
             qty  int  PATH '$.qty',
             NESTED PATH '$.tags[*]' COLUMNS (
                 tag text PATH '$'
             )
         )
     ) AS t;

Output:

SQL 
 sku | qty |   tag
-----+-----+--------
 X1  |   1 | urgent
 X1  |   1 | gift
 Y2  |   2 | bulk

Pro Item × Tag-Element eine Zeile — die klassische „Cross Join über Arrays”-Operation, ohne Subqueries.

06 · Abschnitt

Vergleich zur klassischen Lösung

Vor JSON_TABLE musste man so etwas mit jsonb_array_elements bauen:

SQL Klassisches Pattern (immer noch erlaubt) 
SELECT
    o.data->>'order_no' AS order_no,
    item->>'sku'        AS sku,
    (item->>'qty')::int AS qty,
    (item->>'price')::numeric AS price
FROM orders o,
     jsonb_array_elements(o.data->'items') AS item;

Funktional identisch, aber:

  • Casts überall ((… ->> 'qty')::int) — leicht zu vergessen, leicht zu vertauschen.
  • Bei NESTED-Strukturen wird das schnell ein Multi-jsonb_array_elements-Salat.
  • Keine DEFAULT ON EMPTY-Logik out of the box.

JSON_TABLE ist deklarativer und lesbarer. Bei einfachen Cases ist’s Geschmackssache; bei komplexen lohnt es sich klar.

07 · Abschnitt

Use-Cases

Reporting aus jsonb-Logs

SQL Events analysieren 
-- Annahme: events.data hat z. B.
--   {"type": "purchase", "user_id": 42, "value": 99.95}
SELECT
    event_type,
    user_id,
    SUM(value) AS total_value
FROM events e,
     JSON_TABLE(
         e.data,
         '$'
         COLUMNS (
             event_type text PATH '$.type',
             user_id    int  PATH '$.user_id',
             value      numeric(10,2) PATH '$.value' DEFAULT 0 ON EMPTY
         )
     ) AS t
WHERE event_type = 'purchase'
GROUP BY event_type, user_id
ORDER BY total_value DESC;

View über jsonb erstellen

SQL Wrap als View 
CREATE VIEW orders_flat AS
SELECT
    o.id AS order_id,
    t.*
FROM orders o,
     JSON_TABLE(
         o.data,
         '$.items[*]'
         COLUMNS (
             order_no text PATH '$.order_no',
             sku      text PATH '$.sku',
             qty      int  PATH '$.qty',
             price    numeric(10,2) PATH '$.price'
         )
     ) AS t;

-- Jetzt wie eine normale Tabelle nutzbar:
SELECT order_no, sum(qty * price) AS revenue
FROM orders_flat
GROUP BY order_no;

Praktisch, wenn man häufig dieselbe Aufschlüsselung braucht — die View kapselt die JSON_TABLE-Definition ein.

08 · Abschnitt

Was JSON_TABLE NICHT kann

  • Daten zurückschreiben — JSON_TABLE ist read-only. Updates am ursprünglichen jsonb gehen über jsonb_set & Co.
  • Beliebig dynamische Schemas — die Spalten-Liste muss statisch im SQL stehen. Wer Spaltennamen aus den Daten ableiten will, braucht Dynamic-SQL und eine Stored Procedure.
  • Index-Beschleunigung der ganzen Operation — JSON_TABLE selbst ist eine Materialisierung; Indexe greifen auf die Quell-jsonb-Spalte (z. B. GIN), nicht auf das Ergebnis.
09 · Abschnitt

Interessantes

JSON_TABLE gibt's erst seit PG 17.

Auf älteren Versionen (16 und davor) musst du jsonb_array_elements mit LATERAL JOIN oder cross join lateral bauen — funktional ähnlich, aber syntaktisch holpriger. Vor dem Einsatz die Postgres-Version prüfen.

SQL-Standard-Funktion — auch in Oracle und MSSQL.

JSON_TABLE ist nicht Postgres-spezifisch, sondern Teil von SQL/JSON (ISO 9075). Oracle und SQL Server haben es seit Längerem; Postgres hat 2024 nachgezogen. Code damit ist relativ portabel zwischen DB-Systemen — anders als jsonb_array_elements (Postgres-only).

FORMAT JSON bei text-Quellen.

Wenn die Quell-Spalte text ist (nicht jsonb), brauchst du JSON_TABLE(col FORMAT JSON, …). Bei jsonb ist das automatisch erkannt — kein FORMAT nötig.

Defaults bei fehlenden Werten.

Ohne DEFAULT … ON EMPTY liefert ein fehlender Pfad NULL — meist das, was man will. Mit Default kann man Reports „löcherfrei” machen (z. B. qty DEFAULT 0 ON EMPTY). Bei ON ERROR denke an unerwartet fehlerhafte Daten — z. B. ein numerisches Feld, das mal Text enthält.

Performance: JSON_TABLE iteriert pro Zeile.

Wie auch jsonb_array_elements: Postgres muss pro Quell-Zeile das JSON parsen und expandieren. Bei sehr großen jsonb-Werten (über 1 MB) wird das spürbar. Bei kleineren Werten (typisch unter 50 KB) ist’s vergleichbar mit normalem SELECT.

Vor JSON_TABLE: jsonb_to_recordset oder jsonb_array_elements.

Der häufigste Vorgänger-Pattern war jsonb_to_recordset(o.data->'items') mit AS-Klausel oder jsonb_array_elements(o.data->'items') plus manuelle Casts. Beide funktionieren weiter — JSON_TABLE ist die saubere Standard-Lösung.

10 · Abschnitt

Weiterführende Ressourcen

Externe Quellen

/ Weiter

Zurück zu JSON & JSONB

Zur Übersicht