No GoogleSQL para BigQuery, uma matriz é uma lista ordenada que consiste em zero ou mais valores do mesmo tipo de dados. É possível criar matrizes de um tipo de dados simples, como INT64, ou um tipo de dados complexo, como STRUCT. No entanto, não é possível usar matrizes de matrizes. Para saber mais sobre o tipo de dados ARRAY, incluindo o gerenciamento de NULL, consulte Tipo de matriz.
Com o GoogleSQL, é possível criar literais de matriz e matrizes a partir de subconsultas por meio da função ARRAY, além de agregar valores a uma matriz por meio da função ARRAY_AGG.
É possível combinar matrizes usando funções como ARRAY_CONCAT() e convertê-las em strings por meio de ARRAY_TO_STRING().
Como acessar os elementos da matriz
Considere a tabela a seguir chamada Sequences. Essa 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 acessar elementos de matriz na coluna some_numbers, especifique qual tipo de indexação você 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 | *--------------------+---------+----------+-----------*/ Como encontrar comprimentos
A função ARRAY_LENGTH retorna 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 | *--------------------+--------*/ Como converter elementos em uma matriz para linhas de uma tabela
Para converter um ARRAY em um conjunto de linhas, processo conhecido também como "nivelamento", use o operador UNNEST. UNNEST usa uma ARRAY e retorna uma tabela com uma única linha para cada elemento na ARRAY.
Como UNNEST destrói a ordem dos elementos da ARRAY, pode ser necessário restaurar essa ordem na tabela. Para fazer isso, use a cláusula opcional WITH OFFSET para retornar uma coluna adicional com o deslocamento de cada elemento da matriz. Em seguida, use a cláusula ORDER BY para ordenar as linhas pelo deslocamento delas.
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 nivelar uma coluna inteira do tipo ARRAY enquanto preserva os valores das outras colunas em cada linha, use uma INNER JOIN correlacionada para mesclar a tabela que contém a coluna ARRAY com a saída UNNEST dessa coluna ARRAY.
Com uma correlaçã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 nivela a ARRAY da linha N em um conjunto de linhas contendo os elementos da ARRAY. Em seguida, uma INNER JOIN ou CROSS JOIN correlacionada combina esse novo conjunto de linhas com a única linha N da tabela de origem.
Exemplos
No exemplo a seguir, usamos UNNEST para retornar uma linha de cada elemento na coluna da matriz. Por causa do 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 | *------+-------------------*/ Para correlações, o operador UNNEST é opcional e o INNER JOIN pode ser expresso como um CROSS JOIN ou uma correlação implícita. Usando a notação abreviada de junção cruzada com vírgula, o exemplo anterior é consolidado da seguinte maneira:
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 | *------+-------------------*/ Como consultar matrizes aninhadas
Se uma tabela contém uma ARRAY de STRUCTs, é possível nivelar a ARRAY para consultar os campos de STRUCT. Também é possível nivelar campos do tipo ARRAY de valores STRUCT.
Como consultar elementos STRUCT em uma matriz
O exemplo a seguir usa UNNEST com INNER JOIN para nivelar uma 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]} | *------+---------------------------------------*/ É possível encontrar informações específicas a partir de campos repetidos. Por exemplo, a consulta a seguir retorna o corredor mais rápido em uma 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 | *------+---------------*/ Como consultar campos do tipo ARRAY em um struct
Também é possível encontrar informações em campos repetidos aninhados. Por exemplo, a declaração a seguir retorna o corredor que teve a volta mais rápida em uma 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 | *------+-------------------------*/ Perceba que a consulta anterior usa o operador vírgula (,) para realizar uma junção cruzada e simplificar a matriz. Isso equivale a usar um CROSS JOIN explícito ou o exemplo a seguir, 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 | *------+-------------------------*/ Nivelar matrizes com INNER JOIN exclui linhas que têm matrizes vazias ou NULL. Se você quiser incluir essas 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 | *-------------+--------------------*/ Como construir matrizes
Para construir um ARRAY, use literais de matriz ou funções de matriz. Para saber mais sobre como criar matrizes, consulte Tipo de matriz.
Como criar matrizes a partir de subconsultas
Uma tarefa comum ao trabalhar com matrizes é transformar o resultado de uma subconsulta em uma matriz. No BigQuery, é possível fazer isso usando a função ARRAY().
Por exemplo, veja 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] | *--------------------+---------------------*/ Esse exemplo inicia com uma tabela chamada "sequences". Essa tabela contém uma coluna some_numbers, do tipo ARRAY<INT64>.
A própria consulta contém uma subconsulta. Essa subconsulta seleciona cada linha na coluna some_numbers e usa UNNEST para retornar a matriz como um conjunto de linhas. A seguir, ela multiplica cada valor por dois e, em seguida, recombina as linhas em uma matriz usando o operador ARRAY().
Como filtrar matrizes
O exemplo a seguir usa uma cláusula WHERE na subconsulta do operador ARRAY() para filtrar as linhas retornadas.
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] | | [] | *------------------------*/ Perceba que a terceira linha contém uma matriz vazia, porque os elementos na linha original correspondente ([5, 10]) não atenderam ao requisito x < 5 do filtro.
Também é possível filtrar matrizes usando SELECT DISTINCT para retornar somente elementos únicos dentro de uma 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 é possível usar a palavra-chave IN para filtrar linhas de matrizes. Essa palavra-chave filtra linhas contendo 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] | | [] | *--------------------*/ Mais uma vez, perceba que a terceira linha contém uma matriz vazia, porque a matriz na linha original correspondente ([5, 10]) não continha 2.
Como verificar informações das matrizes
Para verificar se uma matriz contém um valor específico, use o operador IN com UNNEST. Se você quiser verificar se uma matriz contém um valor correspondente a uma condição, use a função EXISTS com UNNEST.
Como verificar valores específicos
Para verificar se há um valor específico em uma matriz, use o operador IN com UNNEST.
Exemplo
O exemplo a seguir retorna true se a matriz contém o número 2.
SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value; /*----------------* | contains_value | +----------------+ | true | *----------------*/ Para retornar as linhas de uma tabela em que a coluna da matriz contém um valor específico, filtre os resultados de IN UNNEST usando a cláusula WHERE.
Exemplo
O exemplo a seguir retorna o valor de id referente às 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 | *---------------*/ Como verificar valores que atendem a uma condição
Para verificar se uma matriz contém valores que correspondem a uma condição, use UNNEST para retornar uma tabela dos elementos na matriz, WHERE para filtrar a tabela resultante em uma subconsulta e EXISTS para verificar se a tabela filtrada contém alguma linha.
Exemplo
O exemplo a seguir retorna o valor de id referente às linhas em que a coluna da matriz contém valores maiores 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 | *---------------*/ Como verificar valores de campos STRUCT que atendem a uma condição
Para pesquisar se uma matriz de valores STRUCT contém um campo com o valor correspondente a uma condição, use UNNEST para retornar uma tabela com uma coluna para cada campo STRUCT e, em seguida, filtre as linhas não correspondentes da tabela usando WHERE EXISTS.
Exemplo
O exemplo a seguir retorna as linhas em que a coluna da matriz contém um STRUCT com o campo b tendo um valor maior 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 | *---------------*/ Matrizes e agregação
Com o GoogleSQL, é possível agregar valores em uma 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 retornada por ARRAY_AGG() está ordenada arbitrariamente, já que a ordem na qual 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 é possível aplicar funções de agregação, como SUM(), aos elementos de uma matriz. Por exemplo, a seguinte consulta retorna a soma dos elementos de matriz em 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 permite uma função de agregação, ARRAY_CONCAT_AGG(), que concatena os elementos de uma coluna da matriz em todas as 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] | *--------------------------------------------------*/ Como converter matrizes em strings
A função ARRAY_TO_STRING() permite que você converta uma ARRAY<STRING> em um único valor STRING ou uma ARRAY<BYTES> em um único valor BYTES, 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 entradas para produzir a saída. Esse segundo argumento precisa 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 toma o lugar dos valores NULL na matriz de entrada.
Se você omitir esse argumento, a função ignorará os elementos da matriz
NULL.Se você fornecer uma string vazia, a função inserirá um separador para 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 | *------------------+--------------+---------*/ Como combinar matrizes
Em alguns casos, convém combinar várias matrizes em apenas uma. É possível fazer isso usando a 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] | *--------------------------------------------------*/ Como atualizar matrizes
Considere a tabela a seguir chamada arrays_table. A primeira coluna na tabela é uma matriz de números inteiros, e a segunda 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] | *---------------*---------------------------*----------------------------*/ É possível atualizar matrizes em uma tabela usando a instrução UPDATE. O exemplo a seguir insere o número 5 na coluna regular_array e 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] | *---------------*---------------------------*----------------------------*/ Como compactar matrizes
Com duas matrizes de tamanho igual, é possível mesclá-las em uma única matriz composta por pares de elementos de matrizes de entrada, obtidos das posições correspondentes delas. Essa operação às vezes é chamada de compactação.
É possível compactar matrizes com UNNEST e WITH OFFSET. Neste exemplo, cada par de valores é armazenado como STRUCT em uma 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 }] | *------------------------------*/ É possível usar matrizes de entrada de comprimentos diferentes, desde que a primeira matriz seja do mesmo tamanho ou menor que a segunda matriz. A matriz compactada terá o tamanho da matriz de entrada menor.
Para ter uma matriz compactada que inclua todos os elementos, mesmo que as matrizes de entrada tenham comprimentos diferentes, altere LEAST para GREATEST. Os elementos de qualquer matriz que não tenham elementos associados na outra matriz serão pareados com 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 }] | *-------------------------------*/ Como criar matrizes de outras matrizes
O GoogleSQL não é compatível com a criação direta de matrizes de outras matrizes. Em vez disso, você cria uma matriz de structs. Cada struct contém um campo do tipo ARRAY. Para entender melhor, pense na tabela Points a seguir:
/*----------* | point | +----------+ | [1, 5] | | [2, 8] | | [3, 7] | | [4, 1] | | [5, 7] | *----------*/ Agora, suponha que você queira criar uma matriz composta de cada point na tabela Points. Para fazer isso, una a matriz retornada de cada linha em uma 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]}] | *-------------------*/