No GoogleSQL para BigQuery, uma matriz é uma lista ordenada composta por zero ou mais valores do mesmo tipo de dados. Pode construir matrizes de um tipo de dados simples, como INT64, ou um tipo de dados complexo, como STRUCT. No entanto, não são suportadas matrizes de matrizes. Para saber mais acerca do ARRAY tipo de dados, incluindo o NULL processamento, consulte Tipo de matriz.
Com o GoogleSQL, pode construir literais de matrizes, criar matrizes a partir de subconsultas através da função ARRAY e agregar valores numa matriz através da função ARRAY_AGG.
Pode combinar matrizes através de funções como ARRAY_CONCAT() e converter matrizes em strings através de ARRAY_TO_STRING().
Aceder a elementos de matriz
Considere a seguinte tabela denominada Sequences. Esta tabela contém a coluna some_numbers do tipo de dados 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 aceder a elementos de matriz na coluna some_numbers, especifique que tipo de indexação quer usar: index ou OFFSET(index) para índices baseados em zero, ou ORDINAL(index) para índices baseados em um.
Por exemplo:
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 | *--------------------+---------+----------+-----------*/ Encontrar comprimentos
A função ARRAY_LENGTH devolve o comprimento de uma 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 | *--------------------+--------*/ Converter elementos numa matriz em linhas numa tabela
Para converter um ARRAY num conjunto de linhas, também conhecido como "aplanamento", use o operador UNNEST. UNNEST recebe um ARRAY e devolve uma tabela com uma única linha para cada elemento no ARRAY.
Uma vez que UNNEST destrói a ordem dos elementos ARRAY, pode querer restaurar a ordem da tabela. Para tal, use a cláusula WITH OFFSET opcional para devolver uma coluna adicional com o desvio de cada elemento da matriz, em seguida, use a cláusula ORDER BY para ordenar as linhas pelo respetivo desvio.
Exemplo
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 reduzir uma coluna inteira do tipo ARRAY, mantendo os valores das outras colunas em cada linha, use um INNER JOIN correlacionado para juntar a tabela que contém a coluna ARRAY à saída UNNEST dessa coluna ARRAY.
Com uma junção correlacionada, o operador UNNEST faz referência à coluna com tipo ARRAY de cada linha na tabela de origem, que aparece anteriormente na cláusula FROM. Para cada linha N na tabela de origem, UNNEST reduz a hierarquia de ARRAY da linha N a um conjunto de linhas que contêm os elementos ARRAY e, em seguida, uma função INNER JOIN ou CROSS JOIN correlacionada combina este novo conjunto de linhas com a linha única N da tabela de origem.
Exemplos
O exemplo seguinte usa UNNEST para devolver uma linha para cada elemento na coluna de matriz. Devido a INNER JOIN, a coluna id contém os valores id para a linha em Sequences que contém 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 | *------+-------------------*/ Tenha em atenção que, para as junções correlacionadas, o operador UNNEST é opcional e a INNER JOIN pode ser expressa como uma CROSS JOIN ou uma junção cruzada separada por vírgulas. Usando a notação abreviada de junção cruzada com vírgula, o exemplo anterior é consolidado da seguinte forma:
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 matrizes aninhadas
Se uma tabela contiver um ARRAY de STRUCTs, pode reduzir o ARRAY para consultar os campos do STRUCT. Também pode reduzir os campos do tipo ARRAY dos valores STRUCT.
Consultar elementos STRUCT numa matriz
O exemplo seguinte usa UNNEST com INNER JOIN para reduzir um 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]} | *------+---------------------------------------*/ Pode encontrar informações específicas de campos repetidos. Por exemplo, a consulta seguinte devolve o corredor mais rápido numa corrida de 800 m.
Exemplo
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 do tipo ARRAY numa struct
Também pode obter informações de campos repetidos aninhados. Por exemplo, a seguinte declaração devolve o corredor que teve a volta mais rápida numa corrida 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 | *------+-------------------------*/ Repare que a consulta anterior usa o operador de vírgula (,) para fazer uma junção cruzada e achatar a matriz. Isto é equivalente a usar um CROSS JOIN explícito ou o exemplo seguinte, que usa um 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 | *------+-------------------------*/ A redução de matrizes com INNER JOIN exclui linhas que tenham matrizes vazias ou NULL Se quiser incluir estas linhas, use 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 | *-------------+--------------------*/ Criar matrizes
Pode construir uma matriz usando literais de matriz ou funções de matriz. Para saber mais sobre a criação de matrizes, consulte o tipo de matriz.
Criar matrizes a partir de subconsultas
Uma tarefa comum quando trabalha com matrizes é transformar o resultado de uma subconsulta numa matriz. No GoogleSQL, pode fazê-lo através da função ARRAY().
Por exemplo, considere a seguinte operação na tabela 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 exemplo começa com uma tabela denominada Sequences. Esta tabela contém uma coluna, some_numbers, do tipo ARRAY<INT64>.
A própria consulta contém uma subconsulta. Esta subconsulta seleciona cada linha na coluna some_numbers e usa UNNEST para devolver a matriz como um conjunto de linhas. Em seguida, multiplica cada valor por dois e, de seguida, volta a combinar as linhas numa matriz através do operador ARRAY().
Filtrar matrizes
O exemplo seguinte usa uma cláusula WHERE na subconsulta do operador ARRAY() para filtrar as linhas devolvidas.
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] | | [] | *------------------------*/ Repare que a terceira linha contém uma matriz vazia, porque os elementos na linha original correspondente ([5, 10]) não cumpriram o requisito do filtro de x < 5.
Também pode filtrar matrizes usando SELECT DISTINCT para devolver apenas elementos únicos numa matriz.
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] | *-----------------*/ Também pode filtrar linhas de matrizes através da palavra-chave IN. Esta palavra-chave filtra linhas que contêm matrizes determinando se um valor específico corresponde a um elemento na 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] | | [] | *--------------------*/ Repare novamente que a terceira linha contém uma matriz vazia, porque a matriz na linha original correspondente ([5, 10]) não continha 2.
Analisar matrizes
Para verificar se uma matriz contém um valor específico, use o operador IN com UNNEST. Para verificar se uma matriz contém um valor que corresponde a uma condição, use o operador EXISTS com UNNEST.
Procura de valores específicos
Para procurar um valor específico numa matriz, use o operador IN com UNNEST.
Exemplo
O exemplo seguinte devolve true se a matriz contiver o número 2.
SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value; /*----------------* | contains_value | +----------------+ | true | *----------------*/ Para devolver as linhas de uma tabela onde a coluna da matriz contém um valor específico, filtre os resultados de IN UNNEST usando a cláusula WHERE.
Exemplo
O exemplo seguinte devolve o valor id para as linhas em que a coluna da matriz contém o 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 | *---------------*/ Procura valores que satisfaçam uma condição
Para analisar uma matriz em busca de valores que correspondam a uma condição, use UNNEST para devolver uma tabela dos elementos na matriz, use WHERE para filtrar a tabela resultante numa subconsulta e use EXISTS para verificar se a tabela filtrada contém linhas.
Exemplo
O exemplo seguinte devolve o valor id para as linhas em que a coluna da matriz contém 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 | *---------------*/ Procura valores de campos STRUCT que satisfazem uma condição
Para pesquisar uma matriz de valores STRUCT para um campo cujo valor corresponde a uma condição, use UNNEST para devolver uma tabela com uma coluna para cada campo STRUCT e, em seguida, filtre as linhas que não correspondem da tabela usando WHERE EXISTS.
Exemplo
O exemplo seguinte devolve as linhas em que a coluna da matriz contém um STRUCT cujo campo b tem um 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 | *---------------*/ Matrizes e agregação
Com o GoogleSQL, pode agregar valores numa matriz usando 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] | *-----------------------*/ A matriz devolvida por ARRAY_AGG() está numa ordem arbitrária, uma vez que a ordem em que a função concatena os valores não é garantida. Para ordenar os elementos da matriz, use ORDER BY. Por exemplo:
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] | *-----------------------*/ Também pode aplicar funções de agregação, como SUM(), aos elementos numa matriz. Por exemplo, a seguinte consulta devolve a soma dos elementos da matriz para cada linha da tabela 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 | *--------------------+------*/ O GoogleSQL também suporta uma função de agregação, ARRAY_CONCAT_AGG(), que concatena os elementos de uma coluna de matriz em várias linhas.
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] | *--------------------------------------------------*/ Converter matrizes em strings
A função ARRAY_TO_STRING() permite-lhe converter uma ARRAY<STRING> num valor STRING único ou uma ARRAY<BYTES> num valor BYTES único em que o valor resultante é a concatenação ordenada dos elementos da matriz.
O segundo argumento é o separador que a função vai inserir entre as entradas para produzir o resultado. Este segundo argumento tem de ser do mesmo tipo que os elementos do primeiro argumento.
Exemplo:
WITH Words AS (SELECT ["Hello", "World"] AS greeting) SELECT ARRAY_TO_STRING(greeting, " ") AS greetings FROM Words; /*-------------* | greetings | +-------------+ | Hello World | *-------------*/ O terceiro argumento opcional substitui os valores NULL na matriz de entrada.
Se omitir este argumento, a função ignora os elementos da matriz
NULL.Se fornecer uma string vazia, a função insere um separador para os elementos da matriz
NULL.
Exemplo:
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 matrizes
Em alguns casos, pode querer combinar várias matrizes numa única matriz. Pode fazê-lo através da função ARRAY_CONCAT().
SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) AS count_to_six; /*--------------------------------------------------* | count_to_six | +--------------------------------------------------+ | [1, 2, 3, 4, 5, 6] | *--------------------------------------------------*/ Atualizar matrizes
Considere a seguinte tabela denominada arrays_table. A primeira coluna na tabela é uma matriz de números inteiros e a segunda coluna contém duas matrizes aninhadas de números inteiros.
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] | *---------------*---------------------------*----------------------------*/ Pode atualizar matrizes numa tabela através da declaração UPDATE. O exemplo seguinte insere o número 5 na coluna regular_array e insere os elementos do campo first_array da coluna nested_arrays no 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 matrizes
Dadas duas matrizes de tamanho igual, pode uni-las numa única matriz constituída por pares de elementos das matrizes de entrada, retirados das respetivas posições correspondentes. Por vezes, esta operação é denominada compressão.
Pode comprimir matrizes com UNNEST e WITH OFFSET. Neste exemplo, cada par de valores é armazenado como um STRUCT numa 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 }] | *------------------------------*/ Pode usar matrizes de entrada de comprimentos diferentes, desde que o comprimento da primeira matriz seja igual ou inferior ao comprimento da segunda matriz. A matriz comprimida tem o comprimento da matriz de entrada mais curta.
Para obter uma matriz comprimida que inclua todos os elementos, mesmo quando as matrizes de entrada têm comprimentos diferentes, altere LEAST para GREATEST. Os elementos de qualquer uma das matrizes que não tenham um elemento associado na outra matriz são associados a 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 }] | *-------------------------------*/ Criar matrizes de matrizes
O GoogleSQL não suporta a criação direta de matrizes de matrizes. Em alternativa, tem de criar uma matriz de structs, com cada struct a conter um campo do tipo ARRAY. Para ilustrar isto, considere a seguinte tabela Points:
/*----------* | point | +----------+ | [1, 5] | | [2, 8] | | [3, 7] | | [4, 1] | | [5, 7] | *----------*/ Agora, suponhamos que quer criar uma matriz composta por cada point na tabela Points. Para o fazer, envolva a matriz devolvida de cada linha num STRUCT, conforme mostrado abaixo.
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]}] | *-------------------*/