PostgreSQL JSON/JSONB Fortgeschrittene Operationen

Einführung

In diesem Lab werden Sie fortgeschrittene Operationen mit dem JSONB-Datentyp von PostgreSQL untersuchen. Sie lernen, wie Sie JSON-Daten effektiv in einer relationalen Datenbank speichern, verwalten und abfragen.

Das Lab führt Sie durch das Erstellen einer Tabelle mit einer JSONB-Spalte, das Einfügen von Daten und das anschließende Anwenden eines GIN-Indexes zur Verbesserung der Suchleistung. Sie werden auch das Abfragen verschachtelter JSON-Strukturen, das Aktualisieren spezifischer Elemente innerhalb eines JSONB-Dokuments und das Durchführen von Aggregatberechnungen für Ihre JSON-Daten üben.

Tabelle erstellen und JSONB-Felder indizieren

In diesem Schritt erstellen Sie eine Tabelle zur Speicherung von Produktinformationen unter Verwendung des JSONB-Datentyps und erstellen dann einen GIN-Index zur Optimierung von Abfragen auf diesen Daten. JSONB speichert JSON-Daten in einem zerlegten Binärformat, dessen Eingabe etwas langsamer ist, dessen Verarbeitung jedoch deutlich schneller erfolgt. Ein GIN (Generalized Inverted Index) ist ideal für die Indizierung von zusammengesetzten Werten wie denen in einer JSONB-Spalte.

Öffnen Sie zunächst ein Terminal und verbinden Sie sich mit der PostgreSQL-Datenbank über die interaktive psql-Shell:

sudo -u postgres psql

Sie sehen nun die PostgreSQL-Eingabeaufforderung, die wie postgres=# aussieht.

Erstellen Sie als Nächstes eine Tabelle namens products mit einer id-Spalte und einer data-Spalte vom Typ JSONB.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    data JSONB
);

Fügen Sie nun einige Beispiel-Produktdaten in die Tabelle products ein.

INSERT INTO products (data) VALUES
('{"name": "Laptop", "price": 1200, "tags": ["electronics", "computer"]}'),
('{"name": "Keyboard", "price": 75, "tags": ["electronics", "accessory"]}'),
('{"name": "Mouse", "price": 30, "tags": ["electronics", "accessory"]}'),
('{"name": "Monitor", "price": 300, "tags": ["electronics", "display"]}');

Um die Suche in der Spalte data erheblich zu beschleunigen, erstellen Sie einen GIN-Index darauf.

CREATE INDEX idx_products_data ON products USING GIN (data);

Sie können überprüfen, ob der Index erfolgreich erstellt wurde, indem Sie den Befehl \di verwenden, der alle Indizes auflistet.

\di

Sie sollten idx_products_data in der Liste der Relationen sehen, ähnlich der folgenden Ausgabe:

                                List of relations
 Schema |        Name         | Type  |  Owner   |   Table   |    Size    | Description
--------+---------------------+-------+----------+-----------+------------+-------------
 public | idx_products_data   | index | postgres | products  | 16 kB      |
 public | products_pkey       | index | postgres | products  | 16 kB      |
(2 rows)

Sie haben Ihre Tabelle erfolgreich eingerichtet und die JSONB-Spalte indiziert. Sie bleiben für den nächsten Schritt in der psql-Shell.

Verschachtelte JSON-Strukturen abfragen

In diesem Schritt lernen Sie, wie Sie Daten innerhalb von JSONB-Spalten abfragen, einschließlich verschachtelter Strukturen. PostgreSQL bietet hierfür mehrere Operatoren.

Fügen wir zunächst einige Produkte mit komplexeren, verschachtelten Daten in die Tabelle products ein.

INSERT INTO products (data) VALUES
('{"name": "Gaming PC", "price": 1500, "specs": {"cpu": "Intel i7", "ram": "32GB", "storage": "1TB SSD"}}'),
('{"name": "Office PC", "price": 800, "specs": {"cpu": "Intel i5", "ram": "16GB", "storage": "500GB SSD"}}');

Um auf einen Schlüssel auf oberster Ebene zuzugreifen, können Sie den Operator ->> verwenden, der den Wert als Text zurückgibt. Ermitteln wir den Preis des "Laptop".

SELECT data ->> 'price' FROM products WHERE data ->> 'name' = 'Laptop';

Die Ausgabe wird sein:

 price
-------
 1200
(1 row)

Um auf einen Schlüssel innerhalb eines verschachtelten JSON-Objekts zuzugreifen, können Sie die Operatoren -> und ->> verketten. Der Operator -> ruft ein JSON-Objektfeld ab, während ->> es als Text abruft. Holen wir die CPU-Spezifikation für den "Gaming PC".

SELECT data -> 'specs' ->> 'cpu' FROM products WHERE data ->> 'name' = 'Gaming PC';

Dieser Befehl gibt den CPU-Typ zurück:

   ?column?
--------------
 Intel i7
(1 row)

Sie können diese Operatoren auch in der WHERE-Klausel verwenden, um Ergebnisse basierend auf verschachtelten Werten zu filtern. Finden Sie beispielsweise alle Produkte, die eine "Intel i5"-CPU haben.

SELECT data ->> 'name' FROM products WHERE data -> 'specs' ->> 'cpu' = 'Intel i5';

Die Abfrage gibt den Namen des übereinstimmenden Produkts zurück:

   name
-----------
 Office PC
(1 row)

Sie können nun sowohl auf Daten auf oberster Ebene als auch auf verschachtelte Daten innerhalb Ihrer JSONB-Spalten zugreifen.

Spezifische JSONB-Elemente aktualisieren

In diesem Schritt lernen Sie, wie Sie spezifische Elemente innerhalb einer JSONB-Spalte mithilfe der Funktion jsonb_set ändern. Dies ist effizienter, als das gesamte JSON-Objekt abzurufen, es in Ihrer Anwendung zu ändern und es dann zurückzuschreiben.

Die Funktion jsonb_set hat die folgende Syntax: jsonb_set(target_jsonb, path_array, new_value_jsonb, create_if_missing).

Aktualisieren wir den Preis des "Laptop" von 1200 auf 1250. Der Pfad zum Preis ist '{price}', und der neue Wert muss nach JSONB gecastet werden.

UPDATE products
SET data = jsonb_set(data, '{price}', '1250'::jsonb)
WHERE data ->> 'name' = 'Laptop';

Überprüfen Sie die Aktualisierung, indem Sie die Daten des Laptops auswählen.

SELECT data FROM products WHERE data ->> 'name' = 'Laptop';

Die Ausgabe sollte den neuen Preis anzeigen:

                                   data
--------------------------------------------------------------------------
 {"name": "Laptop", "price": 1250, "tags": ["electronics", "computer"]}
(1 row)

Sie können auch verschachtelte Werte aktualisieren. Rüsten wir den RAM des "Office PC" auf "32GB" auf. Der Pfad zum verschachtelten ram-Schlüssel ist '{specs,ram}'.

UPDATE products
SET data = jsonb_set(data, '{specs,ram}', '"32GB"'::jsonb)
WHERE data ->> 'name' = 'Office PC';

Überprüfen Sie nun diese verschachtelte Aktualisierung.

SELECT data -> 'specs' FROM products WHERE data ->> 'name' = 'Office PC';

Das Ergebnis bestätigt die Änderung des ram-Wertes:

                          ?column?
------------------------------------------------------------
 {"cpu": "Intel i5", "ram": "32GB", "storage": "500GB SSD"}
(1 row)

Sie haben nun gelernt, wie Sie gezielte Aktualisierungen an JSONB-Daten durchführen.

JSON-Daten aggregieren

In diesem letzten Schritt führen Sie aggregierte Berechnungen auf Daten durch, die aus JSONB-Spalten extrahiert wurden. Dies ist nützlich für Berichte und Analysen.

Um Berechnungen durchzuführen, müssen Sie oft einen Wert extrahieren und ihn in einen numerischen Typ umwandeln (casten). Berechnen wir den Durchschnittspreis aller Produkte.

SELECT AVG((data ->> 'price')::numeric) FROM products;

Die Abfrage extrahiert den price als Text, wandelt ihn in numeric um und berechnet dann den Durchschnitt. Die Ausgabe ist eine einzelne Zahl:

          avg
------------------------
 659.1666666666666667
(1 row)

Sie können Aggregatfunktionen auch mit einer GROUP BY-Klausel verwenden. Ermitteln wir die Gesamtzahl der Produkte für jedes Tag. Dazu müssen wir zuerst das tags-Array mit jsonb_array_elements_text in separate Zeilen aufteilen (unnest).

SELECT tag, COUNT(*)
FROM products, jsonb_array_elements_text(data -> 'tags') AS tag
GROUP BY tag;

Diese Abfrage liefert eine Zählung für jedes eindeutige Tag:

     tag     | count
-------------+-------
 accessory   |     2
 electronics |     4
 computer    |     1
 display     |     1
(4 rows)

Schließlich ermitteln wir den Gesamtwert aller Produkte, die das Tag "electronics" haben.

SELECT SUM((data ->> 'price')::numeric)
FROM products
WHERE data -> 'tags' @> '"electronics"'::jsonb;

Der Operator @> prüft, ob der linke JSONB-Wert den rechten JSONB-Wert enthält. Hier prüfen wir, ob das tags-Array das Element "electronics" enthält.

Das Ergebnis ist die Summe der Preise für die vier elektronischen Produkte:

  sum
--------
 1655
(1 row)

Sie haben nun gelernt, wie Sie Aggregatfunktionen auf Ihre JSONB-Daten anwenden. Um die psql-Shell zu beenden, geben Sie ein:

\q

Zusammenfassung

In diesem Lab haben Sie mehrere fortgeschrittene Operationen zur Verarbeitung von JSONB-Daten in PostgreSQL kennengelernt. Sie haben mit der Erstellung einer Tabelle mit einer JSONB-Spalte begonnen und die Bedeutung der Verwendung eines GIN-Indexes zur Optimierung der Abfrageleistung gesehen. Anschließend haben Sie das Abfragen von JSONB-Daten auf oberster Ebene und verschachtelten Ebenen mit den Operatoren -> und ->> geübt. Darüber hinaus haben Sie gelernt, wie Sie mit der Funktion jsonb_set gezielte Änderungen an JSONB-Dokumenten vornehmen und wie Sie leistungsstarke Datenanalysen mit Aggregatfunktionen wie AVG, COUNT und SUM auf Ihren JSON-Daten durchführen.