En GoogleSQL para BigQuery, una matriz es una lista ordenada que consta de cero o más valores del mismo tipo de datos. Puedes crear arrays de un tipo de datos simple, como INT64, o de un tipo de datos complejo, como STRUCT. Sin embargo, no se admiten matrices de matrices. Para obtener más información sobre el tipo de datos ARRAY
array, incluido el control de NULL, consulta Tipo de array.
Con GoogleSQL, puedes crear literales de matriz, crear matrices a partir de subconsultas mediante la función ARRAY y agregar valores en una matriz mediante la función ARRAY_AGG.
Puedes combinar matrices con funciones como ARRAY_CONCAT() y convertir matrices en cadenas con ARRAY_TO_STRING().
Acceder a elementos de una matriz
Consideremos la siguiente tabla, 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 de la matriz en la columna some_numbers, especifique el tipo de indexación que quiera usar: index o OFFSET(index) para los índices basados en cero, o ORDINAL(index) para los í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 |
*--------------------+---------+----------+-----------*/
Buscar longitudes
La función ARRAY_LENGTH devuelve la longitud de una matriz.
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 |
*--------------------+--------*/
Convertir elementos de una matriz en filas de una tabla
Para convertir un ARRAY en un conjunto de filas, también conocido como "aplanamiento", usa el operador
UNNEST. UNNEST toma un ARRAY y devuelve una tabla con una sola fila por cada elemento del ARRAY.
Como UNNEST arrasa con el orden de los elementos de ARRAY, podría interesarte restaurar el orden de la tabla. Para ello, usa la cláusula opcional WITH OFFSET
para devolver una columna adicional con el desplazamiento de cada elemento de la matriz
y, a continuación, usa la cláusula ORDER BY para 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 aplanar una columna completa de tipo ARRAY y, al mismo tiempo, conservar los valores de las demás columnas de cada fila, usa una INNER JOIN correlacionada para unir la tabla que contiene la columna ARRAY con la salida UNNEST de esa columna ARRAY.
En una unión correlacionada, el operador UNNEST hace referencia a la columna ARRAY con tipo de cada fila de la tabla de origen, que aparece anteriormente en la cláusula FROM. Por cada fila N de la tabla de origen, UNNEST cambia el formato de ARRAY de la fila N a un conjunto de filas que contiene los elementos ARRAY. A continuación, un INNER JOIN o un CROSS JOIN correlacionado combina este nuevo conjunto de filas con la fila N de la tabla de origen.
Ejemplos
En el siguiente ejemplo se usa UNNEST para devolver una fila por cada elemento de la columna de matriz. Debido a INNER JOIN, la columna id contiene los valores de id de la fila de 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, en las uniones correlacionadas, el operador UNNEST es opcional y el INNER JOIN se puede expresar como CROSS JOIN o como una unión cruzada con comas. Si se usa la notación abreviada de combinació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 |
*------+-------------------*/
Consultar matrices anidadas
Si una tabla contiene un ARRAY de STRUCTs, puedes desanidar el ARRAY para consultar los campos del STRUCT.
También puede acoplar campos de tipo ARRAY de valores STRUCT.
Consultar elementos STRUCT en una matriz
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 campos repetidos. Por ejemplo, la siguiente consulta devuelve el corredor más rápido de una carrera de 800 metros.
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 |
*------+---------------*/
Consultar campos de tipo ARRAY en una estructura
También puedes obtener información de campos repetidos anidados. Por ejemplo, la siguiente declaración devuelve el corredor que ha hecho la vuelta más rápida en una carrera de 800 metros.
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 combinación cruzada y acoplar el array. Esto equivale a usar un CROSS JOIN explícito o el siguiente ejemplo, que usa un INNER JOIN explícito:
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 |
*------+-------------------------*/
Al aplanar las matrices con INNER JOIN, se excluyen las filas que tienen matrices vacías o NULL. Si quieres 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 |
*-------------+--------------------*/
Crear matrices
Puedes crear una matriz usando literales de matriz o funciones de matriz. Para obtener más información sobre cómo crear arrays, consulta Tipo de array.
Crear arrays a partir de subconsultas
Una tarea habitual en el trabajo con matrices consiste en convertir el resultado de una subconsulta en una matriz. En GoogleSQL, puedes hacerlo con la función ARRAY().
Por ejemplo, supongamos que se realiza la siguiente operación 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 empieza con una tabla llamada Sequences. Esta tabla contiene una columna, some_numbers, de tipo ARRAY<INT64>.
La propia consulta contiene una subconsulta. Esta subconsulta selecciona cada fila de la columna some_numbers y usa UNNEST para devolver la matriz como un conjunto de filas. A continuación, multiplica cada valor por dos y vuelve a combinar las filas en una matriz mediante el operador ARRAY().
Filtrar arrays
En el siguiente ejemplo se usa una cláusula WHERE en la subconsulta del operador ARRAY() para filtrar las filas devueltas.
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, ya que los elementos de la fila original correspondiente ([5, 10]) no cumplen el requisito del filtro x < 5.
También puedes filtrar arrays con SELECT DISTINCT para devolver solo los elementos únicos 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 filas de arrays con la palabra clave IN. Esta palabra clave filtra las filas que contienen las matrices mediante la determinación de si un valor específico coincide con un elemento en la matriz.
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] |
| [] |
*--------------------*/
Observe de nuevo que la tercera fila contiene un array vacío, ya que el array de la fila original correspondiente ([5, 10]) no contenía 2.
Analizar arrays
Para comprobar si una matriz contiene un valor específico, usa el operador IN con UNNEST. Para comprobar si una matriz contiene un valor que cumple una condición, usa el operador EXISTS con UNNEST.
Análisis en busca de valores específicos
Para analizar una matriz de un valor específico, utiliza el operador IN con UNNEST.
Ejemplo
En el siguiente ejemplo se devuelve true si la matriz contiene el número 2.
SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;
/*----------------*
| contains_value |
+----------------+
| true |
*----------------*/
Para devolver las filas de una tabla en las que la columna de matriz contiene un valor específico, filtra los resultados de IN UNNEST con la cláusula WHERE.
Ejemplo
En el siguiente ejemplo se devuelve el valor id de las filas en las que la columna de la matriz 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 |
*---------------*/
Análisis en busca de valores que cumplen con una condición
Para analizar una matriz en busca de valores que cumplen con una condición, utiliza UNNEST para devolver una tabla de los elementos en la matriz, utiliza WHERE para filtrar la tabla resultante en una subconsulta y utiliza EXISTS para comprobar si la tabla filtrada contiene filas.
Ejemplo
En el siguiente ejemplo se devuelve el valor id de las filas en las que la columna de la matriz contiene valores superiores a 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 |
*---------------*/
Buscar valores de campo STRUCT que cumplan una condición
Para buscar en una matriz de valores STRUCT un campo cuyo valor coincida con una condición, usa UNNEST para devolver una tabla con una columna por cada campo STRUCT y, a continuación, filtra las filas que no coincidan de la tabla con WHERE EXISTS.
Ejemplo
En el siguiente ejemplo se devuelven las filas en las que la columna de la matriz contiene un STRUCT cuyo campo b tiene un valor superior a 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 una matriz mediante 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] |
*-----------------------*/
La matriz devuelta por ARRAY_AGG() está en un orden arbitrario, ya que no se garantiza el orden en el que la función concatena los valores. Para ordenar los elementos de la matriz, 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 una matriz. Por ejemplo, la siguiente consulta devuelve la suma de los elementos de la matriz de 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 matriz en las 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] |
*--------------------------------------------------*/
Convertir arrays en cadenas
La función ARRAY_TO_STRING() te permite convertir un ARRAY<STRING> en un solo valor STRING o un ARRAY<BYTES> en un solo valor BYTES, donde el valor resultante es la concatenación ordenada de los elementos de la matriz.
El segundo argumento es el separador que la función insertará entre las entradas para generar la salida. Este segundo argumento debe ser del mismo tipo que los elementos del primer argumento.
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 sustituye a los valores de NULL en la matriz de entrada.
Si omite este argumento, la función ignorará los elementos del array
NULL.Si proporcionas una cadena vacía, la función inserta un separador para los elementos de la matriz
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 |
*------------------+--------------+---------*/
Combinar matrices
En algunos casos, puede que quieras combinar varias matrices en una sola.
Para ello, puedes 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] |
*--------------------------------------------------*/
Actualizar arrays
Consideremos la siguiente tabla, llamada arrays_table. La primera columna de la tabla es una matriz de números enteros y la segunda columna contiene dos matrices de números enteros anidadas.
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 las matrices de una tabla con la instrucción UPDATE. En el siguiente ejemplo, se inserta el número 5 en la columna regular_array y se insertan 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] |
*---------------*---------------------------*----------------------------*/
Comprimir arrays
Dadas dos matrices del mismo tamaño, puedes combinarlas en una sola matriz que conste de pares de elementos de las matrices de entrada, tomados de sus posiciones correspondientes. Esta operación a veces se denomina compresión.
Puedes comprimir arrays con UNNEST y WITH OFFSET. En este ejemplo, cada par de valores se almacena como un STRUCT en una matriz.
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 matrices de entrada de diferentes longitudes siempre que la primera matriz sea igual o menor que la longitud de la segunda matriz. La longitud de la matriz comprimida será la de la matriz de entrada más corta.
Para obtener un array comprimido que incluya todos los elementos aunque los arrays de entrada tengan longitudes diferentes, cambia LEAST por GREATEST. Los elementos de cualquiera de las dos matrices que no tengan ningún elemento asociado en la otra matriz se emparejará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 }] |
*-------------------------------*/
Construir matrices de matrices
GoogleSQL no admite la creación directa de matrices de matrices. En su lugar, debes crear una matriz de structs, donde cada struct contenga un campo de tipo ARRAY. Para ilustrarlo, consulta la siguiente tabla:Points
/*----------*
| point |
+----------+
| [1, 5] |
| [2, 8] |
| [3, 7] |
| [4, 1] |
| [5, 7] |
*----------*/
Supongamos que quieres crear una matriz que contenga cada point de la tabla Points. Para ello, envuelve la matriz devuelta de cada fila en un 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 |
+-------------------+
| [{point: [1,5]}, |
| {point: [2,8]}, |
| {point: [5,7]}, |
| {point: [3,7]}, |
| {point: [4,1]}] |
*-------------------*/