Trabaja con arreglos en SQL estándar

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: no se admiten arreglos de arreglos.

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

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

Construye arreglos

Usa literales de arreglo

Puede 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.

Si deseas 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 del contexto circundante. Si BigQuery no puede deducir un tipo, se usa el tipo ARRAY<INT64> predeterminado.

Usa valores generados

También puedes construir un ARRAY con valores generados.

Genera arreglos de números enteros

GENERATE_ARRAY genera un arreglo de valores a partir de un valor inicial y final, y un valor de paso. 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 arreglos de fechas

GENERATE_DATE_ARRAY genera un arreglo 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 siguientes DATE 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 arreglo

Considera la tabla siguiente 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 del ARRAY. Para acceder a los elementos desde los arreglos de esta columna, debes especificar qué tipo de indexación 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         |
+--------------------+----------+-----------+

Encuentra longitudes

Con la función ARRAY_LENGTH(), se 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      |
+--------------------+--------+

Compacta los arreglos

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. Para ello, 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 si deseas ordenar 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 acoplar una columna completa de ARRAY mientras conservas los valores de las otras columnas en cada fila, debes usar CROSS JOIN a fin de unir la tabla que contiene la columna ARRAY a la salida 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 une a este conjunto de filas nuevo con la única fila 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 arreglo. Debido a CROSS JOIN, la columna id contiene los valores id de la fila en sequences que contienen 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 |
+------+-------------------+

Consulta arreglos anidados

Si una tabla contiene un ARRAY de STRUCT, puedes acoplar el ARRAY para consultar los campos de la STRUCT. También puedes acoplar los campos de tipo ARRAY de los valores de la STRUCT.

Consulta los elementos STRUCT en un ARRAY

En el ejemplo siguiente, se usa UNNEST con CROSS JOIN para acoplar un ARRAY de STRUCT.

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

Ten en cuenta que acoplar los arreglos con una CROSS JOIN excluye las filas que tienen arreglos vacíos o NULL. Si deseas incluir estas filas, usa una 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 arreglos de subconsultas

Una tarea común cuando se trabaja con arreglos es convertir un resultado de subconsulta en un arreglo. En BigQuery, puedes lograr esto con la función ARRAY().

Por ejemplo, considera la operación siguiente en la tabla de 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 contiene una columna, some_numbers, de tipo ARRAY<INT64>.

La consulta en sí contiene una subconsulta. Esta subconsulta selecciona cada fila de la columna some_numbers y usa UNNEST para mostrar el arreglo como un conjunto de filas. A continuación, multiplica cada valor por dos y, luego, vuelve a combina las filas en un arreglo con el operador ARRAY().

Filtra arreglos

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, ya que los elementos en la fila original correspondiente ([5, 10]) no cumplieron con el requisito del filtro de x < 5.

También puedes filtrar los arreglos con SELECT DISTINCT para mostrar solo los elementos únicos dentro de este.

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 arreglos 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 una vez más que la tercera fila contiene una matriz vacía, ya que la matriz en la fila original correspondiente ([5, 10]) no contenía 2.

Analiza arreglos

Para verificar si un arreglo contiene un valor específico, usa el operador IN con UNNEST. Si deseas verificar si un arreglo 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 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           |
+----------------+

Si deseas 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 del id para las filas en 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 satisfacen 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 del id para las filas en la que la columna del 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 la que la columna del arreglo 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             |
+---------------+

Arreglos y agregación

Con BigQuery, puedes agregar valores en un arreglo 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 los valores no está garantizado. A fin de ordenar los elementos del arreglo, debes usar 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 de 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 arreglo que ARRAY_CONCAT_AGG() muestra no es determinista, ya que el orden en el que la función concatena los valores no está garantizado.

Convierte los arreglos en strings

La función ARRAY_TO_STRING() te permite convertir un ARRAY<STRING> en un solo valor de STRING o un ARRAY<BYTES> a un solo valor de BYTES, en el que el valor resultante es la concatenación ordenada de los elementos del arreglo.

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 arreglos

En algunos casos, es posible que desees combinar varios arreglos en uno solo. Para esto, debes usar 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]                               |
+--------------------------------------------------+

Construye arreglos de arreglos

BigQuery no admite la compilación de arreglos de arreglos de forma directa. En su lugar, debes crear un arreglo de estructuras, con cada estructura que contenga un campo de tipo ARRAY. A fin de ilustrar esto, considera la tabla siguiente de points:

+----------+
| 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 de points. A fin de lograrlo, debes unir el arreglo que se muestra de cada fila a una STRUCT, como se muestra a continuación.

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                                        |
+----------------------------------------------------+
| [{[1, 5]}, {[2, 8]}, {[3, 7]}, {[4, 1]}, {[5, 7]}] |
+----------------------------------------------------+
¿Te sirvió esta página? Envíanos tu opinión:

Enviar comentarios sobre…

Esta página
Comentarios sobre la documentación
BigQuery
Comentarios sobre el producto
¿Necesitas ayuda? Visita nuestra página de asistencia.