SQLite JSON: json_extract, json_set & json_each erklärt

SQLite hat keinen JSON-Typ – und das ist auch gut so

SQLite kennt keinen eigenen JSON-Spaltentyp. JSON landet einfach in einer normalen TEXT-Spalte, und eine Reihe eingebauter Funktionen – zusammengefasst als JSON1-Erweiterung – weiß, wie man darin liest, sucht und schreibt. JSON1 ist in jedem modernen SQLite-Build dabei, du musst also nichts nachinstallieren.

Die Denkweise dahinter: Speichere das Dokument als Text und greif mit Funktionen darauf zu, um reinzuschauen.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    data TEXT
);

INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "email": "boris@example.com", "tags": ["beta"]}');

SELECT id, data FROM users;

Zwei Zeilen, jede mit einem JSON-Dokument in einer ganz normalen Textspalte. Jetzt brauchen wir Möglichkeiten, an die Werte in diesen Dokumenten heranzukommen.

Felder auslesen mit json_extract und dem ->>-Operator

json_extract(spalte, pfad) holt einen Wert aus einem JSON-Dokument heraus. Der Pfad beginnt mit $ (der Wurzel) und nutzt .feldname für Objektschlüssel sowie [i] für Array-Indizes.

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "email": "boris@example.com", "tags": ["beta"]}');

SELECT
    json_extract(data, '$.name')     AS name,
    json_extract(data, '$.tags[0]')  AS first_tag
FROM users;

Wer ständig json_extract(data, '$.name') schreiben muss, hat irgendwann die Nase voll. Genau dafür gibt es in SQLite zwei praktische Operatoren:

• -> liefert einen JSON-codierten Wert zurück (Strings kommen also samt Anführungszeichen).
• ->> liefert einen reinen SQL-Wert (Text oder Zahl, ohne Anführungszeichen).

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "tags": ["admin", "beta"]}');

SELECT
    data -> '$.name'      AS name_json,
    data ->> '$.name'     AS name_text,
    data ->> '$.tags[0]'  AS first_tag
FROM users;

name_json liefert "Ada" zurück (immer noch JSON), name_text dagegen Ada. Nimm ->>, wenn du den Wert vergleichen oder anzeigen willst. Nimm ->, wenn das Ergebnis als Eingabe für eine weitere JSON-Funktion dient.

Nach JSON-Feldern filtern

Wer extrahieren kann, kann auch filtern. Der Ausdruck wandert einfach in die WHERE-Klausel wie jede andere Bedingung auch:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "active": 1}'),
    ('{"name": "Boris", "email": "boris@example.com", "active": 0}'),
    ('{"name": "Cleo", "email": "cleo@example.com", "active": 1}');

SELECT data ->> '$.name' AS name
FROM users
WHERE data ->> '$.active' = 1;

Das funktioniert zwar, wird aber bei größeren Tabellen schnell langsam – schließlich muss jede Zeile geparst werden, um das Prädikat auszuwerten. Wie du das mit einem Index in den Griff bekommst, sehen wir gleich.

JSON erzeugen mit json_object und json_array

Umgekehrt geht es genauso: Du kannst JSON direkt innerhalb einer Abfrage zusammenbauen:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
INSERT INTO users (name, email) VALUES
    ('Ada', 'ada@example.com'),
    ('Boris', 'boris@example.com');

SELECT json_object(
    'id', id,
    'name', name,
    'email', email
) AS user_json
FROM users;

json_object('k1', v1, 'k2', v2, ...) erzeugt ein Objekt, json_array(v1, v2, ...) ein Array. Praktisch, wenn du API-Antworten direkt in SQL zusammenbauen willst – und verschachteln lassen sie sich problemlos:

SELECT json_object(
    'user', json_object('name', 'Ada', 'id', 1),
    'roles', json_array('admin', 'beta')
) AS payload;

JSON aktualisieren: json_set, json_insert und json_replace

In SQLite gibt es drei eng verwandte Funktionen, mit denen du ein JSON-Dokument verändern und die neue Fassung zurückbekommen kannst:

• json_set(doc, path, value) — setzt den Wert am angegebenen Pfad. Existiert der Pfad nicht, wird er angelegt; existiert er bereits, wird er überschrieben.
• json_insert(doc, path, value) — fügt nur dann ein, wenn der Pfad noch nicht vorhanden ist.
• json_replace(doc, path, value) — aktualisiert nur, wenn der Pfad bereits existiert.

Wichtig: Diese Funktionen verändern das Dokument nicht direkt, sondern liefern eine neue Version zurück. Das Ergebnis schreibst du anschließend typischerweise mit UPDATE zurück in die Tabelle:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com"}');

UPDATE users
SET data = json_set(data, '$.email', 'ada@new.com', '$.verified', 1)
WHERE id = 1;

SELECT data FROM users;

json_set akzeptiert übrigens mehrere Pfad/Wert-Paare in einem einzigen Aufruf. Um einen Schlüssel zu entfernen, nimmst du json_remove(doc, path).

JSON-Arrays auflösen mit json_each

json_each ist eine Table-Valued Function: Du übergibst ihr ein JSON-Array (oder ein Objekt) und bekommst pro Element eine Zeile zurück. Damit wird aus "alle User mit dem Tag admin finden" – in reinem SQL eher umständlich – ein ganz normaler Join:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "tags": ["beta"]}'),
    ('{"name": "Cleo", "tags": ["admin"]}');

SELECT u.data ->> '$.name' AS name, t.value AS tag
FROM users u, json_each(u.data, '$.tags') t
WHERE t.value = 'admin';

Jede Zeile aus users wird mit den Elementen ihres tags-Arrays verknüpft. json_each liefert dabei nützliche Spalten wie key, value, type und fullkey. Das Pendant json_tree läuft rekursiv durch das gesamte Dokument und nimmt jeden verschachtelten Knoten mit – praktisch, wenn du Dokumente mit unbekannter Struktur durchsuchen willst.

Index auf JSON-Feld in SQLite anlegen

Die obige Abfrage WHERE data ->> '$.active' = 1 funktioniert zwar, aber SQLite muss jede Zeile parsen, um das Prädikat auszuwerten. Für Felder, die du häufig abfragst, lohnt sich ein Ausdrucksindex (Expression Index):

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);

CREATE INDEX idx_users_email
    ON users(json_extract(data, '$.email'));

INSERT INTO users (data) VALUES
    ('{"email": "ada@example.com"}'),
    ('{"email": "boris@example.com"}');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE json_extract(data, '$.email') = 'ada@example.com';

Der Index muss exakt denselben Ausdruck verwenden wie deine Query. Wenn du im Index json_extract(data, '$.email') schreibst, in der Query aber data ->> '$.email', greift der Index nicht – er liegt einfach ungenutzt herum. Also: eine Schreibweise wählen und konsequent dabei bleiben.

Für Felder, die du ständig abfragst, ist eine generierte Spalte (Generated Column) übersichtlicher:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    data TEXT,
    email TEXT GENERATED ALWAYS AS (data ->> '$.email') VIRTUAL
);

CREATE INDEX idx_users_email ON users(email);

INSERT INTO users (data) VALUES ('{"email": "ada@example.com"}');

SELECT email FROM users WHERE email = 'ada@example.com';

email sieht für alle, die Queries schreiben, aus wie eine ganz normale Spalte – bleibt aber automatisch synchron mit dem JSON.

JSON validieren in SQLite

json_valid(text) gibt 1 zurück, wenn sich der Text als JSON parsen lässt, sonst 0. Kombiniert mit einer CHECK-Bedingung kannst du fehlerhafte Daten direkt beim Schreiben blockieren:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    payload TEXT CHECK (json_valid(payload))
);

INSERT INTO events (payload) VALUES ('{"type": "click"}');
INSERT INTO events (payload) VALUES ('not json');

Beim ersten Insert klappt alles, der zweite scheitert mit einem Constraint-Fehler. Ohne diesen Check würde fehlerhaftes JSON unbemerkt in der Tabelle schlummern – bis irgendwann Monate später ein json_extract-Aufruf um die Ohren fliegt.

JSON vs. JSONB in SQLite

Seit SQLite 3.45 gibt es mit JSONB eine binäre Darstellung – dieselben Daten, aber bereits geparst und in einem kompakten Binärformat abgelegt, sodass die Funktionen sie nicht bei jedem Aufruf neu parsen müssen. Die Funktionsfamilie jsonb_* (jsonb_extract, jsonb_set, jsonb_object, ...) liefert JSONB statt Text zurück, und JSONB-Spalten lassen sich mit denselben Operatoren abfragen.

CREATE TABLE docs (id INTEGER PRIMARY KEY, body BLOB);

INSERT INTO docs (body) VALUES
    (jsonb('{"name": "Ada", "score": 99}'));

SELECT body ->> '$.name' AS name FROM docs;

Greife zu reinem JSON (als Text), wenn deine Dokumente in Dumps für Menschen lesbar bleiben und sich leicht inspizieren lassen sollen. JSONB lohnt sich dann, wenn eine Tabelle groß ist, häufig abgefragt wird und der Parsing-Overhead im Profiling tatsächlich auftaucht. Wechsle nicht reflexartig – die Lesbarkeit von Plain JSON ist beim Debuggen Gold wert.

Wann JSON in SQLite wirklich Sinn ergibt

JSON-Spalten spielen ihre Stärken aus, wenn:

• Die Struktur von Zeile zu Zeile variiert (z. B. Event-Payloads, Audit-Logs, Webhooks aus Integrationen).
• Du eine externe API-Antwort cachen und sie unverändert behalten willst.
• Ein Feld nur selten abgefragt und so gut wie nie zum Filtern genutzt wird.

Schlecht passen sie, wenn:

• Du JSON nur einsetzt, um dir einen sauberen Schema-Entwurf zu sparen. Wenn jede Zeile dieselben Felder hat, gehören diese in eigene Spalten.
• Du regelmäßig nach einem Wert filtern oder darauf joinen musst. Eine echte Spalte mit Index ist einem JSON-Pfad-Lookup immer überlegen.
• Du eigentlich Fremdschlüssel bräuchtest. JSON kennt keine relationale Integrität.

Der Sweet Spot ist die Kombination aus beidem: skalare Spalten für die Felder, die Queries und Constraints tragen, daneben eine JSON-Spalte für den Long Tail an variablen Daten.

Weiter geht's: Volltextsuche

JSON gibt dir Flexibilität auf der Speicherseite. Die nächste Seite widmet sich FTS5 – der Volltextsuch-Engine von SQLite. Damit bekommst du echte Textsuche mit Ranking und Highlighting, weit jenseits dessen, was LIKE leisten kann.