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 un tipo de datos simple, como INT64, o de un tipo de datos complejo, como STRUCT. Sin embargo, no se admiten los arrays de arrays. 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 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 tipo ARRAY y conservar los valores de las otras columnas en cada fila, debes usar una INNER JOIN correlacionada 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, una operación correlacionada INNER JOIN o CROSS JOIN combina este conjunto de filas nuevo con la única 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 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 la INNER JOIN se puede expresar como una CROSS JOIN o una unión cruzada de comas. Con la notación abreviada 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 compactar 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 | *------+-------------------------*/ Si compactas los arrays con INNER JOIN, se excluyen las filas que tienen arrays vacíos o NULL. 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 valores 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 devuelve ARRAY_AGG() está en un orden arbitrario, ya que el orden en el que la función concatena los 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 un 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 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]}] | *-------------------*/