En GoogleSQL para 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, 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 GoogleSQL, 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 arrays mediante funciones como ARRAY_CONCAT() y convertir los arrays en strings con ARRAY_TO_STRING().
Accede a elementos del array
Considera la siguiente tabla emulada llamada Sequences. Esta tabla contiene la columna some_numbers del tipo de datos ARRAY.
WITH
Sequences AS (
SELECT [0, 1, 1, 2, 3, 5] AS some_numbers UNION ALL
SELECT [2, 4, 8, 16, 32] UNION ALL
SELECT [5, 10]
)
SELECT * FROM Sequences
/*---------------------*
| some_numbers |
+---------------------+
| [0, 1, 1, 2, 3, 5] |
| [2, 4, 8, 16, 32] |
| [5, 10] |
*---------------------*/
Para acceder a los elementos del array en la columna some_numbers especifica qué
tipo de índice quieres usar:
index
o OFFSET(index) para
índices basados en cero, o bien ORDINAL(index) para
índices basados en uno.
Por ejemplo:
SELECT
some_numbers,
some_numbers[0] AS index_0,
some_numbers[OFFSET(1)] AS offset_1,
some_numbers[ORDINAL(1)] AS ordinal_1
FROM Sequences
/*--------------------+---------+----------+-----------*
| some_numbers | index_0 | offset_1 | ordinal_1 |
+--------------------+---------+----------+-----------+
| [0, 1, 1, 2, 3, 5] | 0 | 1 | 0 |
| [2, 4, 8, 16, 32] | 2 | 4 | 2 |
| [5, 10] | 5 | 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 |
*--------------------+--------*/
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 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 INNER JOIN correlacionado para 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, un INNER JOIN o CROSS JOIN correlacionado combina este conjunto nuevo de filas con la fila única N de la tabla de origen.
Ejemplos
En el siguiente ejemplo, se usa UNNEST para mostrar una fila para cada elemento en la columna de array. Debido a INNER 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
INNER 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 correlacionadas, el operador UNNEST es opcional y INNER JOIN se puede expresar como una CROSS JOIN o una unión cruzada de comas. Con el uso de la notación de abreviatura de unión cruzada con comas, el ejemplo anterior se consolida de la siguiente manera:
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 INNER 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 AS r
INNER 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.
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 un elemento 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 unión cruzada y aplanar el array. Esto equivale a usar una CROSS JOIN explícita o el siguiente ejemplo, que usa una INNER 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)
INNER 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 arrays con INNER 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 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
Participant.name,
SUM(duration) AS finish_time
FROM Races
INNER JOIN Races.participants AS Participant
LEFT JOIN Participant.laps AS 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 |
*-------------+--------------------*/
Construye arrays
Puedes construir un array mediante funciones literales o de array. Para obtener más información sobre cómo construir arrays, consulta Tipo de array.
Crea arrays de subconsultas
Una tarea común cuando se trabaja con arrays es convertir un resultado de subconsulta en un array. En GoogleSQL, 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.
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 el operador 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 GoogleSQL, 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) AS x) AS sums
FROM Sequences AS s;
/*--------------------+------*
| some_numbers | sums |
+--------------------+------+
| [0, 1, 1, 2, 3, 5] | 12 |
| [2, 4, 8, 16, 32] | 62 |
| [5, 10] | 15 |
*--------------------+------*/
GoogleSQL también admite una función de agregación, ARRAY_CONCAT_AGG(), que concatena los elementos de una columna de array en filas.
WITH Aggregates 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 Aggregates;
/*--------------------------------------------------*
| count_to_six_agg |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
*--------------------------------------------------*/
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 Words AS
(SELECT ["Hello", "World"] AS greeting)
SELECT ARRAY_TO_STRING(greeting, " ") AS greetings
FROM Words;
/*-------------*
| 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] |
*--------------------------------------------------*/
Actualiza arrays
Considera la siguiente tabla llamada arrays_table. La primera columna de la tabla es un array de números enteros y la segunda columna contiene dos arreglos anidados de números enteros.
WITH arrays_table AS (
SELECT
[1, 2] AS regular_array,
STRUCT([10, 20] AS first_array, [100, 200] AS second_array) AS nested_arrays
UNION ALL SELECT
[3, 4] AS regular_array,
STRUCT([30, 40] AS first_array, [300, 400] AS second_array) AS nested_arrays
)
SELECT * FROM arrays_table;
/*---------------*---------------------------*----------------------------*
| regular_array | nested_arrays.first_array | nested_arrays.second_array |
+---------------+---------------------------+----------------------------+
| [1, 2] | [10, 20] | [100, 200] |
| [3, 4] | [30, 40] | [130, 400] |
*---------------*---------------------------*----------------------------*/
Puedes actualizar arrays en una tabla mediante la declaración UPDATE. En el siguiente
ejemplo, se inserta el número 5 en la columna regular_array
y los elementos del campo first_array de la columna nested_arrays
en el campo second_array:
UPDATE
arrays_table
SET
regular_array = ARRAY_CONCAT(regular_array, [5]),
nested_arrays.second_array = ARRAY_CONCAT(nested_arrays.second_array,
nested_arrays.first_array)
WHERE TRUE;
SELECT * FROM arrays_table;
/*---------------*---------------------------*----------------------------*
| regular_array | nested_arrays.first_array | nested_arrays.second_array |
+---------------+---------------------------+----------------------------+
| [1, 2, 5] | [10, 20] | [100, 200, 10, 20] |
| [3, 4, 5] | [30, 40] | [130, 400, 30, 40] |
*---------------*---------------------------*----------------------------*/
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(
SELECT AS STRUCT
letters[SAFE_OFFSET(index)] AS letter,
numbers[SAFE_OFFSET(index)] AS number
FROM Combinations
INNER JOIN
UNNEST(
GENERATE_ARRAY(
0,
LEAST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
ORDER BY index
) AS pairs;
/*------------------------------*
| 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.
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
INNER JOIN
UNNEST(
GENERATE_ARRAY(
0,
GREATEST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
ORDER BY index
) AS pairs;
/*-------------------------------*
| pairs |
+-------------------------------+
| [{ letter: "a", number: 1 }, |
| { letter: "b", number: 2 }, |
| { letter: null, number: 3 }] |
*-------------------------------*/
Compila arrays de arrays
GoogleSQL no admite la compilación de arrays de arrays directamente. En su lugar, debes crear un array de structs, 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]}] |
*-------------------*/