Verschachtelte Arrays glätten

Beim Arbeiten mit verschachtelten Arrays müssen Sie verschachtelte Array-Elemente oft zu einem einzelnen Array erweitern oder das Array in mehrere Zeilen erweitern.

Verwenden Sie die Funktion Flatten

Verwenden Sie die Funktion flatten, um die Elemente eines verschachtelten Arrays zu einem einzelnen Array mit Werten zu reduzieren. Diese Abfrage gibt eine Zeile für jedes Element im Array zurück.

SELECT flatten(ARRAY[ ARRAY[1,2], ARRAY[3,4] ]) AS items

Diese Abfrage gibt Folgendes zurück:

+-----------+
| items     |
+-----------+
| [1,2,3,4] |
+-----------+

Verwenden Sie CROSS JOIN und UNNEST

Um ein Array in mehrere Zeilen zu reduzieren, verwenden Sie CROSS JOIN in Verbindung mit dem UNNEST-Operator wie in diesem Beispiel:

WITH dataset AS (
  SELECT
    'engineering' as department,
    ARRAY['Sharon', 'John', 'Bob', 'Sally'] as users
)
SELECT department, names FROM dataset
CROSS JOIN UNNEST(users) as t(names)

Diese Abfrage gibt Folgendes zurück:

+----------------------+
| department  | names  |
+----------------------+
| engineering | Sharon |
+----------------------|
| engineering | John   |
+----------------------|
| engineering | Bob    |
+----------------------|
| engineering | Sally  |
+----------------------+

Um ein Array mit Schlüssel-Wert-Paaren zu reduzieren, verteilen Sie die ausgewählten Schlüssel wie in diesem Beispiel in Spalten:

WITH
dataset AS (
  SELECT
    'engineering' as department,
     ARRAY[
      MAP(ARRAY['first', 'last', 'age'],ARRAY['Bob', 'Smith', '40']),
      MAP(ARRAY['first', 'last', 'age'],ARRAY['Jane', 'Doe', '30']),
      MAP(ARRAY['first', 'last', 'age'],ARRAY['Billy', 'Smith', '8'])
     ] AS people
  )
SELECT names['first'] AS
 first_name,
 names['last'] AS last_name,
 department FROM dataset
CROSS JOIN UNNEST(people) AS t(names)

Diese Abfrage gibt Folgendes zurück:

+--------------------------------------+
| first_name | last_name | department  |
+--------------------------------------+
| Bob        | Smith     | engineering |
| Jane       | Doe       | engineering |
| Billy      | Smith     | engineering |
+--------------------------------------+

Wählen Sie aus einer Liste mit Mitarbeitern den Mitarbeiter mit den höchsten kombinierten Werten aus. UNNEST kann in der Klausel FROM ohne vorangestelltes CROSS JOIN, da es der Standard-JOIN-Operator ist und somit vorausgesetzt wird.

WITH
dataset AS (
  SELECT ARRAY[
    CAST(ROW('Sally', 'engineering', ARRAY[1,2,3,4]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))),
    CAST(ROW('John', 'finance', ARRAY[7,8,9]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))),
    CAST(ROW('Amy', 'devops', ARRAY[12,13,14,15]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER)))
  ] AS users
),
users AS (
 SELECT person, score
 FROM
   dataset,
   UNNEST(dataset.users) AS t(person),
   UNNEST(person.scores) AS t(score)
)
SELECT person.name, person.department, SUM(score) AS total_score FROM users
GROUP BY (person.name, person.department)
ORDER BY (total_score) DESC
LIMIT 1

Diese Abfrage gibt Folgendes zurück:

+---------------------------------+
| name | department | total_score |
+---------------------------------+
| Amy  | devops     | 54          |
+---------------------------------+

Wählen Sie aus einer Liste mit Mitarbeitern den Mitarbeiter mit den höchsten einzelnen Werten aus.

WITH
dataset AS (
 SELECT ARRAY[
   CAST(ROW('Sally', 'engineering', ARRAY[1,2,3,4]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))),
   CAST(ROW('John', 'finance', ARRAY[7,8,9]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))),
   CAST(ROW('Amy', 'devops', ARRAY[12,13,14,15]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER)))
 ] AS users
),
users AS (
 SELECT person, score
 FROM
   dataset,
   UNNEST(dataset.users) AS t(person),
   UNNEST(person.scores) AS t(score)
)
SELECT person.name, score FROM users
ORDER BY (score) DESC
LIMIT 1

Diese Abfrage gibt Folgendes zurück:

+--------------+
| name | score |
+--------------+
| Amy  | 15    |
+--------------+

Überlegungen zu CROSS JOIN und UNNEST

Wenn UNNEST für ein oder mehrere Arrays in der Abfrage verwendet wird und eines der Arrays NULL ist, gibt die Abfrage keine Zeilen zurück. Wenn UNNEST in ein Array verwendet wird, das eine leere Zeichenfolge ist, wird die leere Zeichenfolge zurückgegeben.

In der folgenden Abfrage werden beispielsweise keine Zeilen zurückgegeben, da das zweite Array null ist.

SELECT 
    col1, 
    col2 
FROM UNNEST (ARRAY ['apples','oranges','lemons']) AS t(col1)
CROSS JOIN UNNEST (ARRAY []) AS t(col2)

Im nächsten Beispiel wird das zweite Array so geändert, dass es eine leere Zeichenfolge enthält. Für jede Zeile gibt die Abfrage den Wert in col1 und eine leere Zeichenfolge für den Wert in col2 zurück. Die leere Zeichenfolge im zweiten Array ist erforderlich, damit die Werte im ersten Array zurückgegeben werden.

SELECT 
    col1, 
    col2 
FROM UNNEST (ARRAY ['apples','oranges','lemons']) AS t(col1)
CROSS JOIN UNNEST (ARRAY ['']) AS t(col2)