JSON-Überblick

DuckDB unterstützt SQL-Funktionen zum Lesen von Werten aus vorhandenem JSON und zum Erzeugen neuer JSON-Daten. JSON wird über die Erweiterung json unterstützt, die in den meisten DuckDB-Distributionen enthalten ist und beim ersten Einsatz automatisch geladen wird. Wenn Sie sie manuell installieren oder laden möchten, siehe die Seite „Installieren und Laden“.

Über JSON

JSON ist ein offenes Datei- und Datenaustauschformat, das menschenlesbaren Text verwendet, um Datenobjekte aus Attribut-Wert-Paaren und Arrays (oder anderen serialisierbaren Werten) zu speichern und zu übertragen. Für tabellarische Daten ist es kein besonders effizientes Format, wird aber sehr häufig verwendet, vor allem als Austauschformat.

JSONPath- und JSON-Pointer-Syntax

DuckDB implementiert mehrere Schnittstellen zur JSON-Extraktion: JSONPath und JSON Pointer. Beide funktionieren mit dem Pfeiloperator (->) und dem Funktionsaufruf json_extract.

Beachten Sie, dass DuckDB in JSONPath nur Lookups unterstützt, d. h. das Extrahieren von Feldern mit .<key> oder von Array-Elementen mit [<index>]. Arrays können vom Ende her indiziert werden, und beide Ansätze unterstützen den Platzhalter *. DuckDB unterstützt nicht die vollständige JSONPath-Syntax, weil für weitere Transformationen SQL zur Verfügung steht.

Am besten wählen Sie entweder die JSONPath- oder die JSON-Pointer-Syntax und verwenden sie durchgängig in Ihrer Anwendung.

Indizierung

Warning DuckDB verwendet gemäß den Konventionen von PostgreSQL 1-basierte Indizierung für die Datentypen ARRAY und LIST, aber 0-basierte Indizierung für den JSON-Datentyp.

Beispiele

JSON laden

JSON-Datei von der Festplatte lesen, Optionen automatisch ableiten:

SELECT * FROM 'todos.json';

Die Funktion read_json mit eigenen Optionen verwenden:

SELECT *
FROM read_json('todos.json',
               format = 'array',
               columns = {userId: 'UBIGINT',
                          id: 'UBIGINT',
                          title: 'VARCHAR',
                          completed: 'BOOLEAN'});

JSON-Datei von stdin lesen, Optionen automatisch ableiten:

cat data/json/todos.json | duckdb -c "SELECT * FROM read_json('/dev/stdin')"

JSON-Datei in eine Tabelle einlesen:

CREATE TABLE todos (userId UBIGINT, id UBIGINT, title VARCHAR, completed BOOLEAN);
COPY todos FROM 'todos.json' (AUTO_DETECT true);

Alternativ eine Tabelle ohne manuelle Schemaangabe mit einer CREATE TABLE ... AS SELECT-Klausel erzeugen:

CREATE TABLE todos AS
    SELECT * FROM 'todos.json';

Seit DuckDB v1.3.0 liefert der JSON-Reader die virtuelle Spalte filename:

SELECT filename, *
FROM 'todos-*.json';

JSON schreiben

Abfrageergebnis in eine JSON-Datei schreiben:

COPY (SELECT * FROM todos) TO 'todos.json';

JSON-Datentyp

Tabelle mit einer Spalte für JSON-Daten anlegen und Daten einfügen:

CREATE TABLE example (j JSON);
INSERT INTO example VALUES
    ('{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }');

JSON-Daten abrufen

Wert des Schlüssels family abrufen:

SELECT j.family FROM example;

"anatidae"

Wert des Schlüssels family mit einem JSONPath-Ausdruck als JSON extrahieren:

SELECT j->'$.family' FROM example;

"anatidae"

Wert des Schlüssels family mit einem JSONPath-Ausdruck als VARCHAR extrahieren:

SELECT j->>'$.family' FROM example;

anatidae

Anführungszeichen für Sonderzeichen

Schlüssel in JSON-Objekten, die die Sonderzeichen [ und . enthalten, können durch Setzen in doppelte Anführungszeichen (") verwendet werden:

SELECT '{"d[u]._\"ck":42}'->'$."d[u]._\"ck"' AS v;