Daten aus JSON-Objekten extrahieren

JSONB ist ein mächtiges Tool, mit dem du komplexe Datenstrukturen wie verschachtelte Objekte oder Arrays speichern kannst. Aber nur Daten in JSONB zu speichern reicht nicht – wir müssen sie auch rausziehen können. Stell dir vor, du hast eine Spalte data in der Tabelle users, wo alle User-Einstellungen als JSONB gespeichert sind. Willst du wissen, welches Theme ein User gewählt hat? Dann musst du das aus dem JSONB-Objekt extrahieren.

Wenn JSONB eine Schatztruhe ist, dann sind die Operatoren ->, ->>, #>> und Funktionen wie jsonb_extract_path() deine Schlüssel. Lass uns anschauen, wie man die benutzt.

Wichtige Operatoren für die Arbeit mit JSONB

PostgreSQL hat ein paar wichtige Operatoren für JSONB am Start. Damit kannst du Werte aus Keys, verschachtelten Objekten und Arrays rausziehen. Hier sind die wichtigsten:

Operator ->

Der Operator -> holt dir ein Objekt oder Array zu einem bestimmten Key. Wenn du den Wert im JSON-Format willst, ist das dein Operator.

Beispiel:

-- Beispiel-Daten
SELECT '{"name": "Alice", "age": 25}'::jsonb -> 'name';
-- Ergebnis: "Alice"

Operator ->>

Der Operator ->> ist ähnlich wie ->, aber er gibt den extrahierten Wert als Text zurück. Das ist praktisch, wenn du eine einfache Textdarstellung brauchst.

Beispiel:

-- Beispiel-Daten
SELECT '{"name": "Alice", "age": 25}'::jsonb ->> 'age';
-- Ergebnis: "25" (String)

Operator #>>

Der Operator #>> extrahiert Daten aus verschachtelten Objekten über einen angegebenen Pfad. Der Pfad wird als Array von Keys übergeben.

Beispiel:

-- Beispiel-Daten
SELECT '{"user": {"name": "Bob", "details": {"age": 30}}}'::jsonb #>> '{user, details, age}';
-- Ergebnis: "30" (String)

Unterschied zwischen -> und ->>:

Wenn dir der Datentyp wichtig ist (z.B. Array oder Objekt), nimm ->. Wenn du Text willst, dann ->>.

Funktionen für die Arbeit mit JSONB

Die Funktion jsonb_extract_path() extrahiert einen Wert aus einem JSONB-Objekt über einen angegebenen Pfad. Das ist quasi die funktionale Version vom Operator #>>, aber ein bisschen ausdrucksstärker.

Beispiel:

SELECT jsonb_extract_path('{"user": {"name": "Alice", "settings": {"theme": "dark"}}}'::jsonb, 'user', 'settings', 'theme');
-- Ergebnis: "dark"

Wenn du direkt einen Textwert willst, nimm jsonb_extract_path_text(). Die funktioniert wie jsonb_extract_path(), gibt aber einen String zurück.

Beispiel:

SELECT jsonb_extract_path_text('{"user": {"name": "Alice", "settings": {"theme": "dark"}}}'::jsonb, 'user', 'settings', 'theme');
-- Ergebnis: dark

Praktische Beispiele

Wert nach Key extrahieren. Angenommen, wir haben eine Tabelle products, wo in der Spalte details Daten als JSONB gespeichert sind:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    details JSONB
);

INSERT INTO products (name, details) VALUES
    ('Laptop', '{"brand": "Dell", "price": 1200, "specs": {"ram": "16GB", "cpu": "Intel i7"}}'),
    ('Phone', '{"brand": "Apple", "price": 1000, "specs": {"ram": "4GB", "cpu": "A13"}}');

Ergebnis:

Alle Brands der Produkte extrahieren.

SELECT name, details->'brand' AS brand FROM products;

Ergebnis:

Textwert extrahieren. Wenn wir den Brand ohne Anführungszeichen wollen, nehmen wir den Operator ->>:

SELECT name, details->>'brand' AS brand FROM products;

Ergebnis:

Verschachtelte Daten extrahieren. Wir holen uns den RAM (ram) für jedes Produkt:

SELECT name, details#>>'{specs, ram}' AS ram FROM products;

Ergebnis:

Daten per Pfad extrahieren. Das Gleiche geht auch mit der Funktion jsonb_extract_path_text():

SELECT name, jsonb_extract_path_text(details, 'specs', 'ram') AS ram FROM products;

Ergebnis:

Typische Fehler und wie du sie vermeidest

Fehler passieren oft, wenn du:

• Versuchst, Daten über einen falschen Pfad zu extrahieren. Wenn der Key nicht existiert, kommt null raus.
• Den falschen Operator für die Aufgabe benutzt. -> ist für Objekte und Arrays, aber für Text brauchst du ->>.

Beispiel für einen Fehler:

-- Fehler: Key 'nonexistent' gibt's nicht
SELECT details->>'nonexistent' FROM products;
-- Ergebnis: null

Tipp: Check immer die Datenstruktur, bevor du Queries schreibst, damit du Fehler vermeidest.

Einsatz in echten Projekten

Daten aus JSONB zu extrahieren wird in vielen echten Anwendungen genutzt:

• Im E-Commerce für Produkteigenschaften.
• In Web-Apps für User-Einstellungen.
• In Analytics für strukturierte Daten wie Events und Logs.

Hier noch ein Beispiel. Angenommen, wir haben eine Tabelle orders, wo Bestelldaten gespeichert sind:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT,
    items JSONB
);

INSERT INTO orders (customer_name, items) VALUES
    ('John', '[{"product": "Laptop", "quantity": 1}, {"product": "Mouse", "quantity": 2}]'),
    ('Alice', '[{"product": "Phone", "quantity": 1}]');

Wir holen uns die Namen aller Produkte aus den Bestellungen:

SELECT customer_name, jsonb_array_elements(items)->>'product' AS product FROM orders;

Ergebnis:

Als Nächstes tauchen wir noch tiefer in JSONB ein, schauen uns verschachtelte Daten an und wie man sie für Analysen noch besser aufbereitet. Mach dich bereit für noch mehr spannende Sachen!