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.
Ejemplos
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] |
+-----------+
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 y limitaciones
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)