Einführung
In diesem Lab erkunden Sie die fortgeschrittenen Datentypen von PostgreSQL, wobei der Schwerpunkt auf JSON/JSONB, Arrays und UUIDs liegt. Sie lernen, wie Sie Daten innerhalb dieser Typen speichern, abfragen und bearbeiten können.
Das Lab beginnt mit einer Demonstration, wie JSON- und JSONB-Daten gespeichert und abgefragt werden. Dazu gehört das Erstellen einer Tabelle mit einer JSONB-Spalte, das Einfügen von JSON-Daten sowie die Verwendung von Operatoren wie -> und ->>, um spezifische Werte zu extrahieren. Anschließend lernen Sie den Umgang mit Array-Spalten und UUIDs.
Speichern und Abfragen von JSON- und JSONB-Daten
In diesem Schritt lernen Sie, wie Sie JSON- und JSONB-Daten in PostgreSQL speichern und abfragen. PostgreSQL bietet zwei Datentypen für die Speicherung von JSON-Daten: JSON und JSONB. Der Datentyp JSON speichert eine exakte Kopie des eingegebenen JSON-Textes, während der Datentyp JSONB die JSON-Daten in einem zerlegten Binärformat speichert. JSONB wird im Allgemeinen bevorzugt, da es eine bessere Performance bei Abfragen und Indizierung bietet.
Beginnen wir damit, die PostgreSQL-Shell zu öffnen. Verbinden Sie sich zunächst mit der Datenbank labex:
sudo -u postgres psql -d labex
Sie sollten nun den PostgreSQL-Prompt sehen:
labex=#
Erstellen wir nun eine Tabelle zum Speichern von JSONB-Daten:
CREATE TABLE products (id SERIAL PRIMARY KEY, data JSONB);
Dieser SQL-Befehl erstellt eine Tabelle namens products. Die Tabelle hat zwei Spalten: id (ein automatisch inkrementierender Integer als Primärschlüssel) und data (eine JSONB-Spalte zum Speichern von JSON-Daten).
Sie sollten eine Ausgabe ähnlich dieser sehen:
CREATE TABLE
Fügen wir nun einige Daten in die products-Tabelle ein:
INSERT INTO products (data) VALUES ('{"name": "Laptop", "price": 1200, "features": ["16GB RAM", "512GB SSD"]}');
INSERT INTO products (data) VALUES ('{"name": "Keyboard", "price": 75, "features": ["Mechanical", "RGB Backlight"]}');
Diese Befehle fügen zwei Zeilen in die products-Tabelle ein. Jede Zeile enthält ein JSON-Objekt mit Informationen über ein Produkt.
Für jeden Einfügevorgang sollten Sie eine Ausgabe ähnlich dieser sehen:
INSERT 0 1
Um die JSON-Daten abzufragen, können Sie die Operatoren -> und ->> verwenden. Der Operator -> gibt ein JSON-Objekt zurück, während der Operator ->> einen JSON-Wert als Text zurückgibt.
Um beispielsweise den Namen des ersten Produkts abzurufen, können Sie die folgende Abfrage verwenden:
SELECT data ->> 'name' FROM products WHERE id = 1;
Dieser Befehl wählt den Wert aus, der dem Schlüssel name in der Spalte data der products-Tabelle zugeordnet ist, wobei die id gleich 1 ist. Der Operator ->> stellt sicher, dass das Ergebnis als Text zurückgegeben wird.
Sie sollten eine Ausgabe ähnlich dieser sehen:
?column?
----------
Laptop
(1 row)
Sie können auch verschachtelte JSON-Objekte abfragen. Um beispielsweise das erste Merkmal des ersten Produkts abzurufen, können Sie die folgende Abfrage verwenden:
SELECT data -> 'features' ->> 0 FROM products WHERE id = 1;
Dieser Befehl wählt zuerst das features-Array aus der data-Spalte aus und wählt dann das Element am Index 0 aus dem Array. Der Operator ->> stellt sicher, dass das Ergebnis als Text zurückgegeben wird.
Sie sollten eine Ausgabe ähnlich dieser sehen:
?column?
----------
16GB RAM
(1 row)
Sie können auch den Operator @> verwenden, um zu prüfen, ob ein JSON-Objekt ein bestimmtes Schlüssel-Wert-Paar enthält. Um beispielsweise alle Produkte mit einem Preis von 75 zu finden, können Sie die folgende Abfrage verwenden:
SELECT data ->> 'name' FROM products WHERE data @> '{"price": 75}';
Dieser Befehl wählt den Namen aller Zeilen aus der products-Tabelle aus, bei denen die data-Spalte ein JSON-Objekt mit einem price-Schlüssel und dem Wert 75 enthält.
Sie sollten eine Ausgabe ähnlich dieser sehen:
?column?
----------
Keyboard
(1 row)
Großartig! Wir haben erfolgreich unsere erste Tabelle mit JSONB-Daten erstellt und gelernt, wie man sie abfragt. Wir werden im nächsten Schritt weiter mit dieser Tabelle arbeiten, um fortgeschrittenere Funktionen hinzuzufügen.
Hinzufügen von Array-Spalten zu einer bestehenden Tabelle
In diesem Schritt lernen Sie, wie Sie Array-Spalten in PostgreSQL hinzufügen und bearbeiten. Wir erweitern unsere bestehende products-Tabelle um eine Array-Spalte zum Speichern von Tags. Array-Spalten ermöglichen es Ihnen, mehrere Werte desselben Datentyps in einer einzigen Spalte zu speichern, was nützlich für Listen wie Tags, Kategorien oder Merkmale ist.
Da wir bereits mit der Datenbank verbunden sind und unsere products-Tabelle aus dem vorherigen Schritt haben, fügen wir nun Array-Spalten hinzu:
ALTER TABLE products ADD COLUMN name VARCHAR(255);
ALTER TABLE products ADD COLUMN tags TEXT[];
Diese Befehle fügen unserer bestehenden products-Tabelle zwei neue Spalten hinzu: name (ein String) und tags (ein Array von Strings). Der Datentyp TEXT[] gibt an, dass die tags-Spalte ein Array von Textwerten ist.
Für jeden ALTER-Befehl sollten Sie eine Ausgabe ähnlich dieser sehen:
ALTER TABLE
Aktualisieren wir nun unsere bestehenden Daten und fügen neue Daten mit den neuen Spalten ein:
UPDATE products SET name = data ->> 'name' WHERE id = 1;
UPDATE products SET name = data ->> 'name' WHERE id = 2;
UPDATE products SET tags = ARRAY['electronics', 'computers', 'portable'] WHERE id = 1;
UPDATE products SET tags = ARRAY['electronics', 'accessories', 'input'] WHERE id = 2;
Diese Befehle aktualisieren unsere bestehenden Produkte mit Namen und Tags, die aus den JSONB-Daten extrahiert wurden, sowie mit neuen Array-Werten.
Für jeden Aktualisierungsvorgang sollten Sie eine Ausgabe ähnlich dieser sehen:
UPDATE 1
Um die Array-Daten abzufragen, können Sie die Array-Indizierung verwenden. Array-Indizes in PostgreSQL beginnen bei 1.
Um beispielsweise das erste Tag des ersten Produkts abzurufen, können Sie die folgende Abfrage verwenden:
SELECT tags[1] FROM products WHERE id = 1;
Dieser Befehl wählt das Element am Index 1 aus dem tags-Array der products-Tabelle aus, wobei die id gleich 1 ist.
Sie sollten eine Ausgabe ähnlich dieser sehen:
tags
-----------
electronics
(1 row)
Sie können auch die Funktion UNNEST verwenden, um ein Array in eine Menge von Zeilen zu erweitern.
Um beispielsweise alle Tags aller Produkte abzurufen, können Sie die folgende Abfrage verwenden:
SELECT name, UNNEST(tags) AS tag FROM products;
Dieser Befehl wählt den name und jedes einzelne tag aus dem tags-Array aus und erstellt für jedes Tag eine neue Zeile.
Sie sollten eine Ausgabe ähnlich dieser sehen:
name | tag
---------+-------------
Laptop | electronics
Laptop | computers
Laptop | portable
Keyboard| electronics
Keyboard| accessories
Keyboard| input
(6 rows)
Sie können den Operator @> verwenden, um zu prüfen, ob ein Array einen bestimmten Wert enthält.
Um beispielsweise alle Produkte mit dem Tag 'electronics' zu finden, können Sie die folgende Abfrage verwenden:
SELECT name FROM products WHERE tags @> ARRAY['electronics'];
Dieser Befehl wählt den Namen aller Zeilen aus der products-Tabelle aus, bei denen das tags-Array den Wert 'electronics' enthält.
Sie sollten eine Ausgabe ähnlich dieser sehen:
name
----------
Laptop
Keyboard
(2 rows)
Sie können auch den Operator && verwenden, um zu prüfen, ob zwei Arrays gemeinsame Elemente haben.
Um beispielsweise alle Produkte zu finden, die sich mindestens ein Tag mit dem ersten Produkt teilen, können Sie die folgende Abfrage verwenden:
SELECT p2.name FROM products p1, products p2 WHERE p1.id = 1 AND p1.tags && p2.tags AND p2.id != 1;
Dieser Befehl wählt den Namen aller Zeilen aus der products-Tabelle (mit Alias p2) aus, die mindestens ein gemeinsames Tag mit den Tags des ersten Produkts (mit Alias p1) haben, wobei das erste Produkt selbst ausgeschlossen wird.
Sie sollten eine Ausgabe ähnlich dieser sehen:
name
----------
Keyboard
(1 row)
Perfekt! Wir haben erfolgreich Array-Spalten zu unserer bestehenden Tabelle hinzugefügt und gelernt, wie man mit Arrays arbeitet. Unsere products-Tabelle enthält nun sowohl JSONB- als auch Array-Datentypen und ist bereit für den nächsten Schritt.
Hinzufügen einer UUID-Spalte und Generierung von UUIDs
In diesem Schritt lernen Sie, wie Sie UUIDs (Universally Unique Identifiers) in PostgreSQL generieren und verwenden. UUIDs sind 128-Bit-Zahlen, die so konzipiert sind, dass sie über Raum und Zeit hinweg eindeutig sind. Sie werden häufig als eindeutige Identifikatoren in Datenbanktabellen verwendet, um Konflikte beim Zusammenführen von Daten aus verschiedenen Quellen zu vermeiden.
Da wir mit unserer bestehenden Sitzung und Tabelle fortfahren, fügen wir unserer aktuellen products-Tabelle eine UUID-Spalte hinzu, um die UUID-Funktionalität zu demonstrieren.
Aktivieren Sie zunächst die UUID-Erweiterung, die Funktionen zur UUID-Generierung bereitstellt:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
Dieser Befehl erstellt die uuid-ossp-Erweiterung, falls sie noch nicht existiert.
Sie sollten eine Ausgabe ähnlich dieser sehen:
CREATE EXTENSION
Fügen wir nun eine UUID-Spalte zu unserer bestehenden products-Tabelle hinzu:
ALTER TABLE products ADD COLUMN uuid_id UUID;
Dieser Befehl fügt unserer bestehenden products-Tabelle eine neue UUID-Spalte hinzu.
Sie sollten eine Ausgabe ähnlich dieser sehen:
ALTER TABLE
Jetzt können wir unsere bestehenden Zeilen mit UUID-Werten aktualisieren, indem wir die Funktion uuid_generate_v4() verwenden:
UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 1;
UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 2;
Diese Befehle aktualisieren unsere bestehenden Produkte mit eindeutigen UUID-Werten. Die Funktion uuid_generate_v4() generiert für jede Zeile eine neue UUID.
Für jeden Aktualisierungsvorgang sollten Sie eine Ausgabe ähnlich dieser sehen:
UPDATE 1
Um die Daten mithilfe von UUIDs abzufragen, können Sie die UUID-Werte in Ihren WHERE-Klauseln verwenden. Da UUIDs zufällig generiert werden, schauen wir uns zunächst unsere aktuellen Daten an:
SELECT id, name, uuid_id FROM products;
Dieser Befehl wählt alle Spalten aus der products-Tabelle aus, um die generierten UUIDs neben unseren anderen Daten zu sehen.
Sie sollten eine Ausgabe ähnlich dieser sehen (Ihre UUID wird anders sein):
id | name | uuid_id
----+----------+--------------------------------------
1 | Laptop | 8f14e45f-ea7b-4f9f-a2b0-73f9c3f85a9b
2 | Keyboard | c9f0f895-fb98-4635-bd31-4f7f4d8f9e7a
(2 rows)
Verwenden Sie nun diese UUID im folgenden Befehl und ersetzen Sie <YOUR_UUID_HERE> durch die tatsächliche UUID, die Sie abgerufen haben:
SELECT name FROM products WHERE uuid_id = '<YOUR_UUID_HERE>';
Dieser Befehl wählt den name aus der products-Tabelle aus, bei der die uuid_id mit der angegebenen UUID übereinstimmt.
Sie sollten eine Ausgabe ähnlich dieser sehen (abhängig davon, welche UUID Sie gewählt haben):
name
----------
Laptop
(1 row)
Behalten Sie die products-Tabelle und die uuid-ossp-Erweiterung bei, da der nächste Schritt diese weiterhin verwendet.
Extrahieren von Daten aus fortgeschrittenen Datentypen
In diesem letzten Schritt üben Sie das Extrahieren von Daten aus allen fortgeschrittenen Datentypen, die wir in diesem Lab zu unserer products-Tabelle hinzugefügt haben. Unsere Tabelle enthält nun JSONB-, Array- und UUID-Spalten und bietet ein umfassendes Beispiel für die fortgeschrittenen Datentypen von PostgreSQL.
Da wir mit unserer bestehenden Sitzung und Tabelle fortfahren, können wir sofort mit den Daten arbeiten, die wir in den vorherigen Schritten aufgebaut haben.
Fügen wir zunächst einige zusätzliche JSONB-Daten hinzu, um unsere Extraktionsbeispiele umfassender zu gestalten:
UPDATE products SET data = '{"brand": "Dell", "model": "XPS 13", "specs": {"ram": "16GB", "storage": "512GB SSD"}, "warranty": "3 years"}' WHERE id = 1;
UPDATE products SET data = '{"brand": "Logitech", "model": "G915", "specs": {"type": "Mechanical", "backlight": "RGB"}, "warranty": "2 years"}' WHERE id = 2;
Diese Befehle aktualisieren unsere bestehenden JSONB-Daten mit detaillierteren Informationen, einschließlich verschachtelter Objekte und zusätzlicher Felder.
Für jeden Aktualisierungsvorgang sollten Sie eine Ausgabe ähnlich dieser sehen:
UPDATE 1
Lassen Sie uns nun das Extrahieren von Daten aus all unseren fortgeschrittenen Datentypen üben. Um Daten aus der JSONB-Spalte data zu extrahieren, können Sie die Operatoren -> und ->> verwenden. Um beispielsweise die Marke aus unseren aktualisierten Daten zu extrahieren:
SELECT data ->> 'brand' FROM products WHERE id = 1;
Dieser Befehl ruft den Wert ab, der dem Schlüssel brand in der Spalte data zugeordnet ist.
Sie sollten eine Ausgabe ähnlich dieser sehen:
?column?
----------
Dell
(1 row)
Um verschachtelte Daten aus der JSONB-Spalte zu extrahieren, können Sie die Operatoren -> und ->> verketten. Um beispielsweise die RAM-Spezifikation zu extrahieren:
SELECT data -> 'specs' ->> 'ram' FROM products WHERE id = 1;
Dieser Befehl ruft den Wert ab, der dem Schlüssel ram innerhalb des specs-Objekts zugeordnet ist.
Sie sollten eine Ausgabe ähnlich dieser sehen:
?column?
----------
16GB
(1 row)
Um Daten aus der tags-Spalte (Array) zu extrahieren, können Sie die Array-Indizierung verwenden, wie in Schritt 2 gelernt. Um beispielsweise das erste Tag des ersten Produkts zu extrahieren:
SELECT tags[1] FROM products WHERE id = 1;
Dieser Befehl ruft das Element am Index 1 aus dem tags-Array der products-Tabelle ab, bei der die id gleich 1 ist.
Sie sollten eine Ausgabe ähnlich dieser sehen:
tags
-----------
electronics
(1 row)
Erstellen wir nun eine umfassende Abfrage, die Daten aus all unseren fortgeschrittenen Datentypen extrahiert – JSONB, Arrays und UUIDs:
SELECT
id,
name,
data ->> 'brand' AS brand,
data -> 'specs' ->> 'ram' AS ram,
tags[1] AS first_tag,
uuid_id
FROM products;
Dieser Befehl ruft Daten aus allen fortgeschrittenen Datentypen ab, mit denen wir gearbeitet haben: die Integer-id, den name, die brand aus der JSONB-Spalte data, die verschachtelte ram-Spezifikation, das erste Element des tags-Arrays und die uuid_id.
Sie sollten eine Ausgabe ähnlich dieser sehen:
name | ?column? | tags
----------+------------+-----------
Laptop | Dell | electronics
Keyboard | Logitech | electronics
(2 rows)
Ausgezeichnet! Sie haben erfolgreich mit allen drei fortgeschrittenen PostgreSQL-Datentypen in einer einzigen Tabelle gearbeitet. Dieses umfassende Beispiel zeigt, wie JSONB, Arrays und UUIDs zusammen verwendet werden können, um flexible und leistungsstarke Datenbankschemata zu erstellen.
Wenn Sie mit allen Schritten fertig sind, können Sie die PostgreSQL-Shell beenden, indem Sie Folgendes eingeben:
\q
Sie können die Tabelle auch behalten, um weiter mit den fortgeschrittenen Datentypen von PostgreSQL zu experimentieren.
Zusammenfassung
In diesem Lab haben Sie schrittweise eine umfassende products-Tabelle erstellt und bearbeitet, die die fortgeschrittenen Datentypen von PostgreSQL in der Praxis demonstriert. Sie begannen mit der Erstellung einer Tabelle mit JSONB-Spalten und lernten, wie man JSON-Daten mithilfe von Operatoren wie -> und ->> speichert und abfragt.
Anschließend haben Sie die Tabelle um Array-Spalten erweitert und gelernt, wie man mehrere Werte in einer einzigen Spalte speichert und diese mithilfe von Array-Indizierung und Funktionen wie UNNEST abfragt. Danach haben Sie UUID-Funktionalität hinzugefügt, indem Sie die uuid-ossp-Erweiterung aktiviert und eine UUID-Spalte zur Generierung eindeutiger Identifikatoren hinzugefügt haben.
Abschließend haben Sie umfassende Datenextraktionstechniken geübt, die alle drei fortgeschrittenen Datentypen – JSONB, Arrays und UUIDs – in komplexen Abfragen kombinierten. Dieser schrittweise Ansatz hat gezeigt, wie diese Datentypen in realen Datenbankschemata zusammenarbeiten können und Flexibilität sowie leistungsstarke Abfragemöglichkeiten für moderne Anwendungen bieten.
