Appiattimento di matrici nidificate

Quando si utilizzano matrici nidificate, spesso è necessario espanderne gli elementi in una singola matrice oppure espandere la matrice stessa in più righe.

Esempi

Per appiattire gli elementi di una matrice nidificata in una singola matrice di valori, utilizza la funzione flatten. Questa query restituisce una riga per ciascun elemento della matrice.

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

Questa query restituisce:

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

Per appiattire una matrice in più righe, utilizza CROSS JOIN in combinazione con l'operatore UNNEST, come in questo esempio:

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)

Questa query restituisce:

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

Per appiattire una matrice di coppie chiave-valore, ridisponi le chiavi selezionate in colonne, come in questo esempio:

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)

Questa query restituisce:

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

Da un elenco di dipendenti, seleziona quello con il miglior punteggio combinato. UNNEST può essere utilizzato nella clausola FROM senza essere preceduto da CROSS JOIN, poiché è l'operatore join predefinito e pertanto è implicito.

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

Questa query restituisce:

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

Da un elenco di dipendenti, seleziona quello con il miglior punteggio individuale.

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

Questa query restituisce:

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

Considerazioni e limitazioni

Se UNNEST viene utilizzato su uno o più array nella query e uno degli array è NULL, la query non restituisce righe. Se UNNEST viene utilizzato su un array che è una stringa vuota, viene restituita la stringa vuota.

Ad esempio, nella query seguente, poiché il secondo array è nullo, la query non restituisce alcuna riga.

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

Nell'esempio successivo, il secondo array viene modificato per contenere una stringa vuota. Per ogni riga, la query restituisce il valore in col1 e una stringa vuota per il valore in col2. La stringa vuota nel secondo array è necessaria per restituire i valori del primo array.

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