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 Korrelationen zu anderen 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' ] )
Copy

Argumente

Benötigt:

INPUT => expr

Der Ausdruck, der in Zeilen vereinfacht werden soll. Der Ausdruck muss den Datentyp VARIANT, OBJECT oder ARRAY aufweisen.

Optional:

PATH => constant_expr

Der 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 | FALSE
  • Bei FALSE werden 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 TRUE wird für Null-Zeilen-Erweiterungen genau eine Zeile generiert (mit NULL in den Spalten KEY, INDEX und VALUE).

Standard: FALSE

Bemerkung

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 | FALSE
  • Bei FALSE wird nur das von PATH referenzierte Element erweitert.

  • Bei TRUE wird die Erweiterung für alle Unterelemente rekursiv ausgeführt.

Standard: FALSE

MODE => '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

Informationen zur Verwendung dieser Funktion mit strukturierten Typen finden Sie unter Verwenden der Funktion FLATTEN mit Werten von strukturierten Typen.

Beispiele

Siehe auch Beispiel: Verwendung einer Lateral Join mit der Tabellenfunktion FLATTEN und Verwenden von FLATTEN zum Filtern der Ergebnisse in einer WHERE-Klausel.

Das folgende einfache Beispiel vereinfacht einen Datensatz (beachten Sie, dass das mittlere Element des Arrays fehlt):

SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('[1, ,77]'))) f;
Copy
+-----+------+------+-------+-------+------+
| SEQ |  KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+-------+------|
|   1 | NULL | [0]  |     0 |     1 | [    |
|     |      |      |       |       |   1, |
|     |      |      |       |       |   ,  |
|     |      |      |       |       |   77 |
|     |      |      |       |       | ]    |
|   1 | NULL | [2]  |     2 |    77 | [    |
|     |      |      |       |       |   1, |
|     |      |      |       |       |   ,  |
|     |      |      |       |       |   77 |
|     |      |      |       |       | ]    |
+-----+------+------+-------+-------+------+

Die nächsten beiden Abfragen zeigen, wie sich der Parameter PATH auswirkt:

SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88]}'), OUTER => TRUE)) f;
Copy
+-----+-----+------+-------+-------+-----------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS      |
|-----+-----+------+-------+-------+-----------|
|     |     |      |       |       |   "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;
Copy
+-----+------+------+-------+-------+-------+
| SEQ |  KEY | PATH | INDEX | VALUE | THIS  |
|-----+------+------+-------+-------+-------|
|   1 | NULL | b[0] |     0 |    77 | [     |
|     |      |      |       |       |   77, |
|     |      |      |       |       |   88  |
|     |      |      |       |       | ]     |
|   1 | NULL | b[1] |     1 |    88 | [     |
|     |      |      |       |       |   77, |
|     |      |      |       |       |   88  |
|     |      |      |       |       | ]     |
+-----+------+------+-------+-------+-------+

Die nächsten beiden Abfragen zeigen, wie sich der Parameter OUTER auswirkt:

SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('[]'))) f;
Copy
+-----+-----+------+-------+-------+------+
| SEQ | KEY | PATH | INDEX | VALUE | THIS |
|-----+-----+------+-------+-------+------|
+-----+-----+------+-------+-------+------+
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('[]'), OUTER => TRUE)) f;
Copy
+-----+------+------+-------+-------+------+
| SEQ |  KEY | PATH | INDEX | VALUE | THIS |
|-----+------+------+-------+-------+------|
|   1 | NULL |      |  NULL |  NULL | []   |
+-----+------+------+-------+-------+------+

Die nächsten beiden Abfragen zeigen, wie sich der Parameter RECURSIVE auswirkt:

SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88], "c": {"d":"X"}}'))) f;
Copy
+-----+-----+------+-------+------------+--------------+
| 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;
Copy
+-----+------+------+-------+------------+--------------+
| 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"   |
|     |      |      |       |            | }            |
+-----+------+------+-------+------------+--------------+

Das folgende Beispiel zeigt die Auswirkungen des Parameters MODE:

SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88], "c": {"d":"X"}}'),
                            RECURSIVE => TRUE, MODE => 'OBJECT' )) f;
Copy
+-----+-----+------+-------+------------+--------------+
| 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"   |
|     |     |      |       |            | }            |
+-----+-----+------+-------+------------+--------------+

Das folgende Beispiel löst ein Array auf, das in einem anderen Array verschachtelt ist. Erstellen Sie die folgende Tabelle:

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@example.com" } ] } ] }'),
      (98127771,
       '{ name:  { first: "Jane", last: "Doe"},
         contact: [
         { business:[
           { type: "phone", content:"555-1236" },
           { type: "email", content:"j.doe@example.com" } ] } ] }') v;
Copy

Beachten Sie die mehreren Instanzen von LATERAL FLATTEN in der FROM-Klausel der folgenden Abfrage. Jede LATERAL-Ansicht basiert auf der vorherigen, um auf Elemente in mehreren Ebenen von Arrays zu verweisen.

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;
Copy
+----------+-----------------------------------------+---------+-----------------------+
|       ID | Contact                                 | Type    | Details               |
|----------+-----------------------------------------+---------+-----------------------|
| 12712555 | {                                       | "phone" | "555-1234"            |
|          |   "business": [                         |         |                       |
|          |     {                                   |         |                       |
|          |       "content": "555-1234",            |         |                       |
|          |       "type": "phone"                   |         |                       |
|          |     },                                  |         |                       |
|          |     {                                   |         |                       |
|          |       "content": "j.smith@example.com", |         |                       |
|          |       "type": "email"                   |         |                       |
|          |     }                                   |         |                       |
|          |   ]                                     |         |                       |
|          | }                                       |         |                       |
| 12712555 | {                                       | "email" | "j.smith@example.com" |
|          |   "business": [                         |         |                       |
|          |     {                                   |         |                       |
|          |       "content": "555-1234",            |         |                       |
|          |       "type": "phone"                   |         |                       |
|          |     },                                  |         |                       |
|          |     {                                   |         |                       |
|          |       "content": "j.smith@example.com", |         |                       |
|          |       "type": "email"                   |         |                       |
|          |     }                                   |         |                       |
|          |   ]                                     |         |                       |
|          | }                                       |         |                       |
| 98127771 | {                                       | "phone" | "555-1236"            |
|          |   "business": [                         |         |                       |
|          |     {                                   |         |                       |
|          |       "content": "555-1236",            |         |                       |
|          |       "type": "phone"                   |         |                       |
|          |     },                                  |         |                       |
|          |     {                                   |         |                       |
|          |       "content": "j.doe@example.com",   |         |                       |
|          |       "type": "email"                   |         |                       |
|          |     }                                   |         |                       |
|          |   ]                                     |         |                       |
|          | }                                       |         |                       |
| 98127771 | {                                       | "email" | "j.doe@example.com"   |
|          |   "business": [                         |         |                       |
|          |     {                                   |         |                       |
|          |       "content": "555-1236",            |         |                       |
|          |       "type": "phone"                   |         |                       |
|          |     },                                  |         |                       |
|          |     {                                   |         |                       |
|          |       "content": "j.doe@example.com",   |         |                       |
|          |       "type": "email"                   |         |                       |
|          |     }                                   |         |                       |
|          |   ]                                     |         |                       |
|          | }                                       |         |                       |
+----------+-----------------------------------------+---------+-----------------------+