Bei der Programmierung unseres Spielprototypen stoßen wir immer wieder auf neue Herausforderungen. Dabei lernen wir viel, das sich zu teilen lohnt. Heute wollen wir daher auf ein Problem eingehen, das uns bei der Entwicklung unserer Kartendatenkank begegnete: das Exportieren aller Kartendaten in einem Format, das der Spielprototypen verwenden kann.

Alle unsere Kartendaten sind in einer SQL-Datenbank gespeichert, genauer gesagt verwenden wir das quelloffene und verbreitete PostgreSQL. Doch wie können wir effizient alle Daten in ein Format konvertieren, das der Gameprototyp versteht, wie beispielsweise JSON?

Viele Programmiersprachen ermöglichen es, Objekte oder Key-Value-Maps in JSON umzuwandeln. Dies wird bei großen hiearchischen Strukturen jedoch schnell aufwendig und benötigt entweder komplizierte Methoden, um die Objekte zu erstellen, oder sogar mehrere Datenbankabfragen.

PostgreSQL unterstützt von Haus auf viele eingebaute Funktionen, welche mit JSON umgehen können. Diese können dafür genutzt werden, JSON in der Datenbank zu speichern, oder auch JSON in den SQL-Abfragen zu generieren.

Wie diese Funktionen genutzt werden können, um Daten schon in der SQL-Abfrage in JSON umzuwandeln, wollen wir hier an einem kleinen Beispiel erläutern:

Alle Teile des Beispiels könnt ihr auch hier live ausprobieren.

Wir nehmen ein sehr rudimentäres Bestellsystem an, bestehend aus Rechnungen (invoices), Bestellungen (orders), Produkten (products) und deren Attribute (product_attributes).

Das Datenbankschema hierfür könnte beispielsweise so aussehen:

CREATE TABLE product_attributes (
  product_id INT,
  attribute_name VARCHAR,
  value VARCHAR
);
CREATE TABLE products (
  product_id INT,
  name VARCHAR
); 
CREATE TABLE invoices (
  invoice_id INT,
  customer VARCHAR
);
CREATE TABLE orders (
  invoice_id INT,
  product_id INT,
  amount INT
);

Nun wollen wir eine Abfrage gestalten, welche alle Rechnungen auf einmal ausgibt, und hierbei alle dazugehörigen Bestellungen mit den Produkten, der Anzahl, und den Attributen ausgibt. Das Ergebnis könnte so aussehen:

[
  {
    "id": 1,
    "customer": "Max Mustermann",
    "orders": [
      {
        "name": "Testprodukt",
        "attributes": {
          "Größe": "200m",
          "Gewicht": "3kg"
        },
        "amount": 4
      },
      {
        "name": "Spezialprodukt",
        "attributes": {
          "Art": "Spezial",
          "Länge": "2m"
        },
        "amount": 2
      }
    ]
  },
  {
    "id": 2,
    "customer": "Alice",
    "orders": [
      {
        "name": "Anderes Produkt",
        "attributes": {
          "Typ": "Standard"
        },
        "amount": 1
      },
      {
        "name": "Spezialprodukt",
        "attributes": {
          "Art": "Spezial",
          "Länge": "2m"
        },
        "amount": 1
      }
    ]
  },
  {
    "id": 3,
    "customer": "Bob",
    "orders": [
      {
        "name": "Testprodukt",
        "attributes": {
          "Größe": "200m",
          "Gewicht": "3kg"
        },
        "amount": 2
      },
      {
        "name": "Anderes Produkt",
        "attributes": {
          "Typ": "Standard"
        },
        "amount": 3
      },
      {
        "name": "Spezialprodukt",
        "attributes": {
          "Art": "Spezial",
          "Länge": "2m"
        },
        "amount": 1
      }
    ]
  }
]

Wie können wir eine Abfrage gestalten, welches diese Ergebnis erzeugt?

Am besten gelingt dies mit dem PostgreSQL-Feature der „Common Table Expressions“, welche eine übersichtliche Variante zu Subqueries sind. Die Query baut man am besten von innen nach außen. Man benutzt die entsprechenden PostgreSQL-JSON-Funktionen um den gewünschten Teilbereich zu erzeugen, und verbindet diese Teilergebnisse dann mit JOINs.

In unserem Beispiel sind an der innersten Position die Produktattribute, welche im JSON in Form von Key-Value-Paaren dargestellt werden. Mit folgender Query wird der Inhalt von „attributes“ für jedes Produkt erstellt:

SELECT product_id, json_object_agg(attribute_name, value) AS attributes
FROM product_attributes
GROUP BY product_id

Diese Abfrage können wir nun mittels dem WITH-Schlüsselworts als Teil einer größeren Query benutzen, welche die Attribut-Informationen mit den Informationen über Produkte und Bestellungen erweitert.

WITH json_attributes AS (
  SELECT product_id, json_object_agg(attribute_name, value) AS attributes
  FROM product_attributes GROUP BY product_id
)
SELECT invoice_id, product_id, 
  json_build_object('name', name, 'attributes', attributes, 'amount', amount) AS "order"
FROM orders 
INNER JOIN products USING (product_id) 
INNER JOIN json_attributes USING (product_id)

Dies kann schrittweise wiederholt werden, bis man schließlich zur endgültigen Abfrage gelangt, welche insgesamt 4 Zwischenschritte benutzt:

WITH json_attributes AS (
  SELECT product_id, json_object_agg(attribute_name, value) AS attributes
  FROM product_attributes GROUP BY product_id
),
json_order AS (
  SELECT invoice_id, product_id, 
    json_build_object('name', name, 'attributes', attributes, 'amount', amount) AS "order"
  FROM orders INNER JOIN products USING (product_id) INNER JOIN json_attributes USING (product_id)
),
json_orders AS (
  SELECT invoice_id, json_agg("order") AS orders
  FROM json_order GROUP BY invoice_id
),
json_invoices AS (
  SELECT json_build_object('id', invoice_id, 'customer', customer, 'orders', orders) AS invoice
  FROM invoices INNER JOIN json_orders USING (invoice_id)
)
SELECT json_agg(invoice) FROM json_invoices;

Beachtenswert ist hierbei, dass insgesamt 3 verschiedene JSON-Funktionen von PostgreSQL benutzt werden: json_object_agg (zur Erstellung der Key-Value-Paare), json_build_object (für die „normalen“ Attribute) und json_agg (für Arrays).

Wir hoffen wir konnten mit diesem Artikel einen kleinen Einblick in die Entwicklung bei Beyond Worlds geben und Entwicklern helfen, die auf ein ähnliches Problem stoßen.

Euer Beyond Worlds-Team

Teilen: