Trabaja con arrays en SQL estándar

En BigQuery, un array es una lista ordenada que consta de cero o más valores del mismo tipo de datos. Puedes construir arrays 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: no se admiten arrays de arrays. Los arrays pueden incluir valores NULL.

Con BigQuery, puedes construir literales de array, compilar arrays a partir de subconsultas mediante la función ARRAY y agregar los valores a un array con la función ARRAY_AGG.

Puedes combinar arrays mediante funciones como ARRAY_CONCAT() y convertir los arrays en strings con ARRAY_TO_STRING().

Construye arrays

Usa literales de array

Puedes compilar un literal de array en BigQuery con corchetes ([ y ]). Cada elemento en un array 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 arrays 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 array, usa corchetes angulares (< y >). Por ejemplo:

SELECT ARRAY<FLOAT64>[1, 2, 3] as floats;

Los arrays de la mayoría de los tipos de datos, como INT64 o STRING, no requieren que los declares primero.

SELECT [1, 2, 3] as numbers;

Puedes escribir un array vacío de un tipo específico con ARRAY<type>[]. También puedes escribir un array vacío sin tipo con [], en cuyo caso BigQuery intentará deducir el tipo de array 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 array 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 array 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 array.

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      |
+--------------------+--------+

Compacta los arrays

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 array. 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 CROSS JOIN a fin de unir la tabla que contiene la columna ARRAY al resultado UNNEST de esa columna ARRAY.

Esta es una unión cruzada correlacionada: el operador UNNEST hace referencia a la columna de 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, CROSS JOIN se une a este conjunto nuevo de filas con la fila única N de la tabla de origen.

Ejemplo

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 splits),
     STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
     STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
     STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
     STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
     STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
     STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
     STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
       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 array, 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 splits),
     STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
     STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
     STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
     STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
     STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
     STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
     STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
       AS participants)
SELECT
  race,
  (SELECT name
   FROM UNNEST(participants)
   ORDER BY (
     SELECT SUM(duration)
     FROM UNNEST(splits) 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 splits),
    STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
    STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
    STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
    STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
    STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
    STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
    STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
    AS participants)
SELECT
race,
(SELECT name
 FROM UNNEST(participants),
   UNNEST(splits) 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 splits),
    STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
    STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
    STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
    STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
    STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
    STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
    STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
    AS participants)
SELECT
race,
(SELECT name
 FROM UNNEST(participants)
 CROSS JOIN UNNEST(splits) 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 arrays con CROSS JOIN, se excluyen las filas que tienen arrays 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 splits),
    STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
    STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
    STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
    STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
    STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
    STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
    STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits),
    STRUCT("Nathan" as name, ARRAY<FLOAT64>[] as splits),
    STRUCT("David" as name, NULL as splits)]
    AS participants)
SELECT
  name, sum(duration) AS finish_time
FROM races, races.participants LEFT JOIN participants.splits 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 array 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 array 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 arrays para determinar si un valor específico coincide con un elemento del array.

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 array 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 la función EXISTS con UNNEST.

Analiza en busca de valores específicos

Para analizar un array en busca de un valor específico, usa el operador IN con UNNEST.

Ejemplo

En el ejemplo siguiente, se muestra true si el array 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 array 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 array 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 array en busca de valores que coincidan con una condición, usa UNNEST a fin de mostrar una tabla de elementos en el array. 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 array 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 array 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 array 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 array, 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 array. Por ejemplo, con la consulta siguiente, se muestra la suma de elementos del array 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 array 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 array de entrada.

  • Si omites este argumento, la función ignora los elementos del array NULL.

  • Si proporcionas una string vacía, la función inserta un separador para los elementos del array 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 arrays con UNNEST y WITH OFFSET. En este ejemplo, cada par de valores se almacena como una STRUCT en un array:

WITH combinations AS (
  SELECT
    ['a', 'b'] AS letters,
    [1, 2, 3] AS numbers
)
SELECT ARRAY_AGG(
  STRUCT(letter, numbers[OFFSET(letters_offset)] AS number)
) AS pairs
FROM combinations, UNNEST(letters) AS letter WITH OFFSET AS letters_offset;

+------------------------------+
| pairs                        |
+------------------------------+
| [{ letter: "a", number: 1 }, |
|  { letter: "b", number: 2 }] |
+------------------------------+

Puedes usar arrays de entrada de longitudes distintas mientras que el primer array sea igual o menor a la longitud del segundo. El array comprimido tendrá la longitud del array de entrada más corto.

Compila arrays de arrays

BigQuery no admite la compilación de arrays de arrays directamente. En su lugar, debes crear un array 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 array que consta de cada point en la tabla points. A fin de lograrlo, debes unir el array 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]}]  |
+--------------------+