PostgreSQL Abfragen & Filtern von JSON-Feldern

Mit den PostgreSQL JSON-Funktionen kannst du JSON-Daten in deinen Tabellen speichern, abfragen und manipulieren. Sie sind unerlässlich für Anwendungen, die ein flexibles Schema benötigen oder JSON-Daten direkt in der Datenbank verarbeiten müssen.

JSON und JSONB verstehen

JSON" und "JSONB" sind Datentypen in PostgreSQL, die zum Speichern von JSON-Daten verwendet werden. JSON" speichert Daten in einem Textformat, während JSONB" Daten in einem zerlegten Binärformat speichert, was eine effiziente Indizierung und Abfrage ermöglicht.

Verwendung

JSON-Funktionen werden in PostgreSQL verwendet, um auf JSON-Daten zuzugreifen und sie zu filtern, die in Spalten vom Typ `JSON` oder `JSONB` gespeichert sind. Sie ermöglichen komplexe Datenabfragen und -manipulationen, indem sie Methoden zum Extrahieren von Elementen, Filtern von Arrays und Ändern von JSON-Objekten bereitstellen.

SELECT json_column->'key'
FROM table_name
WHERE json_column->>'key' = 'value';

In dieser Syntax wird `->` verwendet, um ein JSON-Objekt zu extrahieren, während `->>` JSON-Text extrahiert.

Beispiele

1. Extrahieren eines JSON-Feldes

SELECT data->'name' AS name
FROM users;

Diese Abfrage extrahiert das Feld "Name" aus der JSON-Spalte "Daten" in der Tabelle "Benutzer".

2. Filtern mit JSONB

SELECT *
FROM orders
WHERE order_data->>'status' = 'shipped';

Hier filtert die Abfrage Zeilen in der Tabelle "Bestellungen", bei denen der Schlüssel "Status" in der JSONB-Spalte "Order_data" auf "Shipped" gesetzt ist.

3. JSONB-Array-Filterung

SELECT id, info
FROM products
WHERE info->'tags' @> '["electronics"]';

Diese Abfrage wählt `id` und `info` aus der Tabelle `Produkte` aus, wobei das Array `tags` in der Spalte `info` JSONB den Wert "electronics" enthält.

4. JSON-Daten aktualisieren

UPDATE users
SET data = jsonb_set(data, '{address, city}', '"New City"')
WHERE id = 1;

Diese Abfrage aktualisiert das Feld "Stadt" im Objekt "Adresse" in der JSONB-Spalte "Daten" für den Benutzer mit der "ID" 1.

Tipps und bewährte Praktiken

• Verwende JSONB für die Indizierung. Ziehe `JSONB` gegenüber `JSON` vor, um eine bessere Leistung beim Indizieren und Suchen zu erzielen.
• Nutze die GIN-Indizes. Erstelle GIN-Indizes auf JSONB-Spalten, um Abfragen zur Eingrenzung zu beschleunigen.
• Sorgfältig extrahieren. Verwende `->` für JSON-Objekte und `->>` für Textextraktion, um Fehler beim Typ-Casting zu vermeiden.
• Halte die Abfragen spezifisch. Strukturiere deine Abfragen, um die Menge der verarbeiteten JSON-Daten zu minimieren und die Leistung zu verbessern.
• Betrachte die Normalisierung. Für häufig abgefragte JSON-Felder solltest du eine Normalisierung der Daten in separate Spalten oder Tabellen in Betracht ziehen, um die Effizienz zu erhöhen.
• Verstehe die Leistungsunterschiede. JSONB" ist aufgrund seines binären Formats in der Regel schneller bei Lesevorgängen, kann aber mehr Speicherplatz benötigen.
• Achte auf die Auswirkungen auf den Speicherplatz. JSONB" kann aufgrund seiner binären Darstellung einen erhöhten Speicherbedarf haben, also wähle den Datentyp je nach Anwendungsfall.