PostgreSQL - JSONB Keys als Spalten
Beispieldaten
CREATE TABLE geraete (id int, daten jsonb);
INSERT INTO geraete VALUES
(1, '{"name": "sw01", "ports": 48, "standort": "RZ1"}'),
(2, '{"name": "sw02", "ports": 24}');| id | daten |
|---|---|
| 1 | {"name": "sw01", "ports": 48, "standort": "RZ1"} |
| 2 | {"name": "sw02", "ports": 24} |
Einzelne Keys
SELECT id,
daten->>'name' AS name,
(daten->>'ports')::int AS ports
FROM geraete;| id | name | ports |
|---|---|---|
| 1 | sw01 | 48 |
| 2 | sw02 | 24 |
| Operator | Ergebnis | Beispiel |
|---|---|---|
-> | Wert als jsonb | daten->'ports' |
->> | Wert als text | daten->>'ports' |
#>> | verschachtelter Pfad als text | daten#>>'{netz,vlan}' |
Viele Keys
SELECT g.id, j.*
FROM geraete g
CROSS JOIN LATERAL jsonb_to_record(g.daten) AS j(name text, ports int, standort text);| id | name | ports | standort |
|---|---|---|---|
| 1 | sw01 | 48 | RZ1 |
| 2 | sw02 | 24 | NULL |
- Spaltenname = Key (case-sensitive, Sonderzeichen quoten:
"Serial-Nr" text) - fehlender Key →
NULL - Keys ohne Spaltendefinition werden ignoriert
- nicht castbarer Wert → Fehler, der ganze Query bricht ab
Unbekannte Keys
Spalten müssen beim Schreiben des Queries feststehen, “alle Keys automatisch als Spalten” geht in reinem SQL nicht.
Vorhandene Keys ermitteln
SELECT key, count(*)
FROM geraete, jsonb_object_keys(daten) AS key
GROUP BY key
ORDER BY key;| key | count |
|---|---|
| name | 2 |
| ports | 2 |
| standort | 1 |
Key/Value als Zeilen
SELECT g.id, kv.key, kv.value
FROM geraete g
CROSS JOIN LATERAL jsonb_each_text(g.daten) AS kv;| id | key | value |
|---|---|---|
| 1 | name | sw01 |
| 1 | ports | 48 |
| 1 | standort | RZ1 |
| 2 | name | sw02 |
| 2 | ports | 24 |
Filtern
| Beschreibung | Command |
|---|---|
| Key vorhanden | WHERE daten ? 'standort' |
| Key hat Wert | WHERE daten @> '{"standort": "RZ1"}' |
| Vergleich als Text | WHERE daten->>'name' = 'sw01' |
Index für ? und @> | CREATE INDEX ON geraete USING gin (daten); |
Zuletzt aktualisiert am