JSON-Spalten in MySQL und MariaDB sinnvoll einsetzen

1. Der JSON-Datentyp in MySQL 8: Binärformat und Validierung

MySQL 8 speichert JSON-Werte in einem binär-optimierten Format, das nicht identisch mit dem Text-JSON ist. Das interne Format erlaubt es, einzelne Pfade direkt zu lesen, ohne das gesamte Dokument zu parsen – ein erheblicher Performance-Vorteil bei großen JSON-Dokumenten. Gleichzeitig validiert MySQL automatisch beim Einfügen, ob der Wert gültiges JSON ist.

-- JSON-Spalte anlegen
CREATE TABLE product_config (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    config     JSON NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Gültiges JSON einfügen
INSERT INTO product_config (product_id, config) VALUES
(42, '{"color": "blue", "size": "L", "options": {"gift_wrap": true, "engraving": "Max"}}'),
(43, '{"color": "red", "size": "M", "options": {"gift_wrap": false}}');

-- Ungültiges JSON: MySQL wirft einen Fehler
-- INSERT INTO product_config (product_id, config) VALUES (44, '{invalid json}');
-- ERROR 3140 (22032): Invalid JSON text ...

-- Größe eines JSON-Dokuments in Bytes prüfen
SELECT id, product_id, JSON_STORAGE_SIZE(config) AS bytes
FROM product_config;
    

2. JSON_EXTRACT, -> und ->>: Werte lesen

MySQL bietet drei Wege, Werte aus JSON zu lesen. JSON_EXTRACT(doc, '$.pfad') und der Kurzoperator doc->'$.pfad' sind identisch und geben den Wert mit JSON-Anführungszeichen zurück. Der Operator ->> (inline path + unquote) entspricht JSON_UNQUOTE(JSON_EXTRACT(...)) und liefert den Wert ohne Anführungszeichen – ideal für Vergleiche und Indizes.

-- Drei äquivalente Wege zum Lesen
SELECT
    JSON_EXTRACT(config, '$.color')   AS color_extract,  -- "blue" (mit Anführungszeichen)
    config->'$.color'                 AS color_arrow,     -- "blue" (mit Anführungszeichen)
    config->>'$.color'                AS color_unquoted,  -- blue   (ohne Anführungszeichen)
    config->>'$.options.gift_wrap'    AS gift_wrap,
    config->>'$.options.engraving'    AS engraving        -- NULL wenn nicht vorhanden
FROM product_config
WHERE product_id = 42;

-- Für Vergleiche immer ->> oder JSON_UNQUOTE nutzen:
SELECT * FROM product_config WHERE config->>'$.color' = 'blue';  -- korrekt
-- SELECT * FROM product_config WHERE config->'$.color' = 'blue'; -- FALSCH: "blue" != blue

-- Array-Elemente lesen
SELECT config->>'$.sizes[0]' AS first_size FROM product_config;

-- Alle Schlüssel eines JSON-Objekts
SELECT JSON_KEYS(config) FROM product_config WHERE product_id = 42;
-- ["color", "size", "options"]
    

3. JSON_SET, JSON_INSERT, JSON_REPLACE und JSON_REMOVE

JSON-Mutationsfunktionen erlauben atomare Änderungen an JSON-Dokumenten, ohne den gesamten Wert in der Anwendung laden zu müssen. Die drei Schreibfunktionen unterscheiden sich im Verhalten bei bereits vorhandenen Pfaden.

-- JSON_SET: Schreibt immer (Insert + Update)
UPDATE product_config
SET config = JSON_SET(config, '$.color', 'green', '$.stock', 25)
WHERE product_id = 42;

-- JSON_INSERT: Nur Insert wenn Pfad nicht existiert
UPDATE product_config
SET config = JSON_INSERT(config, '$.new_field', 'neuer Wert')
WHERE product_id = 42;
-- Wenn '$.new_field' schon existiert: kein Update

-- JSON_REPLACE: Nur Update wenn Pfad existiert
UPDATE product_config
SET config = JSON_REPLACE(config, '$.color', 'yellow')
WHERE product_id = 42;
-- Wenn '$.color' nicht existiert: kein Insert

-- JSON_REMOVE: Pfad löschen
UPDATE product_config
SET config = JSON_REMOVE(config, '$.options.engraving')
WHERE product_id = 42;

-- Verschachteltes Update: Wert in Array aktualisieren
UPDATE product_config
SET config = JSON_SET(config, '$.options.gift_wrap', false)
WHERE product_id = 42;
    

4. Generierte Spalten für JSON-Indizes

Direkte Indizes auf JSON-Spalten sind in MySQL nicht möglich. Der Ausweg sind generierte Spalten (Generated Columns), die einen JSON-Pfad-Wert extrahieren und als eigene Spalte speichern. Diese Spalten werden automatisch befüllt und können indiziert werden. Es gibt VIRTUAL (on-the-fly berechnet) und STORED (physisch gespeichert) generierte Spalten.

-- STORED generierte Spalte für JSON-Pfad anlegen
ALTER TABLE product_config
ADD COLUMN config_color VARCHAR(50)
    GENERATED ALWAYS AS (config->>'$.color') STORED;

-- Index auf die generierte Spalte
CREATE INDEX idx_config_color ON product_config(config_color);

-- Abfrage nutzt nun den Index:
EXPLAIN SELECT * FROM product_config WHERE config_color = 'blue';
-- type=ref, key=idx_config_color, rows=2  (statt Full Table Scan)

-- VIRTUAL (schnelleres Insert, kein physischer Storage, eingeschränkte Index-Typen)
ALTER TABLE product_config
ADD COLUMN config_gift_wrap TINYINT(1)
    GENERATED ALWAYS AS (config->>'$.options.gift_wrap') VIRTUAL;

-- Eindeutiger Index auf JSON-Pfad (z.B. für externe IDs)
ALTER TABLE product_config
ADD COLUMN external_id VARCHAR(100)
    GENERATED ALWAYS AS (config->>'$.external_id') STORED;
CREATE UNIQUE INDEX idx_external_id ON product_config(external_id);
    

5. JSON_TABLE(): JSON in relationale Zeilen umwandeln

JSON_TABLE() ist eine Tabellenfunktion (MySQL 8.0+), die JSON-Daten on-the-fly in relationale Tabellenzeilen umwandelt. Sie ist besonders nützlich, wenn JSON-Arrays als separate Zeilen gejoint werden müssen.

-- JSON_TABLE: Array-Elemente als Zeilen ausgeben
CREATE TABLE orders_json (
    order_id INT PRIMARY KEY,
    items    JSON
);
INSERT INTO orders_json VALUES
(1, '[{"sku":"A001","qty":2,"price":19.99},{"sku":"B002","qty":1,"price":49.00}]'),
(2, '[{"sku":"C003","qty":3,"price":9.50}]');

-- JSON_TABLE als FROM-Quelle
SELECT
    o.order_id,
    jt.sku,
    jt.qty,
    jt.price,
    jt.qty * jt.price AS line_total
FROM orders_json o,
JSON_TABLE(
    o.items,
    '$[*]' COLUMNS(
        sku   VARCHAR(50)    PATH '$.sku',
        qty   INT            PATH '$.qty',
        price DECIMAL(10,2)  PATH '$.price'
    )
) AS jt;

-- Ergebnis:
-- order_id | sku  | qty | price | line_total
-- 1        | A001 |  2  | 19.99 | 39.98
-- 1        | B002 |  1  | 49.00 | 49.00
-- 2        | C003 |  3  |  9.50 | 28.50
    

6. Wann JSON in der Datenbank wirklich Sinn ergibt

JSON-Spalten sind kein Universalwerkzeug. Sie sind dann sinnvoll, wenn die Datenstruktur variiert und nicht vorab vollständig bekannt ist, wenn die Daten hauptsächlich als Block gelesen (nicht gefiltert) werden, oder wenn eine flexible Erweiterbarkeit wichtiger ist als relationale Abfragbarkeit.

• Produktkonfigurationsoptionen: Gewählte Farben, Größen, Custom Options – variieren pro Produkt und Kundenauswahl.
• Event-Log-Payloads: Jeder Event-Typ hat eine andere Payload-Struktur. Normalisierung wäre impraktisch.
• API-Response-Cache: Externe API-Antworten für spätere Verarbeitung speichern, ohne das Schema zu kennen.
• Flexible Kundenattribute: Zusätzliche Profilfelder, die nicht für alle Kunden gelten.

Nicht sinnvoll ist JSON wenn die Daten regelmäßig in WHERE-Klauseln gefiltert, in GROUP BY aggregiert oder mit anderen Tabellen gejoint werden müssen – das erfordert entweder Normalisierung oder generierte Spalten mit Index.

7. Magento: quote_item.product_options und JSON in der Praxis

Magento verwendet JSON in mehreren Kerntabellen. Das wichtigste Beispiel ist quote_item.product_options, das alle Konfigurationsdetails eines Warenkorb-Produkts als JSON speichert – inklusive gewählter Attribute, Bundle-Optionen und Custom Options.

-- quote_item.product_options inspizieren
SELECT
    qi.item_id,
    qi.sku,
    qi.name,
    qi.qty,
    qi.price,
    qi.product_options
FROM quote_item qi
WHERE qi.quote_id = 12345
LIMIT 5;

-- Bestimmtes Feld aus product_options lesen
SELECT
    qi.item_id,
    qi.sku,
    qi.product_options->>'$.info_buyRequest.qty'        AS requested_qty,
    qi.product_options->>'$.info_buyRequest.product'    AS product_id,
    qi.product_options->>'$.attributes_info[0].label'  AS option_label,
    qi.product_options->>'$.attributes_info[0].value'  AS option_value
FROM quote_item qi
WHERE qi.product_options IS NOT NULL
LIMIT 10;

-- sales_order_item hat das gleiche Feld nach der Bestellplatzierung
SELECT
    soi.item_id,
    soi.sku,
    soi.product_options->>'$.attributes_info[0].value' AS selected_option
FROM sales_order_item soi
WHERE soi.order_id = 100042;
    

8. MariaDB JSON: Unterschiede und Besonderheiten

MariaDB unterstützt JSON, aber mit einem wichtigen Unterschied zu MySQL 8: MariaDB speichert JSON als LONGTEXT mit automatischer Gültigkeitsprüfung, nicht als binär-optimiertes Format. Das bedeutet, dass jede Lese-Operation das gesamte Dokument parsen muss – kein direkter Pfad-Zugriff auf Binärdaten wie in MySQL 8.

-- MariaDB JSON: intern LONGTEXT, aber mit Validierung
-- Die meisten Funktionen sind kompatibel mit MySQL 8

-- In MariaDB verfügbar (ab 10.2+):
SELECT JSON_EXTRACT('{"a": 1}', '$.a');   -- 1
SELECT JSON_VALUE('{"a": 1}', '$.a');     -- 1 (MariaDB-spezifisch, ohne Anführungszeichen)

-- JSON_TABLE in MariaDB erst ab 10.6+
-- In früheren Versionen: JSON_ARRAYAGG und manuelle Iteration nötig

-- MariaDB-spezifische Funktion (kein MySQL-Äquivalent):
SELECT JSON_DETAILED('{"a":1,"b":[1,2,3]}');
-- Gibt formatiertes JSON aus (für Debugging)

-- Unterschied bei ->> Operator (MySQL 8 native, MariaDB ab 10.5.0+)
-- Für maximale Kompatibilität besser JSON_UNQUOTE(JSON_EXTRACT(...)) verwenden

-- Prüfen ob Datenbank MySQL oder MariaDB ist:
SELECT VERSION();
-- MySQL: 8.0.x
-- MariaDB: 10.x.y-MariaDB
    

9. Zusammenfassung

JSON MySQL-Spalten sind ein mächtiges Werkzeug für variable Datenstrukturen, haben aber klare Grenzen: Ohne generierte Spalten und Indizes sind JSON-Filterungen extrem langsam. MySQL 8 und MariaDB unterscheiden sich im internen Format. JSON_TABLE normalisiert JSON on-the-fly für relationale Abfragen. Der wichtigste Grundsatz: JSON ist kein Ersatz für normalisiertes Schema, sondern eine Ergänzung für wirklich variable Daten.

10. FAQ: JSON MySQL und MariaDB