Aplanamiento de matrices anidadas

Cuando trabaja con matrices anidadas, a menudo necesita ampliar elementos de matriz anidados en una única matriz o ampliar la matriz en varias filas.

Uso de la función de aplanamiento

Para aplanar los elementos de una matriz anidada en una única matriz de valores, utilice la función flatten. Esta consulta devuelve una fila para cada elemento de la matriz.

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

Esta consulta devuelve:

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

Uso de CROSS JOIN y UNNEST

Para aplanar una matriz en varias filas, utilice CROSS JOIN junto con el operador UNNEST, tal y como se muestra en este ejemplo:

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)

Esta consulta devuelve:

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

Para aplanar una matriz de pares de clave-valor, transponga las claves seleccionadas en columnas, tal y como se muestra en este ejemplo:

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)

Esta consulta devuelve:

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

Partiendo de una lista de empleados, seleccione el que tenga la puntuación combinada más alta. UNNEST puede utilizarse en la cláusula FROM sin ir precedido de CROSS JOIN, ya que es el operador de combinación predeterminado y está implícito.

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

Esta consulta devuelve:

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

En una lista de empleados seleccione el empleado con la máxima puntuación individual.

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

Esta consulta devuelve:

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

Consideraciones sobre CROSS JOIN y UNNEST

Si se usa UNNEST en una o más matrices de la consulta, y una de las matrices es NULL, la consulta no devuelve filas. Si se usa UNNEST en una matriz que es una cadena vacía, se devuelve la cadena vacía.

Por ejemplo, en la siguiente consulta, dado que la segunda matriz es nula, la consulta no devuelve filas.

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

En el siguiente ejemplo, la segunda matriz se modifica para que contenga una cadena vacía. Para cada fila, la consulta devuelve el valor de col1 y una cadena vacía para el valor de col2. La cadena vacía de la segunda matriz es necesaria para que se devuelvan los valores de la primera matriz.

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