En BigQuery, un arreglo es una lista ordenada que consta de cero o más valores del mismo tipo de datos. Puedes construir arreglos de tipos de datos simples, como INT64 y tipos de datos complejos, como STRUCT. La excepción actual a esto es el tipo de datos ARRAY, ya que no se admiten arrays de arrays no compatibles. Para obtener más información sobre el tipo de datos ARRAY, incluido el control de NULL, consulta Tipo array.
Con BigQuery, puedes construir literales de arreglo, compilar arreglos a partir de subconsultas mediante la función ARRAY y agregar los valores a un arreglo con la función ARRAY_AGG.
Puedes combinar arreglos mediante funciones como ARRAY_CONCAT() y convertir los arreglos en strings con ARRAY_TO_STRING().
Construye arreglos
Usa literales de arreglo
Puedes compilar un literal de arreglo en BigQuery con corchetes ([ y ]). Cada elemento en un arreglo está separado por una coma.
SELECT [1, 2, 3] as numbers;
SELECT ["apple", "pear", "orange"] as fruit;
SELECT [true, false, true] as booleans;
También puedes crear arreglos a partir de cualquier expresión que tenga tipos compatibles. Por ejemplo:
SELECT [a, b, c]
FROM
(SELECT 5 AS a,
37 AS b,
406 AS c);
SELECT [a, b, c]
FROM
(SELECT CAST(5 AS INT64) AS a,
CAST(37 AS FLOAT64) AS b,
406 AS c);
Observa que el segundo ejemplo contiene tres expresiones: una que muestra un INT64, otra que muestra un FLOAT64 y otra que declara un literal. Esta expresión funciona porque las tres expresiones comparten FLOAT64 como un supertipo.
A fin de declarar un tipo de datos específico para un arreglo, usa corchetes angulares (< y >). Por ejemplo:
SELECT ARRAY<FLOAT64>[1, 2, 3] as floats;
Los arreglos de la mayoría de los tipos de datos, como INT64 o STRING, no requieren que las declares primero.
SELECT [1, 2, 3] as numbers;
Puedes escribir un arreglo vacío de un tipo específico con ARRAY<type>[]. También puedes escribir un arreglo vacío sin tipo con [], en cuyo caso BigQuery intentará deducir el tipo de arreglo a partir del contexto circundante. Si BigQuery no puede deducir un tipo, se usa el tipo predeterminado ARRAY<INT64>.
Usa valores generados
También puedes construir un ARRAY con valores generados.
Genera arrays de números enteros
GENERATE_ARRAY genera un array de valores a partir de un valor de paso y de un valor inicial y final.
Por ejemplo, con la consulta siguiente se genera un arreglo que contiene todos los números enteros impares del 11 al 33, inclusive:
SELECT GENERATE_ARRAY(11, 33, 2) AS odds;
+--------------------------------------------------+
| odds |
+--------------------------------------------------+
| [11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33] |
+--------------------------------------------------+
También puedes generar un arreglo de valores en orden descendente si le das un valor de paso negativo:
SELECT GENERATE_ARRAY(21, 14, -1) AS countdown;
+----------------------------------+
| countdown |
+----------------------------------+
| [21, 20, 19, 18, 17, 16, 15, 14] |
+----------------------------------+
Genera arrays de fechas
GENERATE_DATE_ARRAY genera un array de DATE desde una DATE inicial y final y un INTERVAL de paso.
Puedes generar un conjunto de valores DATE con GENERATE_DATE_ARRAY. Por ejemplo, esta consulta muestra la DATE actual y las DATE siguientes en intervalos de 1 WEEK y, también, incluye una DATE posterior:
SELECT
GENERATE_DATE_ARRAY('2017-11-21', '2017-12-31', INTERVAL 1 WEEK)
AS date_array;
+--------------------------------------------------------------------------+
| date_array |
+--------------------------------------------------------------------------+
| [2017-11-21, 2017-11-28, 2017-12-05, 2017-12-12, 2017-12-19, 2017-12-26] |
+--------------------------------------------------------------------------+
Accede a elementos del array
Considera la siguiente tabla, sequences:
+---------------------+
| some_numbers |
+---------------------+
| [0, 1, 1, 2, 3, 5] |
| [2, 4, 8, 16, 32] |
| [5, 10] |
+---------------------+
Esta tabla contiene la columna some_numbers del tipo de datos ARRAY.
Para acceder a los elementos desde los arrays de esta columna, debes especificar qué tipo de índice deseas usar: OFFSET para índices basados en cero o, también, ORDINAL para índices basados en uno.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
some_numbers[OFFSET(1)] AS offset_1,
some_numbers[ORDINAL(1)] AS ordinal_1
FROM sequences;
+--------------------+----------+-----------+
| some_numbers | offset_1 | ordinal_1 |
+--------------------+----------+-----------+
| [0, 1, 1, 2, 3, 5] | 1 | 0 |
| [2, 4, 8, 16, 32] | 4 | 2 |
| [5, 10] | 10 | 5 |
+--------------------+----------+-----------+
Busca longitudes
La función ARRAY_LENGTH() muestra la longitud de un arreglo.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
ARRAY_LENGTH(some_numbers) AS len
FROM sequences;
+--------------------+--------+
| some_numbers | len |
+--------------------+--------+
| [0, 1, 1, 2, 3, 5] | 6 |
| [2, 4, 8, 16, 32] | 5 |
| [5, 10] | 2 |
+--------------------+--------+
Convierte elementos de un array en filas de una tabla
Para convertir un ARRAY en un conjunto de filas, también conocido como “compactación”, usa el operador UNNEST. UNNEST toma un ARRAY y muestra una tabla con una sola fila para cada elemento en el ARRAY.
Debido a que UNNEST desacomoda el orden de los elementos del ARRAY, es posible que desees restablecer el orden en la tabla. Si quieres hacerlo, usa la cláusula opcional WITH OFFSET a fin de mostrar una columna adicional con el desplazamiento para cada elemento del arreglo. Luego, usa la cláusula ORDER BY, y se ordenarán las filas por su desplazamiento.
Ejemplo
SELECT *
FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'])
AS element
WITH OFFSET AS offset
ORDER BY offset;
+----------+--------+
| element | offset |
+----------+--------+
| foo | 0 |
| bar | 1 |
| baz | 2 |
| qux | 3 |
| corge | 4 |
| garply | 5 |
| waldo | 6 |
| fred | 7 |
+----------+--------+
Para compactar una columna completa de ARRAY y conservar los valores de las otras columnas en cada fila, debes usar una unión cruzada a fin de unir la tabla que contiene la columna ARRAY al resultado UNNEST de esa columna ARRAY.
Con una unión correlacionada, el operador UNNEST hace referencia a la columna de tipo ARRAY de cada fila de la tabla de origen, que aparece antes en la cláusula FROM. Para cada fila N en la tabla de origen, UNNEST acopla el ARRAY de la fila N en un conjunto de filas que contienen los elementos ARRAY. Luego, la unión cruzada une este conjunto de filas nuevo con la única fila N de la tabla de origen.
Ejemplos
En el ejemplo siguiente, se usa UNNEST a fin de mostrar una fila para cada elemento en la columna de array. Debido a CROSS JOIN, la columna id contiene los valores id de la fila en sequences que contiene cada número.
WITH sequences AS
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id, flattened_numbers
FROM sequences
CROSS JOIN UNNEST(sequences.some_numbers) AS flattened_numbers;
+------+-------------------+
| id | flattened_numbers |
+------+-------------------+
| 1 | 0 |
| 1 | 1 |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 5 |
| 2 | 2 |
| 2 | 4 |
| 2 | 8 |
| 2 | 16 |
| 2 | 32 |
| 3 | 5 |
| 3 | 10 |
+------+-------------------+
Ten en cuenta que para las uniones cruzadas correlacionadas, el operador UNNEST es opcional y la CROSS JOIN se puede expresar como una unión de comas. Con el uso de esta notación abreviada, el ejemplo anterior se convierte en lo que se muestra a continuación:
WITH sequences AS
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id, flattened_numbers
FROM sequences, sequences.some_numbers AS flattened_numbers;
+------+-------------------+
| id | flattened_numbers |
+------+-------------------+
| 1 | 0 |
| 1 | 1 |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 5 |
| 2 | 2 |
| 2 | 4 |
| 2 | 8 |
| 2 | 16 |
| 2 | 32 |
| 3 | 5 |
| 3 | 10 |
+------+-------------------+
Consulta arrays anidados
Si una tabla contiene un ARRAY de STRUCT, puedes compactar el ARRAY para consultar los campos de STRUCT.
También puedes acoplar campos de tipo ARRAY de valores STRUCT.
Consulta los elementos STRUCT en un ARRAY
En el siguiente ejemplo, se usa UNNEST con CROSS JOIN para acoplar un ARRAY de STRUCTs.
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as laps),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as laps),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as laps),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as laps),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as laps),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as laps),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as laps),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as laps)]
AS participants)
SELECT
race,
participant
FROM races r
CROSS JOIN UNNEST(r.participants) as participant;
+------+---------------------------------------+
| race | participant |
+------+---------------------------------------+
| 800M | {Rudisha, [23.4, 26.3, 26.4, 26.1]} |
| 800M | {Makhloufi, [24.5, 25.4, 26.6, 26.1]} |
| 800M | {Murphy, [23.9, 26, 27, 26]} |
| 800M | {Bosse, [23.6, 26.2, 26.5, 27.1]} |
| 800M | {Rotich, [24.7, 25.6, 26.9, 26.4]} |
| 800M | {Lewandowski, [25, 25.7, 26.3, 27.2]} |
| 800M | {Kipketer, [23.2, 26.1, 27.3, 29.4]} |
| 800M | {Berian, [23.7, 26.1, 27, 29.3]} |
+------+---------------------------------------+
Puedes encontrar información específica de los campos repetidos. Por ejemplo, con la consulta siguiente, se muestra el corredor más rápido en una carrera de 800 m.
Este ejemplo no implica acoplar un arreglo, pero representa una forma común de obtener información de un campo repetido.
Ejemplo
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as laps),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as laps),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as laps),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as laps),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as laps),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as laps),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as laps),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as laps)]
AS participants)
SELECT
race,
(SELECT name
FROM UNNEST(participants)
ORDER BY (
SELECT SUM(duration)
FROM UNNEST(laps) AS duration) ASC
LIMIT 1) AS fastest_racer
FROM races;
+------+---------------+
| race | fastest_racer |
+------+---------------+
| 800M | Rudisha |
+------+---------------+
Consulta los campos de tipo ARRAY en una STRUCT
También puedes obtener información de los campos repetidos anidados. Por ejemplo, con la declaración siguiente, se muestra el corredor que tuvo la vuelta más rápida en una carrera de 800 m.
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as laps),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as laps),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as laps),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as laps),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as laps),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as laps),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as laps),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as laps)]
AS participants)
SELECT
race,
(SELECT name
FROM UNNEST(participants),
UNNEST(laps) AS duration
ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM races;
+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer |
+------+-------------------------+
Ten en cuenta que la consulta anterior usa el operador de coma (,) para realizar una CROSS JOIN implícita. Es equivalente al ejemplo siguiente, que usa una CROSS JOIN explícita.
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as laps),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as laps),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as laps),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as laps),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as laps),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as laps),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as laps),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as laps)]
AS participants)
SELECT
race,
(SELECT name
FROM UNNEST(participants)
CROSS JOIN UNNEST(laps) AS duration
ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM races;
+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer |
+------+-------------------------+
Si compactas los arreglos con CROSS JOIN, se excluyen las filas que tienen arreglos NULL o vacíos. Si deseas incluir estas filas, usa LEFT JOIN.
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as laps),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as laps),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as laps),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as laps),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as laps),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as laps),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as laps),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as laps),
STRUCT("Nathan" as name, ARRAY<FLOAT64>[] as laps),
STRUCT("David" as name, NULL as laps)]
AS participants)
SELECT
name, sum(duration) AS finish_time
FROM races, races.participants LEFT JOIN participants.laps duration
GROUP BY name;
+-------------+--------------------+
| name | finish_time |
+-------------+--------------------+
| Murphy | 102.9 |
| Rudisha | 102.19999999999999 |
| David | NULL |
| Rotich | 103.6 |
| Makhloufi | 102.6 |
| Berian | 106.1 |
| Bosse | 103.4 |
| Kipketer | 106 |
| Nathan | NULL |
| Lewandowski | 104.2 |
+-------------+--------------------+
Crea arrays de subconsultas
Una tarea común cuando se trabaja con arrays es convertir un resultado de subconsulta en un array. En BigQuery, puedes lograrlo con la función ARRAY().
Por ejemplo, considera la operación siguiente en la tabla sequences:
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
ARRAY(SELECT x * 2
FROM UNNEST(some_numbers) AS x) AS doubled
FROM sequences;
+--------------------+---------------------+
| some_numbers | doubled |
+--------------------+---------------------+
| [0, 1, 1, 2, 3, 5] | [0, 2, 2, 4, 6, 10] |
| [2, 4, 8, 16, 32] | [4, 8, 16, 32, 64] |
| [5, 10] | [10, 20] |
+--------------------+---------------------+
Este ejemplo comienza con una tabla llamada secuencias. Esta tabla contiene una columna, some_numbers, de tipo ARRAY<INT64>.
La consulta en sí contiene una subconsulta. Esta subconsulta selecciona cada fila en la columna some_numbers y usa UNNEST para mostrar el arreglo como un conjunto de filas. Luego, multiplica cada valor por dos y, luego, vuelve a combinar las filas en un array con el operador ARRAY().
Filtra arrays
En el ejemplo siguiente, se usa una cláusula WHERE en la subconsulta del operador ARRAY() para filtrar las filas que se muestran.
Nota: En los ejemplos siguientes, las filas resultantes no están ordenadas.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT
ARRAY(SELECT x * 2
FROM UNNEST(some_numbers) AS x
WHERE x < 5) AS doubled_less_than_five
FROM sequences;
+------------------------+
| doubled_less_than_five |
+------------------------+
| [0, 2, 2, 4, 6] |
| [4, 8] |
| [] |
+------------------------+
Observa que la tercera fila contiene un arreglo vacío, porque los elementos de la fila original correspondiente ([5, 10]) no cumplieron con el requisito de filtro de x < 5.
También puedes filtrar los arrays con SELECT DISTINCT para mostrar solo elementos únicos dentro de un array.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers)
SELECT ARRAY(SELECT DISTINCT x
FROM UNNEST(some_numbers) AS x) AS unique_numbers
FROM sequences;
+-----------------+
| unique_numbers |
+-----------------+
| [0, 1, 2, 3, 5] |
+-----------------+
También puedes filtrar las filas de los arrays con la palabra clave IN. Esta palabra clave filtra las filas que contienen los arreglos para determinar si un valor específico coincide con un elemento del arreglo.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT
ARRAY(SELECT x
FROM UNNEST(some_numbers) AS x
WHERE 2 IN UNNEST(some_numbers)) AS contains_two
FROM sequences;
+--------------------+
| contains_two |
+--------------------+
| [0, 1, 1, 2, 3, 5] |
| [2, 4, 8, 16, 32] |
| [] |
+--------------------+
Observa otra vez que la tercera fila contiene un array vacío, ya que el array en la fila original correspondiente ([5, 10]) no contenía 2.
Analiza arrays
Para verificar si un arreglo contiene un valor específico, usa el operador IN con UNNEST. Para verificar si un array contiene un valor que coincide con una condición, usa el operador EXISTS con UNNEST.
Analiza en busca de valores específicos
Para analizar un arreglo en busca de un valor específico, usa el operador IN con UNNEST.
Ejemplo
En el ejemplo siguiente, se muestra true si el arreglo contiene el número 2.
SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;
+----------------+
| contains_value |
+----------------+
| true |
+----------------+
Para mostrar las filas de una tabla en la que la columna del arreglo contiene un valor específico, debes filtrar los resultados de IN UNNEST con la cláusula WHERE.
Ejemplo
En el ejemplo siguiente, se muestra el valor id para las filas en las que la columna de arreglo contiene el valor 2.
WITH sequences AS
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id AS matching_rows
FROM sequences
WHERE 2 IN UNNEST(sequences.some_numbers)
ORDER BY matching_rows;
+---------------+
| matching_rows |
+---------------+
| 1 |
| 2 |
+---------------+
Analiza en busca de valores que satisfagan una condición
Si deseas analizar un arreglo en busca de valores que coincidan con una condición, usa UNNEST a fin de mostrar una tabla de elementos en el arreglo. Usa WHERE si deseas filtrar la tabla resultante en una subconsulta y usa EXISTS si deseas comprobar si la tabla filtrada contiene algunas filas.
Ejemplo
En el ejemplo siguiente, se muestra el valor id para las filas donde la columna de arreglo contiene valores mayores que 5.
WITH
Sequences AS (
SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL
SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL
SELECT 3 AS id, [5, 10] AS some_numbers
)
SELECT id AS matching_rows
FROM Sequences
WHERE EXISTS(SELECT * FROM UNNEST(some_numbers) AS x WHERE x > 5);
+---------------+
| matching_rows |
+---------------+
| 2 |
| 3 |
+---------------+
Analiza en busca de valores de campo STRUCT que satisfacen una condición
Si deseas buscar un arreglo de STRUCT para un campo cuyo valor coincida con una condición, usa UNNEST a fin de mostrar una tabla con una columna para cada campo STRUCT. Luego, filtra las filas no coincidentes de la tabla con WHERE EXISTS.
Ejemplo
En el ejemplo siguiente, se muestran las filas en las que la columna del array contiene una STRUCT cuyo campo b tiene un valor mayor que 3.
WITH
Sequences AS (
SELECT 1 AS id, [STRUCT(0 AS a, 1 AS b)] AS some_numbers
UNION ALL
SELECT 2 AS id, [STRUCT(2 AS a, 4 AS b)] AS some_numbers
UNION ALL
SELECT 3 AS id, [STRUCT(5 AS a, 3 AS b), STRUCT(7 AS a, 4 AS b)] AS some_numbers
)
SELECT id AS matching_rows
FROM Sequences
WHERE EXISTS(SELECT 1 FROM UNNEST(some_numbers) WHERE b > 3);
+---------------+
| matching_rows |
+---------------+
| 2 |
| 3 |
+---------------+
Arrays y agregación
Con BigQuery, puedes agregar valores en un array con ARRAY_AGG().
WITH fruits AS
(SELECT "apple" AS fruit
UNION ALL SELECT "pear" AS fruit
UNION ALL SELECT "banana" AS fruit)
SELECT ARRAY_AGG(fruit) AS fruit_basket
FROM fruits;
+-----------------------+
| fruit_basket |
+-----------------------+
| [apple, pear, banana] |
+-----------------------+
El arreglo que muestra ARRAY_AGG() está en un orden arbitrario, ya que el orden en el que la función concatena valores no está garantizado. Para ordenar los elementos del arreglo, usa ORDER BY. Por ejemplo:
WITH fruits AS
(SELECT "apple" AS fruit
UNION ALL SELECT "pear" AS fruit
UNION ALL SELECT "banana" AS fruit)
SELECT ARRAY_AGG(fruit ORDER BY fruit) AS fruit_basket
FROM fruits;
+-----------------------+
| fruit_basket |
+-----------------------+
| [apple, banana, pear] |
+-----------------------+
También puedes aplicar funciones de agregación como SUM() a los elementos de un arreglo. Por ejemplo, con la consulta siguiente, se muestra la suma de elementos del arreglo para cada fila de la tabla sequences.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
(SELECT SUM(x)
FROM UNNEST(s.some_numbers) x) AS sums
FROM sequences s;
+--------------------+------+
| some_numbers | sums |
+--------------------+------+
| [0, 1, 1, 2, 3, 5] | 12 |
| [2, 4, 8, 16, 32] | 62 |
| [5, 10] | 15 |
+--------------------+------+
BigQuery también admite una función de agregación, ARRAY_CONCAT_AGG(), que concatena los elementos de una columna de arreglo en filas.
WITH aggregate_example AS
(SELECT [1,2] AS numbers
UNION ALL SELECT [3,4] AS numbers
UNION ALL SELECT [5, 6] AS numbers)
SELECT ARRAY_CONCAT_AGG(numbers) AS count_to_six_agg
FROM aggregate_example;
+--------------------------------------------------+
| count_to_six_agg |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------------------------------------+
Nota: El array que muestra ARRAY_CONCAT_AGG() no es determinista, ya que el orden en que la función concatena los valores no está garantizado.
Convierte los arrays en strings
La función ARRAY_TO_STRING() te permite convertir un ARRAY<STRING> en un único valor de STRING o un ARRAY<BYTES> en un valor único de BYTES, en el que el valor resultante es la concatenación ordenada de los elementos del array.
El segundo argumento es el separador que la función insertará entre las entradas para producir la salida. Este segundo argumento debe ser del mismo tipo que los elementos del primero.
Ejemplo:
WITH greetings AS
(SELECT ["Hello", "World"] AS greeting)
SELECT ARRAY_TO_STRING(greeting, " ") AS greetings
FROM greetings;
+-------------+
| greetings |
+-------------+
| Hello World |
+-------------+
El tercer argumento opcional ocupa el lugar de los valores NULL en el arreglo de entrada.
Si omites este argumento, la función ignora los elementos del arreglo
NULL.Si proporcionas una string vacía, la función inserta un separador para los elementos del arreglo
NULL.
Ejemplo:
SELECT
ARRAY_TO_STRING(arr, ".", "N") AS non_empty_string,
ARRAY_TO_STRING(arr, ".", "") AS empty_string,
ARRAY_TO_STRING(arr, ".") AS omitted
FROM (SELECT ["a", NULL, "b", NULL, "c", NULL] AS arr);
+------------------+--------------+---------+
| non_empty_string | empty_string | omitted |
+------------------+--------------+---------+
| a.N.b.N.c.N | a..b..c. | a.b.c |
+------------------+--------------+---------+
Combina arrays
En algunos casos, es posible que desees combinar varios arrays en uno solo.
Puedes lograr esto con la función ARRAY_CONCAT().
SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;
+--------------------------------------------------+
| count_to_six |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------------------------------------+
Arrays de compresión
Si tienes dos arrays del mismo tamaño, puedes combinarlos en uno solo que contenga pares de elementos de los arrays de entrada, tomados de sus posiciones correspondientes. A veces, esta operación se denomina compresión.
Puedes comprimir arreglos con UNNEST y WITH OFFSET. En este ejemplo, cada par de valores se almacena como una STRUCT en un arreglo:
WITH
combinations AS (
SELECT
['a', 'b'] AS letters,
[1, 2, 3] AS numbers
)
SELECT
ARRAY(
SELECT AS STRUCT
letters[SAFE_OFFSET(index)] AS letter,
numbers[SAFE_OFFSET(index)] AS number
FROM combinations
CROSS JOIN
UNNEST(
GENERATE_ARRAY(
0,
LEAST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
ORDER BY index
);
+------------------------------+
| pairs |
+------------------------------+
| [{ letter: "a", number: 1 }, |
| { letter: "b", number: 2 }] |
+------------------------------+
Puedes usar arreglos de entrada de longitudes distintas mientras que el primer arreglo sea igual o menor a la longitud del segundo. El arreglo comprimido tendrá la longitud del arreglo de entrada más corto.
Para obtener un array comprimido que incluya todos los elementos, incluso cuando los array de entrada tengan diferentes longitudes, cambia LEAST por GREATEST. Los elementos de cualquiera de los arrays que no tengan un elemento asociado en el otro se vincularán con NULL.
WITH
combinations AS (
SELECT
['a', 'b'] AS letters,
[1, 2, 3] AS numbers
)
SELECT
ARRAY(
SELECT AS STRUCT
letters[SAFE_OFFSET(index)] AS letter,
numbers[SAFE_OFFSET(index)] AS number
FROM combinations
CROSS JOIN
UNNEST(
GENERATE_ARRAY(
0,
GREATEST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
ORDER BY index
);
+-------------------------------+
| pairs |
+-------------------------------+
| [{ letter: "a", number: 1 }, |
| { letter: "b", number: 2 }, |
| { letter: null, number: 3 }] |
+-------------------------------+
Compila arreglos de arreglos
BigQuery no admite la compilación de arrays de arrays directamente. En su lugar, debes crear un arreglo de estructuras, en el que cada estructura contiene un campo de tipo ARRAY. A modo de ejemplo, considera la tabla points a continuación:
+----------+
| point |
+----------+
| [1, 5] |
| [2, 8] |
| [3, 7] |
| [4, 1] |
| [5, 7] |
+----------+
Ahora, supongamos que deseas crear un arreglo que consta de cada point en la tabla points. A fin de lograrlo, debes unir el arreglo que se muestra de cada fila a una STRUCT, como en el ejemplo siguiente.
WITH points AS
(SELECT [1, 5] as point
UNION ALL SELECT [2, 8] as point
UNION ALL SELECT [3, 7] as point
UNION ALL SELECT [4, 1] as point
UNION ALL SELECT [5, 7] as point)
SELECT ARRAY(
SELECT STRUCT(point)
FROM points)
AS coordinates;
+-------------------+
| coordinates |
+-------------------+
| [{point: [1,5]}, |
| {point: [2,8]}, |
| {point: [5,7]}, |
| {point: [3,7]}, |
| {point: [4,1]}] |
+--------------------+