Struct-Datentyp

Konzeptionell enthält eine STRUCT-Spalte eine geordnete Liste von Spalten, die „Einträge“ genannt werden. Die Einträge werden per Name über Zeichenketten referenziert. Dieses Dokument bezeichnet diese Eintragsnamen als Schlüssel. Jede Zeile in der STRUCT-Spalte muss dieselben Schlüssel haben. Die Namen der Struct-Einträge sind Teil des Schemas. Jede Zeile in einer STRUCT-Spalte muss dasselbe Layout haben. Die Namen der Struct-Einträge sind unabhängig von der Groß-/Kleinschreibung.

STRUCTs werden typischerweise verwendet, um mehrere Spalten in eine einzelne Spalte zu verschachteln; die verschachtelte Spalte kann einen beliebigen Typ haben, einschließlich anderer STRUCTs und LISTs.

STRUCTs ähneln dem ROW-Typ von PostgreSQL. Der wesentliche Unterschied ist, dass DuckDB-STRUCTs in jeder Zeile einer STRUCT-Spalte dieselben Schlüssel verlangen. Das erlaubt DuckDB deutlich bessere Leistung durch volle Nutzung der vektorisierten Ausführungsmaschine und erzwingt außerdem Typkonsistenz für bessere Korrektheit. DuckDB enthält eine Funktion row als besondere Möglichkeit, ein STRUCT zu erzeugen, hat aber keinen Datentyp ROW. Siehe ein Beispiel unten und die Dokumentation der STRUCT-Funktionen für Details.

Einen Vergleich der verschachtelten Datentypen finden Sie im Überblick zu den Datentypen.

Structs erstellen

Structs können mit der Funktion struct_pack(name := expr, ...), der äquivalenten Array-Notation {'name': expr, ...}, mit einer Zeilenvariable oder mit der Funktion row erzeugt werden.

Ein Struct mit der Funktion struct_pack erstellen. Beachten Sie das Fehlen einfacher Anführungszeichen um die Schlüssel und die Verwendung des Operators :=:

SELECT struct_pack(key1 := 'value1', key2 := 42) AS s;

Ein Struct mit der Array-Notation erstellen:

SELECT {'key1': 'value1', 'key2': 42} AS s;

Ein Struct mit einer Zeilenvariable erstellen:

SELECT d AS s FROM (SELECT 'value1' AS key1, 42 AS key2) d;

Ein Struct von Integern erstellen:

SELECT {'x': 1, 'y': 2, 'z': 3} AS s;

Ein Struct von Zeichenketten mit einem NULL-Wert erstellen:

SELECT {'yes': 'duck', 'maybe': 'goose', 'huh': NULL, 'no': 'heron'} AS s;

Ein Struct mit einem anderen Typ für jeden Schlüssel erstellen:

SELECT {'key1': 'string', 'key2': 1, 'key3': 12.345} AS s;

Ein Struct von Structs mit NULL-Werten erstellen:

SELECT {
        'birds': {'yes': 'duck', 'maybe': 'goose', 'huh': NULL, 'no': 'heron'},
        'aliens': NULL,
        'amphibians': {'yes': 'frog', 'maybe': 'salamander', 'huh': 'dragon', 'no': 'toad'}
    } AS s;

Felder von Structs hinzufügen oder aktualisieren

Um neue Felder hinzuzufügen oder vorhandene zu aktualisieren, können Sie struct_update verwenden:

SELECT struct_update({'a': 1, 'b': 2}, b := 3, c := 4) AS s;

Alternativ erlaubt struct_insert ebenfalls das Hinzufügen neuer Felder, aber nicht das Aktualisieren vorhandener.

Aus Structs lesen

Einen Wert aus einem Struct lesen Sie mit Punkt-Notation, Klammer-Notation oder über Struct-Funktionen wie struct_extract.

Verwenden Sie die Punkt-Notation, um den Wert an der Position eines Schlüssels zu lesen. In der folgenden Abfrage erzeugt die Unterabfrage eine Struct-Spalte a, die wir dann mit a.x abfragen.

SELECT a.x FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS a);

Enthält ein Schlüssel ein Leerzeichen, setzen Sie ihn einfach in doppelte Anführungszeichen (").

SELECT a."x space" FROM (SELECT {'x space': 1, 'y': 2, 'z': 3} AS a);

Die Klammer-Notation kann ebenfalls verwendet werden. Beachten Sie, dass hier einfache Anführungszeichen (') verwendet werden, weil das Ziel ist, einen bestimmten String-Schlüssel anzugeben, und innerhalb der Klammern nur konstante Ausdrücke verwendet werden dürfen (keine Ausdrücke):

SELECT a['x space'] FROM (SELECT {'x space': 1, 'y': 2, 'z': 3} AS a);

Die Funktion struct_extract ist ebenfalls äquivalent. Das liefert 1:

SELECT struct_extract({'x space': 1, 'y': 2, 'z': 3}, 'x space');

`unnest` / `STRUCT.*`

Statt einen einzelnen Schlüssel aus einem Struct zu lesen, kann die Sonderfunktion unnest verwendet werden, um alle Schlüssel eines Structs als separate Spalten zu lesen. Das ist besonders nützlich, wenn eine vorherige Operation ein Struct unbekannter Form erzeugt oder eine Abfrage beliebige potenzielle Struct-Schlüssel behandeln muss:

SELECT unnest(a)
FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS a);

x	y	z
1	2	3

Dasselbe kann mit der Stern-Notation (*) erreicht werden, die zusätzlich Änderungen der zurückgegebenen Spalten erlaubt:

SELECT a.* EXCLUDE ('y')
FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS a);

x	z
1	3

Warnung Die Stern-Notation ist derzeit auf Struct-Spalten der obersten Ebene und Nicht-Aggregat-Ausdrücke beschränkt.

Auswertungsreihenfolge der Punkt-Notation

Das Beziehen auf Structs mit Punkt-Notation kann mit dem Beziehen auf Schemas und Tabellen mehrdeutig sein. Im Allgemeinen sucht DuckDB zuerst nach Spalten, dann nach Struct-Schlüsseln innerhalb von Spalten. DuckDB löst Referenzen in diesen Reihenfolgen auf und verwendet die erste Übereinstimmung:

Keine Punkte

SELECT part1
FROM tbl;

part1 ist eine Spalte

Ein Punkt

SELECT part1.part2
FROM tbl;

part1 ist eine Tabelle, part2 ist eine Spalte
part1 ist eine Spalte, part2 ist eine Eigenschaft dieser Spalte

Zwei (oder mehr) Punkte

SELECT part1.part2.part3
FROM tbl;

part1 ist ein Schema, part2 ist eine Tabelle, part3 ist eine Spalte
part1 ist eine Tabelle, part2 ist eine Spalte, part3 ist eine Eigenschaft dieser Spalte
part1 ist eine Spalte, part2 ist eine Eigenschaft dieser Spalte, part3 ist eine Eigenschaft dieser Spalte

Zusätzliche Teile (z. B. .part4.part5 usw.) werden immer als Eigenschaften behandelt

Structs mit der Funktion `row` erstellen

Die Funktion row kann verwendet werden, um automatisch mehrere Spalten in eine einzelne Struct-Spalte umzuwandeln. Bei Verwendung von row sind die Schlüssel leere Zeichenketten, was das Einfügen in eine Tabelle mit einer Struct-Spalte erleichtert. Spalten können jedoch nicht mit der Funktion row initialisiert werden und müssen explizit benannt werden. Beispielsweise das Einfügen von Werten in eine Struct-Spalte mit der Funktion row:

CREATE TABLE t1 (s STRUCT(v VARCHAR, i INTEGER));
INSERT INTO t1 VALUES (row('a', 42));
SELECT * FROM t1;

Die Tabelle enthält einen einzelnen Eintrag:

{'v': a, 'i': 42}

Das Folgende liefert dasselbe Ergebnis wie oben:

CREATE TABLE t1 AS (
    SELECT row('a', 42)::STRUCT(v VARCHAR, i INTEGER)
);

Das Initialisieren einer Struct-Spalte mit der Funktion row schlägt fehl:

CREATE TABLE t2 AS SELECT row('a');

Invalid Input Error:
A table cannot be created from an unnamed struct

Beim Casten zwischen Structs müssen die Namen mindestens eines Feldes übereinstimmen. Daher schlägt die folgende Abfrage fehl:

SELECT a::STRUCT(y INTEGER) AS b
FROM
    (SELECT {'x': 42} AS a);

Binder Error:
STRUCT to STRUCT cast must have at least one matching member

Ein Workaround dafür ist, stattdessen struct_pack zu verwenden:

SELECT struct_pack(y := a.x) AS b
FROM
    (SELECT {'x': 42} AS a);

Die Funktion row kann verwendet werden, um unbenannte Structs zu liefern. Zum Beispiel:

SELECT row(x, x + 1, y) FROM (SELECT 1 AS x, 'a' AS y) AS s;

Das erzeugt (1, 2, a).

Wenn beim Erzeugen eines Structs mehrere Ausdrücke verwendet werden, ist die Funktion row optional. Die folgende Abfrage liefert dasselbe Ergebnis wie die vorherige:

SELECT (x, x + 1, y) AS s FROM (SELECT 1 AS x, 'a' AS y);

Vergleich und Ordnung

Der STRUCT-Typ kann mit allen Vergleichsoperatoren verglichen werden. Diese Vergleiche können in logischen Ausdrücken wie WHERE- und HAVING-Klauseln verwendet werden und liefern BOOLEAN-Werte.

Vergleiche erfolgen in lexikographischer Reihenfolge; einzelne Einträge werden wie üblich verglichen, außer dass NULL-Werte als größer als alle anderen Werte behandelt werden.

Konkret:

Wenn alle Werte von s1 und s2 gleich vergleichen, vergleichen s1 und s2 gleich.
andernfalls, wenn s1.value[i] < s2.value[i] OR s2.value[i] is NULL für den ersten Index i, bei dem s1.value[i] != s2.value[i], dann ist s1 kleiner als s2, und umgekehrt.

Structs unterschiedlicher Typen werden implizit in einen Struct-Typ mit der Vereinigung der beteiligten Schlüssel gecastet, gemäß den Regeln für Kombinations-Casting.

Die folgenden Abfragen liefern true:

SELECT {'k1': 0, 'k2': 0} < {'k1': 1, 'k2': 0};

SELECT {'k1': 'hello'} < {'k1': 'world'};

SELECT {'k1': 0, 'k2': 0} < {'k1': 0, 'k2': NULL};

SELECT {'k1': 0} < {'k2': 0};

SELECT  {'k1': 0, 'k2': 0} < {'k2': 0, 'k3': 0};

SELECT {'k1': 1, 'k2': 0} > {'k3': 0, 'k1': 0};

Die folgenden Abfragen liefern false:

SELECT {'k1': 1, 'k2': 0} < {'k1': 0, 'k2': 1};

SELECT {'k1': [0]} < {'k1': [0, 0]};

SELECT {'k1': 1} > {'k2': 0};

SELECT {'k1': 0, 'k2': 0} < {'k3': 0, 'k1': 1};

SELECT  {'k1': 1, 'k2': 0} > {'k2': 0, 'k3': 0};

Das Schema aktualisieren

Ab DuckDB v1.3.0 ist es möglich, das Sub-Schema von Structs mit der Klausel ALTER TABLE zu aktualisieren.

Um den Beispielen zu folgen, initialisieren Sie die Tabelle test wie folgt:

CREATE TABLE test (s STRUCT(i INTEGER, j INTEGER));
INSERT INTO test VALUES (ROW(1, 1)), (ROW(2, 2));

Ein Feld hinzufügen

Feld k INTEGER zum Struct s in der Tabelle test hinzufügen:

ALTER TABLE test ADD COLUMN s.k INTEGER;
FROM test;

┌─────────────────────────────────────────┐
│                    s                    │
│ struct(i integer, j integer, k integer) │
├─────────────────────────────────────────┤
│ {'i': 1, 'j': 1, 'k': NULL}             │
│ {'i': 2, 'j': 2, 'k': NULL}             │
└─────────────────────────────────────────┘

Ein Feld entfernen

Feld i aus dem Struct s in der Tabelle test entfernen:

ALTER TABLE test DROP COLUMN s.i;
FROM test;

┌──────────────────────────────┐
│              s               │
│ struct(j integer, k integer) │
├──────────────────────────────┤
│ {'j': 1, 'k': NULL}          │
│ {'j': 2, 'k': NULL}          │
└──────────────────────────────┘

Ein Feld umbenennen

Feld j des Structs s in der Tabelle test in v1 umbenennen:

ALTER TABLE test RENAME s.j TO v1;
FROM test;

┌───────────────────────────────┐
│               s               │
│ struct(v1 integer, k integer) │
├───────────────────────────────┤
│ {'v1': 1, 'k': NULL}          │
│ {'v1': 2, 'k': NULL}          │
└───────────────────────────────┘

Funktionen

Siehe Struct-Funktionen.