JSON-Spalten in MySQL: praktischer Einsatz und Indizierung

1. Der native JSON-Typ in MySQL

Seit MySQL 5.7 existiert ein nativer JSON-Datentyp, der sich fundamental von der reinen Speicherung als TEXT unterscheidet. Intern validiert MySQL beim Einfügen die Syntax und speichert das Dokument in einem binären Format, das für schnellen Lesezugriff auf einzelne Werte optimiert ist, statt bei jeder Abfrage den kompletten Text neu parsen zu müssen. Ein ungültiges JSON-Dokument wird beim INSERT oder UPDATE sofort mit einem Fehler abgewiesen, was Datenqualität auf Datenbankebene garantiert.

Der native Typ bringt eine ganze Reihe von JSON-spezifischen Funktionen mit, mit denen sich Werte extrahieren, verändern und aggregieren lassen, ohne die Anwendung mit dem Parsen zu belasten. Das ist besonders nützlich für Anwendungsfälle mit variabler oder sich häufig ändernder Struktur, etwa Produktattribute mit unterschiedlichen Feldern je Kategorie, Konfigurationsobjekte oder Event-Payloads, bei denen ein starres relationales Schema unpraktisch wäre.

Wichtig ist dabei die Unterscheidung zwischen JSON als Speicherformat und JSON als vollwertigem Ersatz für relationales Design. Der native JSON-Typ löst das Speicherproblem elegant, ersetzt aber nicht die Notwendigkeit, bei häufig abgefragten oder fachlich zentralen Feldern über eine geeignete Indizierungsstrategie nachzudenken, die im weiteren Verlauf dieses Artikels im Detail behandelt wird.

-- Native JSON column with automatic validation on write
CREATE TABLE product_attribute (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  sku VARCHAR(64) NOT NULL,
  attributes JSON NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO product_attribute (sku, attributes) VALUES
  ('SHOE-001', '{"color": "black", "size": [40, 41, 42], "material": "leather"}'),
  ('SHIRT-002', '{"color": "blue", "fit": "slim", "sleeve": "long"}');

-- Invalid JSON is rejected immediately at insert time
-- INSERT INTO product_attribute (sku, attributes) VALUES ('X', '{invalid}');
-- ERROR 3140 (22032): Invalid JSON text

2. JSON_EXTRACT und die Pfad-Syntax

JSON_EXTRACT liest einzelne Werte aus einem JSON-Dokument anhand eines Pfadausdrucks, der mit $ beginnt und Objektschlüssel sowie Array-Indizes referenziert. Die verkürzte Pfeil-Syntax -> ist ein Alias für JSON_EXTRACT, während ->> zusätzlich das Ergebnis von einem JSON-Wert in einen unquotierten String umwandelt, was für Vergleiche und Sortierungen in der Praxis fast immer die richtige Wahl ist.

Diese Extraktionsfunktionen erlauben es, gezielt nach eingebetteten Werten zu filtern, ohne das gesamte Dokument in die Anwendung zu laden und dort zu parsen. Für gelegentliche Abfragen oder Admin-Werkzeuge ist das völlig ausreichend, für häufig ausgeführte Abfragen im Produktionsbetrieb braucht es aber zusätzlich einen Index, weil JSON_EXTRACT ohne Index bei jeder Zeile das komplette Dokument parsen muss.

-- Extract a single value with the arrow operator
SELECT sku, attributes->>'$.color' AS color
FROM product_attribute
WHERE attributes->>'$.color' = 'black';

-- Equivalent explicit form
SELECT sku, JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color')) AS color
FROM product_attribute;

-- Nested path and array access
SELECT sku, attributes->>'$.size[0]' AS first_available_size
FROM product_attribute
WHERE JSON_CONTAINS(attributes, '41', '$.size');

3. JSON_TABLE: JSON in relationale Zeilen umwandeln

JSON_TABLE, verfügbar seit MySQL 8.0, überführt ein JSON-Dokument oder Array in ein virtuelles, relationales Ergebnis mit echten Zeilen und Spalten, das direkt in JOINs, WHERE-Klauseln und Aggregationen verwendet werden kann. Das ist besonders wertvoll, wenn ein JSON-Array mehrere gleichartige Einträge enthält, etwa Produktvarianten oder Bestellpositionen, die man wie normale Tabellenzeilen behandeln möchte, ohne sie physisch zu normalisieren.

Der Vorteil gegenüber mehreren einzelnen JSON_EXTRACT-Aufrufen liegt in der Fähigkeit, ein Array in mehrere Ausgabezeilen zu entfalten. Ohne JSON_TABLE müsste man Array-Elemente entweder in der Anwendung nachbearbeiten oder mit umständlichen, schwer lesbaren JSON_EXTRACT-Ketten pro Index simulieren, was bei variabler Array-Länge ohnehin nicht praktikabel ist.

-- Expand a JSON array of order line items into relational rows
SELECT o.id AS order_id, items.sku, items.qty, items.price
FROM orders o,
JSON_TABLE(
  o.line_items,
  '$[*]' COLUMNS (
    sku    VARCHAR(64) PATH '$.sku',
    qty    INT         PATH '$.qty',
    price  DECIMAL(10,2) PATH '$.price'
  )
) AS items
WHERE o.status = 'completed';

-- Aggregate over the expanded rows directly in SQL
SELECT items.sku, SUM(items.qty) AS total_sold
FROM orders o,
JSON_TABLE(o.line_items, '$[*]'
  COLUMNS (sku VARCHAR(64) PATH '$.sku', qty INT PATH '$.qty')
) AS items
GROUP BY items.sku
ORDER BY total_sold DESC;

4. Generierte Spalten für Indizierung

MySQL kann keinen regulären B-Tree-Index direkt auf einen JSON-Pfad legen, weil JSON-Spalten selbst nicht direkt indizierbar sind. Die Lösung ist eine generierte Spalte, die den gewünschten Wert per JSON_EXTRACT aus dem Dokument ableitet und als reguläre, indizierbare Spalte materialisiert. Mit STORED statt VIRTUAL wird der Wert physisch auf der Festplatte gespeichert, was den Index performanter macht, aber zusätzlichen Speicherplatz kostet.

Dieses Muster ist der zentrale Baustein für performante JSON-Abfragen in Produktion: Für jedes häufig gefilterte oder sortierte JSON-Feld wird eine generierte Spalte mit passendem Index angelegt. Die Anwendung greift weiterhin transparent über das JSON-Feld zu, während der Optimizer bei geeigneten Abfragen automatisch den Index auf der generierten Spalte nutzt, ohne dass die Abfrage selbst geändert werden muss.

-- Generated column extracts the color value for indexing
ALTER TABLE product_attribute
  ADD COLUMN color VARCHAR(32)
    GENERATED ALWAYS AS (attributes->>'$.color') STORED,
  ADD INDEX idx_color (color);

-- The optimizer now uses the index transparently
EXPLAIN SELECT sku FROM product_attribute WHERE color = 'black';
-- key: idx_color (index used, no full JSON scan needed)

-- Without the generated column, MySQL parses every JSON document
EXPLAIN SELECT sku FROM product_attribute
WHERE attributes->>'$.material' = 'leather';
-- type: ALL (full table scan, JSON parsed for every row)

5. Multi-Value-Indizes für JSON-Arrays

Für den häufigen Fall, dass ein JSON-Array mehrere Werte enthält, die einzeln durchsuchbar sein sollen, etwa Tags oder Kategorie-IDs, bietet MySQL seit Version 8.0.17 Multi-Value-Indizes. Anders als ein regulärer Index, der genau einen Wert pro Zeile abbildet, kann ein Multi-Value-Index mehrere Einträge pro Zeile referenzieren, sodass eine Zeile über jeden ihrer Array-Werte gefunden werden kann.

Ein Multi-Value-Index wird mit der Funktion CAST(... AS ... ARRAY) innerhalb der Indexdefinition erstellt und funktioniert gut in Kombination mit MEMBER OF, JSON_CONTAINS und JSON_OVERLAPS. Für Tag-Systeme oder mehrwertige Kategoriezuordnungen ist das oft die pragmatischere Alternative zu einer eigenen n:m-Verknüpfungstabelle, insbesondere wenn die Werte selten eigenständig abgefragt, sondern meist zusammen mit dem übergeordneten Dokument gelesen werden.

-- Multi-value index on a JSON array of tag strings
ALTER TABLE product_attribute
  ADD COLUMN tags JSON,
  ADD INDEX idx_tags ((CAST(tags AS CHAR(32) ARRAY)));

UPDATE product_attribute
SET tags = '["sale", "new-arrival", "bestseller"]'
WHERE sku = 'SHOE-001';

-- Uses the multi-value index to find matching rows
SELECT sku FROM product_attribute
WHERE 'sale' MEMBER OF (tags->'$');

6. Wann JSON die richtige Wahl ist

JSON-Spalten passen gut zu Daten mit variabler, unvorhersehbarer oder sich häufig ändernder Struktur, bei denen ein starres Tabellenschema entweder viele Nullable-Spalten oder ein aufwendiges Entity-Attribute-Value-Modell erzwingen würde. Klassische Beispiele sind Produktattribute mit kategorieabhängigen Feldern, externe API-Antworten, die roh zwischengespeichert werden sollen, oder Konfigurationsobjekte, die selten strukturiert abgefragt, aber häufig als Ganzes gelesen und geschrieben werden.

Ebenso sinnvoll ist JSON für Audit-Logs und Event-Payloads, bei denen unterschiedliche Ereignistypen unterschiedliche Zusatzdaten mitbringen und eine vollständige relationale Normalisierung mehr Komplexität schaffen würde, als sie löst. In all diesen Fällen ist entscheidend, dass die am häufigsten gefilterten Felder über generierte Spalten indiziert werden, damit die Flexibilität von JSON nicht auf Kosten der Abfrageperformance geht.

7. Wann ein normalisiertes Schema besser ist

Sobald ein Feld regelmässig für Filterung, Sortierung, Aggregation oder Fremdschlüssel-Beziehungen benötigt wird, ist ein reguläres, normalisiertes Schema fast immer die bessere Wahl gegenüber JSON. Referentielle Integrität lässt sich innerhalb eines JSON-Dokuments nicht durch die Datenbank erzwingen, das heisst ungültige oder verwaiste Referenzen bleiben unbemerkt, bis die Anwendung sie explizit prüft. Auch Aggregatfunktionen über viele Zeilen hinweg sind bei JSON-Werten spürbar langsamer als bei nativen numerischen oder Datumsspalten, selbst mit generierten Spalten als Hilfsmittel.

Ein guter Praxistest: Wenn ein Feld in mehr als der Hälfte aller Abfragen an die Tabelle vorkommt, in JOIN-Bedingungen auftaucht oder eine eigene Geschäftslogik-Validierung benötigt, gehört es als eigene Spalte ins Schema, nicht ins JSON-Dokument. JSON sollte die Ausnahme für wirklich variable Daten bleiben, nicht der Standardweg, um Schema-Design-Entscheidungen aufzuschieben.

8. Schreiboperationen: JSON_SET und partielle Updates

Für das Ändern einzelner Werte innerhalb eines JSON-Dokuments bietet MySQL JSON_SET, JSON_REPLACE und JSON_REMOVE, mit denen sich gezielt Pfade verändern lassen, ohne das gesamte Dokument in der Anwendung neu zusammenzusetzen und zurückzuschreiben. Wichtig zu wissen: Auf Storage-Ebene wird dennoch das komplette Dokument neu geschrieben, InnoDB kennt kein echtes partielles Update einzelner JSON-Pfade auf Zeilenebene, was bei sehr grossen Dokumenten und hoher Schreibfrequenz zu spürbarem I/O führen kann.

Für Anwendungsfälle mit häufigen, kleinen Änderungen an grossen JSON-Dokumenten lohnt sich deshalb eine genaue Prüfung, ob nicht doch eine separate Tabelle für die häufig geänderten Felder sinnvoller ist, während stabile, selten geänderte Zusatzdaten im JSON-Dokument verbleiben. Diese hybride Strategie kombiniert die Flexibilität von JSON mit der Schreibeffizienz normaler Spalten.

-- Update a single nested value without rewriting the whole document manually
UPDATE product_attribute
SET attributes = JSON_SET(attributes, '$.color', 'navy')
WHERE sku = 'SHOE-001';

-- Remove a key entirely
UPDATE product_attribute
SET attributes = JSON_REMOVE(attributes, '$.material')
WHERE sku = 'SHOE-001';

-- Append a value to an existing array
UPDATE product_attribute
SET attributes = JSON_ARRAY_APPEND(attributes, '$.size', 43)
WHERE sku = 'SHOE-001';

9. JSON-Ansätze im direkten Vergleich

Die folgende Übersicht ordnet die verschiedenen Techniken für den Umgang mit JSON-Daten in MySQL nach Anwendungsfall und Performance-Charakteristik ein.

Der rote Faden: JSON ohne begleitende generierte Spalten ist für die gelegentliche Abfrage geeignet, aber nicht für Produktionslast. Sobald ein Wert regelmässig gefiltert wird, führt kein Weg an einer generierten Spalte mit Index oder, bei zentraler fachlicher Bedeutung, an einer vollständig normalisierten Spalte vorbei.

10. Zusammenfassung

Der native JSON-Typ in MySQL löst ein reales Problem: die Speicherung semi-strukturierter, variabler Daten ohne aufwendiges Entity-Attribute-Value-Design. JSON_EXTRACT und JSON_TABLE machen diese Daten performant abfragbar, aber erst generierte Spalten mit Index verhindern, dass jede Abfrage das komplette Dokument parsen muss. Multi-Value-Indizes erweitern das für Array-Werte wie Tags.

Die wichtigste Design-Entscheidung bleibt: JSON für wirklich variable, selten strukturiert abgefragte Daten, ein normalisiertes Schema für alles, was regelmässig gefiltert, sortiert, aggregiert oder über Fremdschlüssel verknüpft wird. Wer diese Grenze konsequent zieht, bekommt die Flexibilität von JSON, ohne die Nachteile eines unstrukturierten Schemas in Kauf zu nehmen.

11. FAQ: JSON-Spalten in MySQL