JSON in SQLite Speichern und Abfragen: Beispiele und Best Practices

JSON, oder JavaScript Object Notation, ist ein Format zum Speichern und Austauschen von Daten. Es ist ein leichtgewichtiges, textbasiertes und menschenlesbares Format, das für Maschinen einfach zu analysieren und zu generieren ist. In SQLite kann JSON auf verschiedene Weise gespeichert und abgefragt werden.

Verwendung der JSON1-Erweiterung

Eine Möglichkeit, JSON in SQLite zu speichern, ist die Verwendung der JSON1-Erweiterung, die Funktionen zur Kodierung und Dekodierung von JSON bereitstellt. Um die JSON1-Erweiterung zu verwenden, müssen Sie sie zunächst aktivieren, indem Sie den folgenden Befehl ausführen:

SELECT load_extension('/path/to/json1/extension');

Sobald die Erweiterung aktiviert ist, können Sie eine Tabelle erstellen, die eine Spalte vom Typ JSON hat, mit der folgenden SQL-Anweisung:

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

JSON-Daten Einfügen

Um einen JSON-Wert in diese Tabelle einzufügen, können Sie die json_encode-Funktion verwenden, die von der JSON1-Erweiterung bereitgestellt wird, so:

INSERT INTO users (id, data) VALUES (1, json_encode({
  "name": "Alice",
  "age": 25,
  "email": "alice@example.com"
}));

JSON-Daten Abfragen

Um diese Tabelle abzufragen, können Sie die json_extract-Funktion verwenden, die es Ihnen ermöglicht, Werte aus der JSON-Spalte zu extrahieren, indem Sie einen JSON-Pfad angeben. Zum Beispiel, um die E-Mail-Adresse des Benutzers mit der id 1 zu erhalten, können Sie die folgende Abfrage verwenden:

SELECT json_extract(data, '$.email') AS email
FROM users
WHERE id = 1;

JSON in BLOB Speichern

Eine andere Möglichkeit, JSON in SQLite zu speichern und abzufragen, ist die Verwendung des BLOB-Typs. In diesem Fall können Sie eine Tabelle mit einer BLOB-Spalte so erstellen:

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

JSON in eine BLOB-Spalte Einfügen

Um einen JSON-Wert in diese Tabelle einzufügen, können Sie die json_encode-Funktion verwenden, um den Wert als String zu kodieren, und dann die hex-Funktion verwenden, um ihn in ein BLOB zu konvertieren, so:

INSERT INTO users (id, data) VALUES (1, hex(json_encode({
  "name": "Alice",
  "age": 25,
  "email": "alice@example.com"
})));

JSON in einer BLOB-Spalte Abfragen

Um diese Tabelle abzufragen, können Sie die JSON_EXTRACT-Funktion der JSON1-Erweiterung verwenden, aber Sie müssen den BLOB-Wert mit der unhex-Funktion zurück in einen String konvertieren, so:

SELECT json_extract(unhex(data), '$.email') AS email
FROM users
WHERE id = 1;

Zusammenfassung

Zusammenfassend bietet SQLite zwei Möglichkeiten, JSON-Werte zu speichern und abzufragen: mit der JSON1-Erweiterung und mit dem BLOB-Typ. Beide Ansätze haben ihre eigenen Vor- und Nachteile, und welchen Sie wählen, hängt von Ihren spezifischen Bedürfnissen ab.

Wenn Sie JSON-Daten in SQL INSERT-Anweisungen konvertieren müssen, schauen Sie sich unser kostenloses JSON zu SQL Konverter Tool an.