JSON in SQLite abfragen – json_extract und der ->>-Operator

Nicht alles verdient eine eigene Spalte. Einstellungen, ein Zustandsschnappschuss, die Antwort einer fremden API — so etwas landet bei mir oft als JSON-Text in einer Spalte. Bequem beim Schreiben, unbequem beim Suchen. Zumindest, bis man weiß, dass SQLite JSON selbst versteht.

CREATE TABLE spielstaende (
    id      INTEGER PRIMARY KEY,
    spieler TEXT NOT NULL,
    daten   TEXT NOT NULL   -- JSON
);

Einzelne Werte herausholen

SELECT
    spieler,
    daten ->> '$.level'          AS level,
    daten ->> '$.name'           AS figur,
    daten ->> '$.inventar[0]'    AS erstes_teil
FROM spielstaende;

Der Pfad beginnt mit $ für die Wurzel, danach Punkt für Objektfelder und eckige Klammern für Array-Positionen. Verschachtelt geht genauso: '$.einstellungen.ton.lautstaerke'.

->> oder json_extract?

->> ist die Kurzform für json_extract und liefert einen einfachen Wert — Text, Zahl, oder NULL. Das ist fast immer das, was man will.

Der einfache Pfeil -> dagegen liefert wieder JSON. daten -> '$.name' ergibt "Kel" mit Anführungszeichen. Praktisch, wenn man ein Teilobjekt weiterreichen will; verwirrend, wenn man eigentlich nur den Namen wollte.

Merkhilfe: zwei Pfeile = zwei Anführungszeichen weniger.

Danach filtern und sortieren

SELECT spieler, daten ->> '$.level' AS level
FROM spielstaende
WHERE CAST(daten ->> '$.level' AS INTEGER) >= 10
ORDER BY level DESC;

Das CAST ist wichtig: Was aus dem JSON kommt, ist erstmal Text — und '9' > '10' stimmt bei Text nicht.

Ein Array auffalten

Richtig interessant wird es mit json_each, das ein Array in Zeilen verwandelt:

SELECT s.spieler, j.value AS gegenstand
FROM spielstaende s, json_each(s.daten, '$.inventar') j
WHERE j.value = 'schlüssel';

Damit lassen sich JSON-Listen ganz normal joinen, gruppieren und zählen — als wären sie eine eigene Tabelle.

Und die Geschwindigkeit?

Hier liegt der Haken: Ohne Index muss SQLite für jede Zeile das JSON parsen. Bei zehntausend Zeilen ist das spürbar. Die Lösung ist ein Index auf genau dem Ausdruck, nach dem du filterst:

CREATE INDEX idx_level ON spielstaende (CAST(daten ->> '$.level' AS INTEGER));

Danach ist die obige Abfrage schnell — aber nur, wenn der Ausdruck in der Abfrage exakt so aussieht wie im Index. Ob es geklappt hat, sagt dir EXPLAIN QUERY PLAN.

Wann JSON, wann eine Spalte?

Meine Regel: Wonach ich regelmäßig filtere oder sortiere, bekommt eine eigene Spalte. Alles andere darf im JSON bleiben.

Ein JSON-Feld ist praktisch, weil es ohne Schema-Änderung mitwächst — und genau das ist auch seine Schwäche: Kein Fremdschlüssel, keine NOT NULL-Prüfung, kein Tippfehlerschutz. Wenn du merkst, dass du für ein Feld einen Index brauchst, ist das der Moment, es herauszuziehen.

Du hast eine Abfrage auf JSON-Daten, die spürbar langsam ist? Meistens fehlt genau dieser Ausdrucksindex. Schreib mir.

Quellen

• SQLite: JSON Functions And Operators
• SQLite: Indexes On Expressions
• SQLite: json_each und json_tree