Entschachteln

Entschachteln ist eine Operation, die Werte zusammengesetzter Typen in ihre Bestandteile zerlegt. Werte der Typen LIST und STRUCT können mit der Funktion unnest() entschachtelt werden.

• Das Entschachteln macht aus einem LIST-typisierten Wert eine Tabellenspalte: jedes Listenelement erzeugt eine Zeile, und jedes Element wird zu einem Spaltenwert.
• Das Entschachteln eines STRUCT-typisierten Werts erzeugt eine Spalte pro Mitglied. Der Mitgliedsschlüssel wird zum Spaltennamen, der Mitgliedswert zum Spaltenwert. Werte eines STRUCT-Typs können auch mit der Punkt-Stern-Kurzschreibweise (⟨struct⟩.*{:.language-sql .highlight}) entschachtelt werden, die Funktion unnest() bietet jedoch zusätzliche Funktionen.
• Bei einem [unbenannten Struct]({ % link /docs/current/sql/data_types/struct %}#creating-structs-with-the-row-function) ist die Entschachtelungsoperation dieselbe wie bei einem benannten STRUCT, der Spaltenname wird in diesem Fall jedoch anhand der (1-basierten) Ordinalposition des Mitglieds erzeugt und mit element vorangestellt. Die Punkt-Stern-Kurzschreibweise (⟨struct⟩.*{:.language-sql .highlight}) steht für unbenannte Structs nicht zur Verfügung: unbenannte Structs können nur mit der Funktion unnest() entschachtelt werden.

Aufruf von unnest()

Der zu entschachtelnde LIST- oder STRUCT-Wert wird immer als erstes – verpflichtendes – Argument an die Funktion unnest() übergeben. Die Funktion unnest() hat eine Reihe optionaler zusätzlicher Argumente, um das Verhalten beim rekursiven Entschachteln zu steuern.

Die Funktion unnest kann in der SELECT-Klausel aufgerufen werden, als wäre sie eine skalare Funktion. unnest darf nicht in anderen Kontexten aufgerufen werden, in denen man normalerweise skalare Funktionen aufrufen kann, etwa in den Klauseln WHERE, GROUP BY oder ORDER BY. Auf Werte des LIST-Typs angewendet, darf unnest() auch in Klauseln erscheinen, in denen man normalerweise eine Tabellenfunktion verwenden könnte.

Entschachteln von LIST-typisierten Werten

Um das Entschachteln von LIST-typisierten Werten vollständig zu verstehen, ist es hilfreich, zwischen der „Eingabezeile“, die den zu entschachtelnden LIST-typisierten Wert geliefert hat, und der/den durch die Entschachtelungsoperation erzeugten „Ausgabezeile(n)“ zu unterscheiden. Ein einzelner Aufruf von unnest() auf dem LIST-typisierten Wert verdoppelt die „Eingabezeile“ und füllt das dem unnest()-Aufruf entsprechende Feld mit dem Elementwert. Mit anderen Worten: Die ursprüngliche Zeile wird zu einer Wiederholungsgruppe für das aus dem LIST-typisierten Wert entschachtelte Element. Wird unnest() daher auf einer leeren Liste (oder einem NULL-Wert) aufgerufen, werden keine Elemente entschachtelt und keine „Ausgabezeilen“ erzeugt.

Mehrere Listen entschachteln

Mehrere LIST-typisierte Werte können in derselben SELECT-Klausel entschachtelt werden; für eine „Eingabezeile“ kann es daher mehrere aus einem unnest()-Aufruf resultierende Zeilenmengen geben, und jede kann ihre eigene Zeilenzahl haben. Jedes Ergebnis wird zu einer Spalte der Ausgabetabelle, die Werte werden nach Ordinalposition ausgerichtet, und Spalten werden mit NULL-Werten aufgefüllt, wenn ein bestimmtes Ergebnis weniger Elemente hat als eines der anderen Ergebnisse. In einem letzten Schritt werden die Spalten der Eingabezeile zu diesem Ergebnis hinzugefügt. Die Wiederholungsgruppe wird also nur einmal und für alle unnest()-Ergebnisse erzeugt, nicht erneut für jedes einzelne unnest()-Ergebnis.

Den Elementindex holen

Das Entschachteln eines Werts vom Typ LIST liefert nur die Elementwerte. Um auch ihre Indizes (die Subskripte) mitzuführen, können Sie das eingebaute Makro generate_subscripts() verwenden. Das Makro generate_subscripts nimmt als erstes Argument einen Wert vom Typ LIST.

unnest() als Tabellenfunktion

Da ein Aufruf von unnest auf einem Wert vom Typ LIST eine Zeilenmenge liefert, kann er auch als Tabellenfunktion behandelt werden. Das bedeutet, er darf in der FROM-Klausel oder einer CALL-Anweisung erscheinen.

Aufrufe von unnest() in einer FROM-Klausel oder CALL-Anweisung akzeptieren keine zusätzlichen Parameter und können daher nicht zum rekursiven Entschachteln verwendet werden.

Rekursives Entschachteln

Standardmäßig packt unnest() nur die äußersten Bestandteile des Werts des zusammengesetzten Typs aus. Zusätzliche Parameter können übergeben werden, damit die Entschachtelungsoperation auf die entschachtelten Mitgliedswerte angewendet wird, und auf deren entschachtelte Werte usw., rekursiv. Diese zusätzlichen Parameter sind:

• recursive: BOOLEAN, Standard: false. Übergeben Sie true, um unnest rekursiv weiter auf entschachtelte Mitgliedswerte anzuwenden. Bei explizitem false ist die Rekursion deaktiviert. In dem Fall werden andere zusätzliche Parameter wie max_depth und keep_parent_names faktisch ignoriert
• max_depth: UINT32, Standard: 1. Steuert, wie viele Rekursionsebenen höchstens angewendet werden. Werte größer als 1 implizieren Rekursion; in solchen Fällen muss recursive nicht explizit als true übergeben werden.
• keep_parent_names: BOOLEAN, Standard: false. Ob Spaltennamen aus den Schlüsseln aller Vorfahren-Mitglieder erzeugt werden. Dieses Argument gilt nur beim Entschachteln von STRUCT-Werten.

Beachten Sie, dass rekursives Entschachteln immer den Typ des äußersten unnest()-Aufrufs respektiert:

• Wird ein LIST-typisierter Wert übergeben, werden LIST-typisierte Elemente rekursiv entschachtelt, während STRUCT-typisierte Elemente nicht weiter ausgepackt werden.
• Wird ein STRUCT-typisierter Wert übergeben, werden STRUCT-typisierte Mitgliedswerte rekursiv entschachtelt, während LIST-typisierte Mitgliedswerte nicht weiter ausgepackt werden.

Beispiele

Entschachtelt eine Liste und erzeugt 3 Zeilen (1, 2, 3):

SELECT unnest([1, 2, 3]);

Entschachtelt ein Struct und erzeugt zwei Spalten (a, b):

SELECT unnest({'a': 42, 'b': 84});

Rekursives Entschachteln einer Liste von Structs:

SELECT unnest([{'a': 42, 'b': 84}, {'a': 100, 'b': NULL}], recursive := true);

Begrenzt die Tiefe des rekursiven Entschachtelns mit max_depth:

SELECT unnest([[[1, 2], [3, 4]], [[5, 6], [7, 8, 9], []], [[10, 11]]], max_depth := 2);

Listen entschachteln

Entschachtelt eine Liste und erzeugt 3 Zeilen (1, 2, 3):

SELECT unnest([1, 2, 3]);

Entschachtelt eine Liste und erzeugt 3 Zeilen ((1, 10), (2, 10), (3, 10)):

SELECT unnest([1, 2, 3]), 10;

Entschachtelt zwei Listen unterschiedlicher Größe und erzeugt 3 Zeilen ((1, 10), (2, 11), (3, NULL)):

SELECT unnest([1, 2, 3]), unnest([10, 11]);

Entschachtelt eine Listenspalte aus einer Unterabfrage:

SELECT unnest(l) + 10 FROM (VALUES ([1, 2, 3]), ([4, 5])) tbl(l);

Leeres Ergebnis:

SELECT unnest([]);

Leeres Ergebnis:

SELECT unnest(NULL);

unnest auf einer Liste erzeugt eine Zeile pro Listeneintrag. Gewöhnliche skalare Ausdrücke in derselben SELECT-Klausel werden für jede erzeugte Zeile wiederholt. Werden mehrere Listen in derselben SELECT-Klausel entschachtelt, werden die Listen nebeneinander entschachtelt. Ist eine Liste länger als die andere, wird die kürzere mit NULL-Werten aufgefüllt.

Leere und NULL-Listen entschachteln beide zu null Zeilen.

Structs entschachteln

Entschachtelt ein Struct und erzeugt zwei Spalten (a, b):

SELECT unnest({'a': 42, 'b': 84});

Entschachtelt ein Struct und erzeugt zwei Spalten (a, b):

SELECT unnest({'a': 42, 'b': {'x': 84}});

unnest auf einem Struct erzeugt eine Spalte pro Eintrag im Struct.

Rekursives Entschachteln

Entschachtelt eine Liste von Listen rekursiv und erzeugt 5 Zeilen (1, 2, 3, 4, 5):

SELECT unnest([[1, 2, 3], [4, 5]], recursive := true);

Entschachtelt eine Liste von Structs rekursiv und erzeugt zwei Zeilen mit zwei Spalten (a, b):

SELECT unnest([{'a': 42, 'b': 84}, {'a': 100, 'b': NULL}], recursive := true);

Entschachtelt ein Struct und erzeugt zwei Spalten (a, b):

SELECT unnest({'a': [1, 2, 3], 'b': 88}, recursive := true);

Ein Aufruf von unnest mit der Einstellung recursive entschachtelt Listen vollständig und anschließend Structs vollständig. Das kann nützlich sein, um Spalten vollständig zu flachen, die Listen in Listen oder Listen von Structs enthalten. Beachten Sie, dass Listen innerhalb von Structs nicht entschachtelt werden.

Die maximale Entschachtelungstiefe festlegen

Der Parameter max_depth erlaubt, die maximale Tiefe des rekursiven Entschachtelns zu begrenzen (das standardmäßig angenommen wird und nicht extra angegeben werden muss). Zum Beispiel ergibt das Entschachteln mit max_depth 2 Folgendes:

SELECT unnest([[[1, 2], [3, 4]], [[5, 6], [7, 8, 9], []], [[10, 11]]], max_depth := 2) AS x;

x
[1, 2]
[3, 4]
[5, 6]
[7, 8, 9]
[]
[10, 11]

Das Entschachteln mit max_depth 3 ergibt dagegen:

SELECT unnest([[[1, 2], [3, 4]], [[5, 6], [7, 8, 9], []], [[10, 11]]], max_depth := 3) AS x;

x
1
2
3
4
5
6
7
8
9
10
11

Positionen von Listeneinträgen mitführen

Um die Position jedes Eintrags in der ursprünglichen Liste mitzuführen, kann unnest mit generate_subscripts kombiniert werden:

SELECT unnest(l) AS x, generate_subscripts(l, 1) AS index
FROM (VALUES ([1, 2, 3]), ([4, 5])) tbl(l);

x	index
1	1
2	2
3	3
4	1
5	2

Spaltennamen beim rekursiven Entschachteln behalten

Der Parameter keep_parent_names kann verwendet werden, um die übergeordneten Spaltennamen beim rekursiven Entschachteln eines benannten Structs beizubehalten. Zum Beispiel ergibt das Entschachteln der folgenden Abfrage mit aktiviertem keep_parent_names:

SELECT unnest([{'a': 0, 'b': {'bb': {'bbb': 1}}}], recursive := true, keep_parent_names := true);

folgendes Ergebnis:

a	b.bb.bbb
0	1

In diesem Fall bleiben die Feldnamen erhalten und zeigen den Pfad zum innersten Wert. Das ist besonders nützlich bei komplexen verschachtelten Datenstrukturen, weil Struktur und Namenskonvention der Originaldaten erhalten bleiben. Der Parameter kann auch zusammen mit dem Parameter max_depth verwendet werden und erlaubt so eine präzisere Steuerung verschachtelter Strukturen.