Übung Postgres JSON

Tabellendefinition

CREATE TABLE bestellungen (
  id       SERIAL PRIMARY KEY,
  erstellt TIMESTAMPTZ DEFAULT now(),
  daten    JSONB
);

Testdaten

INSERT INTO bestellungen (daten) VALUES
  ('{"kunde":"Alice","stadt":"Wien",
    "artikel":[
      {"name":"Laptop",    "preis":999.00, "menge":1},
      {"name":"Maus",      "preis":29.90,  "menge":2}
    ],
    "status":"geliefert","gesamt":1058.80}');

INSERT INTO bestellungen (daten) VALUES
  ('{"kunde":"Bob","stadt":"Berlin",
    "artikel":[
      {"name":"Monitor",   "preis":349.00, "menge":1}
    ],
    "status":"offen","gesamt":349.00,"gutschein":true}');

INSERT INTO bestellungen (daten) VALUES
  ('{"kunde":"Clara","stadt":"Wien",
    "artikel":[
      {"name":"Tastatur",  "preis":89.90,  "menge":1},
      {"name":"Headset",   "preis":59.90,  "menge":1}
    ],
    "status":"geliefert","gesamt":149.80}');

INSERT INTO bestellungen (daten) VALUES
  ('{"kunde":"David","stadt":"Hamburg",
    "artikel":[
      {"name":"Laptop",    "preis":1199.00,"menge":1},
      {"name":"Laptop-Tasche","preis":49.90,"menge":1}
    ],
    "status":"storniert","gesamt":1248.90}');

INSERT INTO bestellungen (daten) VALUES
  ('{"kunde":"Eva","stadt":"Berlin",
    "artikel":[
      {"name":"Maus",      "preis":29.90,  "menge":3}
    ],
    "status":"offen","gesamt":89.70,"gutschein":false}');

Aufgaben

Aufgabe 1 · Kundennamen ausgeben

Gib für alle Bestellungen die id und den Kundennamen als einfachen Textwert aus.

Erwartetes Ergebnis (Ausschnitt):

 id | kunde
----+-------
  1 | Alice
  2 | Bob
  ...

Hinweis: Welcher Operator liefert einen Textwert statt eines JSON-Werts?

Aufgabe 2 · Artikel-Array lesen

Gib für jede Bestellung die id und den Wert des Feldes artikel als JSONB-Wert aus.

Erwartetes Ergebnis (Ausschnitt):

 id | artikel
----+----------------------------------------------------------
  1 | [{"menge": 1, "name": "Laptop", "preis": 999.00}, ...]
  ...

Hinweis: -> gibt JSON/JSONB zurück, ->> gibt Text zurück.

Aufgabe 3 · Ersten Artikelnamen lesen

Gib für jede Bestellung den Namen des ersten Artikels (Index 0) als Text aus.

Erwartetes Ergebnis:

 id | erster_artikel
----+----------------
  1 | Laptop
  2 | Monitor
  3 | Tastatur
  4 | Laptop
  5 | Maus

Hinweis: Nutze den Pfad-Operator #>> mit dem Pfad-Array '{artikel,0,name}'.

Aufgabe 4 · Offene Bestellungen filtern

Gib alle Bestellungen aus, deren Status gleich 'offen' ist. Zeige Kundename und Gesamtbetrag.

Erwartetes Ergebnis:

 kunde | gesamt
-------+--------
 Bob   | 349.00
 Eva   |  89.70

Hinweis: ->> in der WHERE-Klausel vergleicht als Text.

Aufgabe 5 · Bestellungen aus Wien (Containment)

Gib alle Bestellungen aus, die aus der Stadt Wien stammen. Verwende den Containment-Operator @>.

Erwartetes Ergebnis:

 id | kunde
----+-------
  1 | Alice
  3 | Clara

Hinweis: daten @> '{"stadt":"Wien"}' prüft ob das JSON-Objekt dieses Key-Value-Paar enthält.

Aufgabe 6 · Bestellungen mit Gutschein-Feld

Gib alle Bestellungen aus, in denen das Feld gutschein vorhanden ist (unabhängig vom Wert true/false).

Erwartetes Ergebnis:

 id | kunde | gutschein
----+-------+-----------
  2 | Bob   | true
  5 | Eva   | false

Hinweis: Der Operator ? prüft, ob ein Key im JSON-Objekt existiert.

Aufgabe 7 · Hohe Bestellsummen

Gib alle Bestellungen aus, bei denen der Gesamtbetrag größer als 500 ist. Sortiere absteigend nach Gesamtbetrag.

Erwartetes Ergebnis:

 kunde | gesamt
-------+---------
 David | 1248.90
 Alice | 1058.80

Hinweis: Das Feld gesamt ist ein JSON-Textwert – für numerische Vergleiche muss ein Cast auf ::numeric erfolgen.

Aufgabe 8 · Status aktualisieren

Setze den Status der Bestellung von Bob (id = 2) auf 'versendet'. Verwende jsonb_set. Prüfe danach das Ergebnis mit einer SELECT-Abfrage.

Ergebnis nach dem UPDATE:

 id | status
----+-----------
  2 | versendet

Hinweis: jsonb_set(daten, '{status}', '"versendet"') – der neue Wert muss gültiges JSON sein, also mit inneren Anführungszeichen für Strings.

Aufgabe 9 · Artikel-Array entpacken

Gib alle Artikel aus allen Bestellungen als einzelne Zeilen aus. Zeige den Bestellungs-id, Artikelname, Preis und Menge.

Erwartetes Ergebnis (Ausschnitt):

 id | name          | preis   | menge
----+---------------+---------+-------
  1 | Laptop        |  999.00 |     1
  1 | Maus          |   29.90 |     2
  2 | Monitor       |  349.00 |     1
  ...

Hinweis: jsonb_array_elements(daten->'artikel') liefert jedes Array-Element als eigene Zeile.

Aufgabe 10 · Anzahl Bestellungen pro Stadt

Zähle, wie viele Bestellungen pro Stadt vorliegen. Sortiere absteigend nach Anzahl.

Erwartetes Ergebnis:

 stadt   | anzahl
---------+--------
 Wien    |      2
 Berlin  |      2
 Hamburg |      1

Hinweis: daten->>'stadt' extrahiert den Stadtnamen als Text – dann kann darauf gruppiert werden.