Postgres JSON – Einordnung

• PostgreSQL unterstützt JSON nativ seit Version 9.2 (json) und 9.4 (jsonb)
• Zwei Typen: json (Textspeicherung) und jsonb (Binärspeicherung, indizierbar)
• Kombination aus relationalem DBMS und dokumentenorientiertem Speicher
• Kein separates System nötig: JSON-Daten direkt in PostgreSQL-Tabellen
• Einsatz: API-Responses, Logs, Konfigurationen, semi-strukturierte Daten
• PostgreSQL 12+: SQL/JSON Path (JSONPath)
• PostgreSQL 16: SQL/JSON-Standard-Funktionen

JSON vs JSONB

Vergleich

• json: speichert Text exakt wie eingegeben, Reihenfolge der Keys erhalten
• jsonb: binäre Darstellung, Schlüssel sortiert, Duplikate entfernt
• jsonb: unterstützt GIN-Indizes → schnelle Abfragen
• jsonb: etwas langsamer beim Schreiben, deutlich schneller beim Lesen/Suchen
• Empfehlung: jsonb für fast alle Anwendungsfälle bevorzugen

Speichern und Eingeben

-- Tabelle mit JSONB-Spalte
CREATE TABLE events (
  id        SERIAL PRIMARY KEY,
  ts        TIMESTAMPTZ DEFAULT now(),
  payload   JSONB
);

-- Einfügen von JSON-Daten
INSERT INTO events (payload) VALUES
  ('{"type":"login","user":"alice","ip":"1.2.3.4"}'),
  ('{"type":"order","user":"bob",
     "items":[1,2,3],"total":49.90}');

-- Gültige JSON-Typen
SELECT '42'::jsonb;           -- Zahl
SELECT '"hello"'::jsonb;      -- String
SELECT 'true'::jsonb;         -- Boolean
SELECT '[1,2,3]'::jsonb;      -- Array
SELECT '{"a":1}'::jsonb;      -- Objekt

Gültige JSON-Typen

• number: Ganzzahlen und Dezimalzahlen
• string: UTF-8 Zeichenketten
• boolean: true oder false
• null: JSON-Null-Wert
• array: geordnete Liste beliebiger Typen
• object: Schlüssel-Wert-Paare

Hinweis

• jsonb normalisiert: Whitespace entfernt, Keys sortiert
• Cast mit ::jsonb

Zugriffs-Operatoren

Operatoren

• ->   gibt JSON-Element zurück (als json/jsonb)
• ->> gibt Textwert zurück (als text)
• #>   navigiert Pfad (Array von Strings)
• #>> navigiert Pfad, gibt Text zurück
• @>   enthält (containment)
• <@   wird enthalten in
• ?     Key existiert
• ?|    mindestens einer der Keys existiert
• ?&    alle Keys existieren

SELECT payload -> 'user'       -- "alice" (jsonb)
SELECT payload ->> 'user'      -- alice  (text)
SELECT payload #> '{items,0}'  -- 1 (jsonb)
SELECT payload #>> '{items,0}' -- 1 (text)
FROM events WHERE id = 2;

JSONPath – Pfadausdrücke

JSONPath-Konzepte

• JSONPath ist Standard SQL/JSON (PostgreSQL 12+)
• Ausdrücke starten mit $ (Root-Element)
• .key für Objektzugriff, [n] für Array-Zugriff
• [*] für alle Array-Elemente
• Filter-Ausdrücke: ?(@ > 10)
• Funktionen: .type(), .size(), .keyvalue()

-- jsonb_path_query: alle passenden Elemente
SELECT jsonb_path_query(payload, '$.items[*]')
FROM events WHERE id = 2;
-- -> 1, 2, 3

-- Filter: items groesser als 1
SELECT jsonb_path_query(
  payload, '$.items[*] ? (@ > 1)')
FROM events WHERE id = 2;
-- -> 2, 3

-- jsonb_path_exists: pruefen ob Pfad existiert
SELECT * FROM events
WHERE jsonb_path_exists(
  payload, '$.type ? (@ == "login")');

Filtern und Suchen

-- Gleichheit ueber ->>
SELECT * FROM events
WHERE payload->>'type' = 'order';

-- Containment @>: enthaelt diesen JSON-Wert
SELECT * FROM events
WHERE payload @> '{"type":"login"}';

-- Key-Existenz ?
SELECT * FROM events
WHERE payload ? 'items';

-- Numerischer Vergleich (cast noetig)
SELECT * FROM events
WHERE (payload->>'total')::numeric > 30;

-- Array-Element enthaelt
SELECT * FROM events
WHERE payload @> '{"items": [2]}';

Tipps für Performance

• @> und ? nutzen GIN-Index direkt
• ->> mit Cast erzwingt Sequenziellen Scan (ohne Funktionsindex)
• Für Cast-Vergleiche: Funktionsindex auf (payload->>'feld')::numeric
• JSONPath-Abfragen können ebenfalls GIN-Index nutzen
• @> ist oft die schnellste Methode für Containment-Checks

Beispiel Funktionsindex

CREATE INDEX ON events
  ((payload->>'type'));
-- Dann: WHERE payload->>'type' = 'order'
-- nutzt den Index!

GIN-Indizes

GIN-Index-Konzepte

• GIN (Generalized Inverted Index) indexiert alle Keys und Werte
• jsonb_ops (Standard): @>, ?, ?|, ?&
• Automatische Nutzung durch Query Planner
• Signifikante Beschleunigung bei @> und ?-Abfragen

-- Standard GIN-Index
CREATE INDEX idx_events_payload
  ON events USING GIN (payload);

-- Path-ops GIN-Index
-- (kompakter, schneller fuer @>)
CREATE INDEX idx_events_payload_path
  ON events USING GIN (payload jsonb_path_ops);

-- Partieller Index auf Subfeld
CREATE INDEX idx_events_type
  ON events ((payload->>'type'));

JSONB modifizieren

Modifikationsfunktionen

• jsonb_set(target, path, value): Wert setzen
• jsonb_insert(target, path, value): Element einfügen
• jsonb_strip_nulls(json): null-Werte entfernen
• ||-Operator: zwei jsonb-Objekte zusammenführen (shallow merge)
• --Operator: Key entfernen

-- Wert setzen (erstellt Key wenn nicht vorhanden)
UPDATE events
SET payload = jsonb_set(
  payload, '{status}', '"processed"')
WHERE id = 2;

-- Key entfernen
UPDATE events
SET payload = payload - 'ip'
WHERE payload->>'type' = 'login';

-- Objekte zusammenfuehren (shallow merge)
SELECT '{"a":1,"b":2}'::jsonb
     || '{"b":99,"c":3}'::jsonb;
-- -> {"a":1,"b":99,"c":3}

-- Nulls entfernen
SELECT jsonb_strip_nulls(
  '{"a":1,"b":null,"c":null}');
-- -> {"a":1}

JSON erzeugen

-- Objekt aus Ausdruecken bauen
SELECT json_build_object(
  'name', first_name || ' ' || last_name,
  'age',  extract(year from age(birthdate))
) FROM users;

-- Array aus Werten
SELECT json_build_array(1, 'two', true, null);
-- -> [1,"two",true,null]

-- Zeilen aggregieren zu JSON-Array
SELECT json_agg(row_to_json(u))
FROM users u WHERE active = true;

-- Key-Value-Aggregation zu Objekt
SELECT json_object_agg(name, value)
FROM config_table;

Aggregationsfunktionen

• json_build_object(k,v,...): Objekt aus Schlüssel-Wert-Paaren
• json_build_array(...): Array aus beliebigen Werten
• json_agg(): Zeilen zu JSON-Array aggregieren
• json_object_agg(k,v): Key-Value-Tabelle zu Objekt
• row_to_json(): Datenbankzeile als JSON-Objekt
• to_jsonb(): beliebigen Wert zu JSONB konvertieren

Anwendungsfälle

• REST-API-Responses direkt aus SQL generieren
• Report-Exports als JSON
• Daten für Frontend-Anwendungen aufbereiten

JSON-Funktionen: Entpacken

-- Objekt-Keys und Werte als Zeilen
SELECT key, value
FROM events,
     jsonb_each(payload)
WHERE id = 1;
-- key: type,  value: "login"
-- key: user,  value: "alice"
-- key: ip,    value: "1.2.3.4"

-- Array-Elemente als Zeilen
SELECT item
FROM events,
     jsonb_array_elements(
       payload->'items') AS item
WHERE id = 2;
-- item: 1, 2, 3

-- Schluessel als Text
SELECT jsonb_object_keys(payload)
FROM events WHERE id = 1;

json_populate_record – JSON zu Zeilen

Konzepte

• Wandelt JSON-Objekt in PostgreSQL-Zeilentyp um
• Nützlich für Import und Normalisierung von JSON-Daten
• json_to_record / json_to_recordset ohne vordefinierten Typ
• Fehlende Felder werden mit NULL gefüllt
• Typen werden automatisch konvertiert (z.B. String zu Integer)

-- Zieltyp definieren
CREATE TYPE order_rec AS (
  user_name text,
  total     numeric
);

-- JSON in Typ umwandeln
SELECT * FROM json_populate_record(
  null::order_rec,
  '{"user_name":"bob","total":49.90}'
);

-- Array von JSON-Objekten als Tabelle
SELECT * FROM json_to_recordset(
  '[{"id":1,"name":"Alice"},
    {"id":2,"name":"Bob"}]'
) AS t(id int, name text);

Anwendungsfall: API-Logs

CREATE TABLE api_logs (
  id        BIGSERIAL PRIMARY KEY,
  logged_at TIMESTAMPTZ DEFAULT now(),
  data      JSONB
);

-- Fehlerrate der letzten Stunde
SELECT
  data->>'endpoint'          AS endpoint,
  count(*) FILTER
    (WHERE (data->>'status')::int >= 500)
                             AS errors,
  count(*)                   AS total
FROM api_logs
WHERE logged_at >= now() - interval '1h'
GROUP BY endpoint
ORDER BY errors DESC;

-- Index fuer schnelle Endpoint-Suche
CREATE INDEX ON api_logs
  USING GIN (data jsonb_path_ops);

Vorteile

• Kein Schema-Change bei neuen Log-Feldern nötig
• Flexible Struktur: verschiedene Log-Typen in einer Tabelle
• Schnelle GIN-Suche nach beliebigen Feldern
• Aggregationen direkt in SQL möglich
• Partition by Time für große Log-Tabellen

Typische Log-Felder

• endpoint, method, status
• duration_ms, user_id
• request_id, trace_id

Anwendungsfall: Konfigurationsmanagement

CREATE TABLE app_config (
  app     TEXT PRIMARY KEY,
  config  JSONB NOT NULL
);

INSERT INTO app_config VALUES (
  'frontend',
  '{"theme":"dark","locale":"de",
    "features":{
      "beta":true,
      "newDashboard":false}}'
);

-- Feature-Flag abfragen
SELECT config #>> '{features,beta}'
FROM app_config WHERE app = 'frontend';

-- Config aktualisieren (ohne Ueberschreiben)
UPDATE app_config
SET config = config || '{"timeout": 30}'
WHERE app = 'frontend';

Vorteile

• Hierarchische Konfigurationen in einem Datensatz
• Atomares Update einzelner Felder mit jsonb_set
• Shallow merge mit ||: nur Top-Level-Keys überschreiben
• Feature-Flags einfach verwaltbar
• Versionierung per Zeitstempel-Spalte möglich

Hinweis Merge-Tiefe

• || ist nur shallow – verschachtelte Objekte werden überschrieben
• Für deep merge: jsonb_set mit explizitem Pfad nutzen

Anwendungsfall: Semi-strukturierte Produkte

Motivation

• Verschiedene Produktkategorien benötigen unterschiedliche Attribute
• Gemeinsame Pflichtfelder relational, Extras als JSONB
• Kein Schema-Change bei neuen Attributen
• Hybrides Modell: besser als rein dokumentenorientiert

CREATE TABLE products (
  id       SERIAL PRIMARY KEY,
  name     TEXT NOT NULL,
  price    NUMERIC(10,2),
  category TEXT,
  attrs    JSONB  -- flexible Attribute
);

INSERT INTO products VALUES
  (1,'Laptop',999.00,'electronics',
   '{"brand":"Dell","ram_gb":16,
     "screen_inch":15.6}'),
  (2,'T-Shirt',19.90,'clothing',
   '{"size":"M","color":"blue",
     "material":"cotton"}');

-- Suche nach RAM >= 8 GB
SELECT name, price
FROM products
WHERE (attrs->>'ram_gb')::int >= 8;

JSONPath: Erweiterte Abfragen

-- Alle Bestellwerte ueber 100
SELECT jsonb_path_query_array(
  '{"orders":[
     {"id":1,"val":120},
     {"id":2,"val":80}]}',
  '$.orders[*] ? (@.val > 100)'
);
-- -> [{"id":1,"val":120}]

-- Erste passende Zeile
SELECT jsonb_path_query_first(
  payload,
  '$.items[*] ? (@ > 2)'
) FROM events WHERE id = 2;
-- -> 3

-- Existenz-Check mit Bedingung
SELECT * FROM events
WHERE jsonb_path_exists(
  payload, '$.total ? (@ > 40)'
);

JSON-Validierung und Constraints

-- CHECK-Constraint fuer JSON-Struktur
ALTER TABLE events ADD CONSTRAINT chk_type
  CHECK (payload ? 'type');

ALTER TABLE events
  ADD CONSTRAINT chk_type_valid
  CHECK (payload->>'type'
         IN ('login','logout','order'));

-- NOT NULL fuer Pflichtfelder innerhalb JSON
ALTER TABLE events ADD CONSTRAINT chk_user
  CHECK (payload ? 'user' AND
         payload->>'user' IS NOT NULL);

-- Erweiterung pg_jsonschema (JSON Schema)
CREATE EXTENSION pg_jsonschema;
ALTER TABLE events
  ADD CONSTRAINT validate_payload
  CHECK (jsonb_matches_schema(
    '{"type":"object",
      "required":["type","user"]}',
    payload
  ));

Validierungsstrategien

• CHECK-Constraints: einfach, direkt in PostgreSQL
• Triggers: komplexe Validierungslogik möglich
• pg_jsonschema: vollständige JSON Schema Draft 7 Unterstützung
• CHECK auf Key-Existenz (?) ist performant
• Kombination: relationale NOT NULL + JSON CHECK

Hinweis

• CHECK-Constraints werden bei INSERT und UPDATE geprüft
• pg_jsonschema ist eine externe Extension
• JSON Schema ermöglicht Typ-, Format- und Musterprüfung

Performance-Vergleich

Kurzfazit

• Relational fast immer schneller für komplexe Abfragen und Joins
• JSONB flexibler, aber oft langsamer

Speicher / Struktur

Relational	JSONB
Feste Spalten, klarer Typ Sehr effizient gespeichert Optimal für strukturierte Daten	Flexibles Schema (Schema-less) Intern binär gespeichert (besser als JSON) Mehr Overhead pro Datensatz

Lese-Performance (SELECT)

Relational	JSONB
Direkter Zugriff auf Spalten Sehr schnelle Filter (WHERE, JOIN) Nutzt Standard-Indizes optimal (B-Tree)	Zugriff über Operatoren (->, ->>, @>) Langsamer, da Parsing/Traversal nötig Gute Performance nur mit passenden Indizes (GIN)

Indizierung

Relational	JSONB
Klassische Indizes (B-Tree, Hash) Sehr effizient und zielgenau	GIN/GiST-Indizes möglich Flexibel, aber mehr Overhead und weniger effizient

Schreib-Performance (INSERT / UPDATE)

Relational	JSONB
Updates betreffen nur einzelne Spalte	Oft muss das gesamte JSON-Dokument neu geschrieben werden Höherer IO-Aufwand

Joins / komplexe Queries

Relational	JSONB
Optimiert für Joins Query Planner extrem effizient	Joins schwierig / indirekt Oft zusätzliche Verarbeitung nötig Wesentlich langsamer als relational

Links in Postgres-Doku

JSON Types	link
JSON Functions and Operators	link
JSONPATH Type	link
SQL/JSON Path Language	link