- Kategorien:
Tabellenfunktionen, Funktionen für semistrukturierte und strukturierte Daten (Extraktion)
FLATTEN¶
Vereinfacht bzw. löst zusammengesetzte Werte in mehrere Zeilen auf.
FLATTEN ist eine Tabellenfunktion, die eine VARIANT-, OBJECT- oder ARRAY-Spalte nutzt, um eine laterale Ansicht zu erzeugen (d. h. eine Inline-Ansicht, die Korrelation bezüglich anderer Tabellen enthält, die ihr in der FROM-Klausel vorangehen).
FLATTEN kann verwendet werden, um semistrukturierte Daten in eine relationale Darstellung zu konvertieren.
Syntax¶
FLATTEN( INPUT => <expr> [ , PATH => <constant_expr> ]
[ , OUTER => TRUE | FALSE ]
[ , RECURSIVE => TRUE | FALSE ]
[ , MODE => 'OBJECT' | 'ARRAY' | 'BOTH' ] )
Argumente¶
Benötigt:
INPUT => exprDer Ausdruck, der in Zeilen aufgehoben werden soll. Der Ausdruck muss den Datentyp VARIANT, OBJECT oder ARRAY aufweisen.
Optional:
PATH => constant_exprDer Pfad zu dem Element in einer VARIANT-Datenstruktur, das vereinfacht werden soll. Kann eine Zeichenfolge der Länge null (d. h ein leerer Pfad) sein, wenn das äußerste Element vereinfacht werden soll.
Standard: Zeichenfolge der Länge null (d. h. leerer Pfad)
OUTER => TRUE | FALSEBei
FALSEwerden alle Eingabezeilen, die sich nicht erweitern lassen, entweder weil auf sie im Pfad nicht zugegriffen werden kann oder weil sie keine Felder oder Einträge enthalten, in der Ausgabe vollständig weggelassen.Bei
TRUEwird für Null-Zeilen-Erweiterungen genau eine Zeile generiert (mit NULL in den Spalten KEY, INDEX und VALUE).
Standard:
FALSEBemerkung
Eine Null-Zeilen-Erweiterung eines leeren Verbunds zeigt in der THIS-Ausgabespalte NULL an. Dadurch wird vom Versuch unterschieden, eine nicht vorhandene oder falsche Art von Verbund zu erweitern.
RECURSIVE => TRUE | FALSEBei
FALSEwird nur das vonPATHreferenzierte Element erweitert.Bei
TRUEwird die Erweiterung für alle Unterelemente rekursiv ausgeführt.
Standard:
FALSEMODE => 'OBJECT' | 'ARRAY' | 'BOTH'Gibt an, ob nur Objekte, Arrays oder beide vereinfacht werden sollen.
Standard:
BOTH
Ausgabe¶
Die zurückgegebenen Zeilen bestehen aus einer festen Menge von Spalten:
+-----+------+------+-------+-------+------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+------+------+-------+-------+------|
- SEQ:
Eine eindeutige Sequenznummer, die dem Eingabedatensatz zugeordnet ist; es ist nicht garantiert, dass die Sequenz lückenlos ist oder auf eine bestimmte Weise angeordnet wird.
- KEY:
Bei Karten oder Objekten enthält diese Spalte den Schlüssel für den aufgelösten Wert.
- PATH:
Der Pfad zu dem Element in einer Datenstruktur, das vereinfacht werden soll.
- INDEX:
Der Index des Elements, wenn es sich um ein Array handelt; sonst NULL.
- VALUE:
Der Wert des Elements des vereinfachten Arrays/Objekts.
- THIS:
Das Element, das vereinfacht wird (nützlich für das rekursive Vereinfachen).
Bemerkung
Die Spalten der ursprünglichen (korrelierten) Tabelle, die als Datenquelle für FLATTEN verwendet wurde, sind ebenfalls aufrufbar. Wenn eine einzelne Zeile der ursprünglichen Tabelle in der vereinfachten Ansicht zu mehreren Zeilen führt, werden die Werte in dieser Eingabezeile entsprechend der Zahl der von FLATTEN erzeugten Zeilen repliziert.
Nutzungshinweise¶
Leitlinien zur Verwendung dieser Funktion mit strukturierten Typen finden Sie unter Verwenden der FLATTEN-Funktion mit strukturierten Typen.
Beispiele¶
Ein einfaches Beispiel für die Vereinfachung eines Datensatzes (beachten Sie, dass das mittlere Element des Arrays fehlt):
SELECT * FROM TABLE(FLATTEN(input => parse_json('[1, ,77]'))) f; +-----+------+------+-------+-------+------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+------+------+-------+-------+------| | 1 | NULL | [0] | 0 | 1 | [ | | | | | | | 1, | | | | | | | , | | | | | | | 77 | | | | | | | ] | | 1 | NULL | [2] | 2 | 77 | [ | | | | | | | 1, | | | | | | | , | | | | | | | 77 | | | | | | | ] | +-----+------+------+-------+-------+------+
Beispiele für die Wirkung des Parameters PATH:
SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88]}'), outer => true)) f; +-----+-----+------+-------+-------+-----------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+-----+------+-------+-------+-----------| | 1 | a | a | NULL | 1 | { | | | | | | | "a": 1, | | | | | | | "b": [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ] | | | | | | | } | | 1 | b | b | NULL | [ | { | | | | | | 77, | "a": 1, | | | | | | 88 | "b": [ | | | | | | ] | 77, | | | | | | | 88 | | | | | | | ] | | | | | | | } | +-----+-----+------+-------+-------+-----------+ SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88]}'), path => 'b')) f; +-----+------+------+-------+-------+-------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+------+------+-------+-------+-------| | 1 | NULL | b[0] | 0 | 77 | [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ] | | 1 | NULL | b[1] | 1 | 88 | [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ] | +-----+------+------+-------+-------+-------+
Beispiel für die Wirkung des Parameters OUTER:
SELECT * FROM TABLE(FLATTEN(input => parse_json('[]'))) f; +-----+-----+------+-------+-------+------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+-----+------+-------+-------+------| +-----+-----+------+-------+-------+------+ SELECT * FROM TABLE(FLATTEN(input => parse_json('[]'), outer => true)) f; +-----+------+------+-------+-------+------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+------+------+-------+-------+------| | 1 | NULL | | NULL | NULL | [] | +-----+------+------+-------+-------+------+
Beispiel für die Wirkung des Parameters RECURSIVE:
SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88], "c": {"d":"X"}}'))) f; +-----+-----+------+-------+------------+--------------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+-----+------+-------+------------+--------------| | 1 | a | a | NULL | 1 | { | | | | | | | "a": 1, | | | | | | | "b": [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | | 1 | b | b | NULL | [ | { | | | | | | 77, | "a": 1, | | | | | | 88 | "b": [ | | | | | | ] | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | | 1 | c | c | NULL | { | { | | | | | | "d": "X" | "a": 1, | | | | | | } | "b": [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | +-----+-----+------+-------+------------+--------------+ SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88], "c": {"d":"X"}}'), recursive => true )) f; +-----+------+------+-------+------------+--------------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+------+------+-------+------------+--------------| | 1 | a | a | NULL | 1 | { | | | | | | | "a": 1, | | | | | | | "b": [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | | 1 | b | b | NULL | [ | { | | | | | | 77, | "a": 1, | | | | | | 88 | "b": [ | | | | | | ] | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | | 1 | NULL | b[0] | 0 | 77 | [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ] | | 1 | NULL | b[1] | 1 | 88 | [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ] | | 1 | c | c | NULL | { | { | | | | | | "d": "X" | "a": 1, | | | | | | } | "b": [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | | 1 | d | c.d | NULL | "X" | { | | | | | | | "d": "X" | | | | | | | } | +-----+------+------+-------+------------+--------------+
Beispiel für die Wirkung des Parameters MODE:
SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88], "c": {"d":"X"}}'), recursive => true, mode => 'object' )) f; +-----+-----+------+-------+------------+--------------+ | SEQ | KEY | PATH | INDEX | VALUE | THIS | |-----+-----+------+-------+------------+--------------| | 1 | a | a | NULL | 1 | { | | | | | | | "a": 1, | | | | | | | "b": [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | | 1 | b | b | NULL | [ | { | | | | | | 77, | "a": 1, | | | | | | 88 | "b": [ | | | | | | ] | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | | 1 | c | c | NULL | { | { | | | | | | "d": "X" | "a": 1, | | | | | | } | "b": [ | | | | | | | 77, | | | | | | | 88 | | | | | | | ], | | | | | | | "c": { | | | | | | | "d": "X" | | | | | | | } | | | | | | | } | | 1 | d | c.d | NULL | "X" | { | | | | | | | "d": "X" | | | | | | | } | +-----+-----+------+-------+------------+--------------+
Ein praktisches Beispiel, bei dem ein in ein anderes Array geschachteltes Array aufgelöst wird:
create or replace table persons as select column1 as id, parse_json(column2) as c from values (12712555, '{ name: { first: "John", last: "Smith"}, contact: [ { business:[ { type: "phone", content:"555-1234" }, { type: "email", content:"j.smith@company.com" } ] } ] }'), (98127771, '{ name: { first: "Jane", last: "Doe"}, contact: [ { business:[ { type: "phone", content:"555-1236" }, { type: "email", content:"j.doe@company.com" } ] } ] }') v; -- Note the multiple instances of LATERAL FLATTEN in the FROM clause of the following query. -- Each LATERAL view is based on the previous one to refer to elements in -- multiple levels of arrays. SELECT id as "ID", f.value AS "Contact", f1.value:type AS "Type", f1.value:content AS "Details" FROM persons p, lateral flatten(input => p.c, path => 'contact') f, lateral flatten(input => f.value:business) f1; +----------+-----------------------------------------+---------+-----------------------+ | ID | Contact | Type | Details | |----------+-----------------------------------------+---------+-----------------------| | 12712555 | { | "phone" | "555-1234" | | | "business": [ | | | | | { | | | | | "content": "555-1234", | | | | | "type": "phone" | | | | | }, | | | | | { | | | | | "content": "j.smith@company.com", | | | | | "type": "email" | | | | | } | | | | | ] | | | | | } | | | | 12712555 | { | "email" | "j.smith@company.com" | | | "business": [ | | | | | { | | | | | "content": "555-1234", | | | | | "type": "phone" | | | | | }, | | | | | { | | | | | "content": "j.smith@company.com", | | | | | "type": "email" | | | | | } | | | | | ] | | | | | } | | | | 98127771 | { | "phone" | "555-1236" | | | "business": [ | | | | | { | | | | | "content": "555-1236", | | | | | "type": "phone" | | | | | }, | | | | | { | | | | | "content": "j.doe@company.com", | | | | | "type": "email" | | | | | } | | | | | ] | | | | | } | | | | 98127771 | { | "email" | "j.doe@company.com" | | | "business": [ | | | | | { | | | | | "content": "555-1236", | | | | | "type": "phone" | | | | | }, | | | | | { | | | | | "content": "j.doe@company.com", | | | | | "type": "email" | | | | | } | | | | | ] | | | | | } | | | +----------+-----------------------------------------+---------+-----------------------+