jsonb ist der binäre JSON-Spaltentyp von PostgreSQL. Felder liest du mit -> (liefert jsonb) und ->> (liefert text), Zeilen findest du mit dem Containment-Operator @>, und Felder änderst du mit jsonb_set oder ||. Ein GIN-Index macht diese Abfragen schnell.
Die Bestellungen 1 und 3 passen. data->'customer' liefert das verschachtelte Objekt als jsonb, ->>'name' holt den Namen als Text heraus, und ->> plus ein Cast macht aus dem Betrag eine Zahl.
Werte aus JSONB lesen
| Operator | Liefert | Beispiel |
|---|---|---|
-> Schlüssel oder Index | jsonb | data->'customer', data->'tags'->0 |
->> Schlüssel oder Index | text | data->>'status' |
#> Pfad | jsonb | data#>'{customer,city}' |
#>> Pfad | text | data#>>'{customer,city}' |
[...] Subscript (PostgreSQL 14+) | jsonb | data['customer']['city'] |
Array-Indizes beginnen bei 0, und ein negativer Index zählt vom Ende (-> -1 ist das letzte Element). Ein fehlender Schlüssel ergibt SQL NULL, keinen Fehler. Bestellung 3 oben hat deshalb eine NULL Stadt.
Nimm ->> zum Vergleich mit Text oder für einen Cast: data->>'status' = 'paid'. Nimm ->, um weiter zu navigieren. Ein häufiger Fehler ist data->'status' = 'paid'. PostgreSQL liest 'paid' als JSON, ein Wort ohne Anführungszeichen ist kein gültiges JSON, und die Abfrage scheitert mit "invalid input syntax for type json".
Zeilen nach JSON-Inhalt finden
@> prüft, ob der linke Wert den rechten enthält. Das funktioniert in jeder Tiefe und innerhalb von Arrays, und ein GIN-Index unterstützt es:
Die Produkte 1 und 2 haben Größe M und einen Preis; nur 2 kostet mehr als 30. Die Suchoperatoren:
@>enthält,<@ist enthalten in.?der Schlüssel (oder String im Array) existiert,?|einer der Schlüssel existiert,?&alle existieren.@?und@@führen eine JSON-Path-Abfrage aus (PostgreSQL 12+), für Vergleiche innerhalb des Dokuments.
Felder in JSONB ändern
PostgreSQL schreibt bei jedem Update den ganzen Wert neu, aber mit diesen Funktionen beschreibst du nur die Änderung:
jsonb_set(target, path, new_value)ersetzt einen Wert an einem Pfad.-entfernt einen Schlüssel, und#-entfernt einen verschachtelten Pfad wie'{notify,sms}'.||fügt Schlüssel auf oberster Ebene hinzu oder überschreibt sie.
Seit PostgreSQL 14 kannst du auch über ein Subscript zuweisen: SET prefs['notify']['email'] = 'false'.
Zwei Fallen:
jsonb_setliefertNULL, wenn der neue Wert SQLNULList, und dasUPDATElöscht dann die ganze Spalte. ÜbergibCOALESCE(to_jsonb(x), 'null')oder nimmjsonb_set_lax(PostgreSQL 13+), das standardmäßig ein JSONnullspeichert.jsonb_setergänzt einen fehlenden letzten Schlüssel, aber keine fehlenden Elternobjekte:'{a,b}'auf{}zu setzen, lässt es unverändert. Die Zuweisung per Subscript legt sie dagegen an.
JSON-Arrays in Zeilen zerlegen
jsonb_array_elements liefert eine Zeile pro Array-Element, und jsonb_to_recordset macht aus einem Array von Objekten typisierte Spalten. Seit PostgreSQL 17 erledigt das Standard-JSON_TABLE beides an einer Stelle:
A-1 wurde 3-mal verkauft und B-2 einmal. In die andere Richtung baut jsonb_build_object('id', id, 'name', name) ein Objekt aus Spalten, und jsonb_agg sammelt Zeilen in einem Array (siehe string_agg und array_agg).
JSONB mit GIN indizieren
Ein GIN-Index auf der ganzen Spalte bedient @>, ?, ?|, ?&, @? und @@:
CREATE INDEX orders_data_idx ON orders USING gin (data);
-- smaller and faster, but only for @>, @? and @@
CREATE INDEX orders_data_path_idx ON orders USING gin (data jsonb_path_ops);
-- one field compared with = or a range: a B-tree on the expression
CREATE INDEX orders_status_idx ON orders ((data->>'status'));
Schreib die Abfrage in der Form, die der Index unterstützt. WHERE data @> '{"status": "paid"}' kann den GIN-Index nutzen. WHERE data->>'status' = 'paid' kann es nicht; dafür braucht es den Ausdrucks-Index. Ein Bereichstest wie (data->>'total')::numeric > 50 braucht einen Ausdrucks-Index auf genau diesem Ausdruck.
JSON vs JSONB
json | jsonb | |
|---|---|---|
| Gespeichert als | Der Eingabetext | Eine geparste Binärform |
| Leerraum, Schlüsselreihenfolge, doppelte Schlüssel | Bleiben erhalten | Entfernt; der letzte doppelte gewinnt |
| Schreiben | Etwas schneller (nur validiert) | Beim Einfügen geparst |
| Lesen und Abfragen | Bei jedem Zugriff neu geparst | Schnell |
@>, ? und GIN-Indizes | Nein | Ja |
json liefert den Text unverändert; jsonb behält nur "a": 3 und ordnet die Schlüssel neu. Nimm jsonb, außer du musst den Originaltext Byte für Byte zurückgeben, etwa eine gespeicherte Webhook-Payload. Felder, die jede Zeile hat oder die einen Foreign Key brauchen, gehören in normale Spalten. Die offizielle Referenz der JSON-Funktionen listet jeden Operator und jede Funktion auf.
Häufig gestellte Fragen
Was ist der Unterschied zwischen -> und ->> in PostgreSQL?
-> liefert den Wert als jsonb, du kannst also weiter navigieren (data->'customer'->'city'). ->> liefert ihn als text, und das brauchst du zum Vergleich mit einem String oder für einen Cast in eine Zahl: (data->>'total')::numeric.
Wie prüfe ich, ob ein JSONB-Array einen Wert enthält?
Mit Containment: data->'tags' @> '["gift"]', oder data @> '{"tags": ["gift"]}', um ab der obersten Ebene des Dokuments zu prüfen. Für ein Array aus Strings funktioniert auch data->'tags' ? 'gift'. Ein GIN-Index auf data beschleunigt die zweite Form.
Behält JSONB die Reihenfolge der Schlüssel?
Nein. jsonb speichert Schlüssel in einer eigenen Reihenfolge, entfernt Leerraum und behält bei doppelten Schlüsseln nur den letzten. Wenn du den Originaltext exakt zurück brauchst, nimm den Typ json.
Wie prüfe ich, ob ein Schlüssel in JSONB existiert?
data ? 'key' ist wahr, wenn das Objekt auf oberster Ebene diesen Schlüssel hat, selbst wenn sein Wert JSON null ist. ?| nimmt ein Array und ist wahr, wenn einer der Schlüssel existiert, ?&, wenn alle existieren.
Kann ich einem Feld in JSONB einen Foreign Key oder NOT NULL geben?
Nicht direkt. Ein CHECK Constraint kann ein Feld prüfen (CHECK (data ? 'email')), aber Foreign Keys brauchen eine echte Spalte. Felder, die jede Zeile hat und auf die andere Tabellen verweisen, gehören in normale Spalten.