Menu

PostgreSQL JSONB: Operatoren, Abfragen und Indizes

JSON in PostgreSQL mit jsonb speichern und abfragen: die Operatoren -> und ->>, Containment mit @>, jsonb_set und andere Updates, Arrays in Zeilen zerlegen, GIN-Indizes und json vs jsonb.

Diese Seite enthält ausführbare Editoren. Bearbeiten, ausführen und Ausgabe sofort sehen.

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

OperatorLiefertBeispiel
-> Schlüssel oder Indexjsonbdata->'customer', data->'tags'->0
->> Schlüssel oder Indextextdata->>'status'
#> Pfadjsonbdata#>'{customer,city}'
#>> Pfadtextdata#>>'{customer,city}'
[...] Subscript (PostgreSQL 14+)jsonbdata['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_set liefert NULL, wenn der neue Wert SQL NULL ist, und das UPDATE löscht dann die ganze Spalte. Übergib COALESCE(to_jsonb(x), 'null') oder nimm jsonb_set_lax (PostgreSQL 13+), das standardmäßig ein JSON null speichert.
  • jsonb_set ergä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

jsonjsonb
Gespeichert alsDer EingabetextEine geparste Binärform
Leerraum, Schlüsselreihenfolge, doppelte SchlüsselBleiben erhaltenEntfernt; der letzte doppelte gewinnt
SchreibenEtwas schneller (nur validiert)Beim Einfügen geparst
Lesen und AbfragenBei jedem Zugriff neu geparstSchnell
@>, ? und GIN-IndizesNeinJa

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.

Illustration der Programmiersprachen bei Coddy

Lerne mit Coddy zu programmieren

Los geht's