Algumas ou todas as informações nesta página podem não se aplicar à Trusted Cloud by S3NS. Consulte o artigo Diferenças em relação ao Google Cloud para mais detalhes.

Esta página foi traduzida pela API Cloud Translation.

Trabalhe com matrizes

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 de 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]}]  |
 *-------------------*/

Trabalhe com matrizes

Aceder a elementos de matriz

Encontrar comprimentos

Converter elementos numa matriz em linhas numa tabela

Consultar matrizes aninhadas

Consultar elementos STRUCT numa matriz

Consultar campos do tipo ARRAY numa struct

Criar matrizes

Criar matrizes a partir de subconsultas

Filtrar matrizes

Analisar matrizes

Procura de valores específicos

Procura valores que satisfaçam uma condição

Procura valores de campos STRUCT que satisfazem uma condição

Matrizes e agregação

Converter matrizes em strings

Combinar matrizes

Atualizar matrizes

Comprimir matrizes

Criar matrizes de matrizes

Consultar elementos `STRUCT` numa matriz

Consultar campos do tipo `ARRAY` numa struct

Procura valores de campos `STRUCT` que satisfazem uma condição