Como trabalhar com matrizes

No Cloud Spanner SQL, uma matriz é uma lista ordenada composta de zero ou mais valores do mesmo tipo de dados. Você pode criar matrizes de tipos de dados simples, como INT64, e complexos, como STRUCTs. A exceção atual é o tipo de dados ARRAY: as matrizes de outras matrizes não são compatíveis.

Com o Cloud Spanner SQL, é possível construir literais de matriz, construir matrizes de subconsultas usando a função ARRAY e agregar valores em uma matriz usando a função ARRAY_AGG.

É possível combinar matrizes usando funções, como ARRAY_CONCAT(), e convertê-las em strings usando ARRAY_TO_STRING().

Como construir matrizes

Como usar literais de matrizes

Você pode criar um literal de matriz em Cloud Spanner SQL usando colchetes ([ e ]). Cada elemento em uma matriz é separado por uma vírgula.

SELECT [1, 2, 3] as numbers;

SELECT ["apple", "pear", "orange"] as fruit;

SELECT [true, false, true] as booleans;

Também é possível criar matrizes a partir de quaisquer expressões que tenham tipos compatíveis. Por exemplo:

SELECT [a, b, c]
FROM
  (SELECT 5 AS a,
          37 AS b,
          406 AS c);

SELECT [a, b, c]
FROM
  (SELECT CAST(5 AS INT64) AS a,
          CAST(37 AS FLOAT64) AS b,
          406 AS c);

Perceba que o segundo exemplo contém três expressões: uma que retorna INT64, uma que retorna FLOAT64 e outra que declara um literal. Essa expressão funciona porque todas as três expressões compartilham FLOAT64 como um supertipo.

Para declarar um tipo de dados específico para uma matriz, use parênteses angulares (< e >). Por exemplo:

SELECT ARRAY<FLOAT64>[1, 2, 3] as floats;

Matrizes compostas da maioria dos tipos de dados, como INT64 ou STRING, não exigem que você as declare primeiro.

SELECT [1, 2, 3] as numbers;

Você pode escrever uma matriz vazia de um tipo específico usando ARRAY<type>[]. Também pode escrever uma matriz vazia não tipada usando [], no caso em que o Cloud Spanner SQL tentar inferir o tipo de matriz do contexto envolvente. Se o Cloud Spanner SQL não puder inferir um tipo, o tipo padrão ARRAY<INT64> é usado.

Como usar valores gerados

Você também pode construir um ARRAY com valores gerados.

Como gerar matrizes de números inteiros

A função GENERATE_ARRAY gera uma matriz de valores a partir de um valor inicial e final e um valor de etapa. Por exemplo, a consulta a seguir gera uma matriz que contém todos os inteiros ímpares de 11 a 33 (ambos inclusos):

SELECT GENERATE_ARRAY(11, 33, 2) AS odds;

+--------------------------------------------------+
| odds                                             |
+--------------------------------------------------+
| [11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33] |
+--------------------------------------------------+

Você também pode gerar uma matriz de valores em ordem decrescente, dando um valor de etapa negativo:

SELECT GENERATE_ARRAY(21, 14, -1) AS countdown;

+----------------------------------+
| countdown                        |
+----------------------------------+
| [21, 20, 19, 18, 17, 16, 15, 14] |
+----------------------------------+

Como gerar matrizes de datas

GENERATE_DATE_ARRAY gera uma matriz de DATEs a partir de uma DATE inicial e final e um passo INTERVAL.

É possível gerar um conjunto de valores de DATE usando GENERATE_DATE_ARRAY. Por exemplo, esta consulta retorna a DATE atual e os valores de DATEs seguintes em intervalos de uma WEEK até uma DATE posterior, que estará inclusa:

SELECT
  GENERATE_DATE_ARRAY('2017-11-21', '2017-12-31', INTERVAL 1 WEEK)
    AS date_array;
+--------------------------------------------------------------------------+
| date_array                                                               |
+--------------------------------------------------------------------------+
| [2017-11-21, 2017-11-28, 2017-12-05, 2017-12-12, 2017-12-19, 2017-12-26] |
+--------------------------------------------------------------------------+

Como acessar os elementos da matriz

Pense na seguinte tabela sequences:

CREATE TABLE sequences (
  id INT64 NOT NULL,
  some_numbers ARRAY<INT64> NOT NULL
) PRIMARY KEY(id);

Suponha que a tabela seja preenchida com as seguintes linhas:

+----+---------------------+
| id | some_numbers        |
+----+---------------------+
| 1  | [0, 1, 1, 2, 3, 5]  |
| 2  | [2, 4, 8, 16, 32]   |
| 3  | [5, 10]             |
+----+---------------------+

Essa tabela contém a coluna some_numbers do tipo de dados ARRAY. Para acessar elementos das matrizes nessa coluna, é necessário especificar que tipo de indexação você quer usar: OFFSET para índices baseados em "zero" ou ORDINAL para índices baseados em "um".

Esta consulta mostra como usar OFFSET() e ORDINAL():

SELECT some_numbers,
       some_numbers[OFFSET(1)] AS offset_1,
       some_numbers[ORDINAL(1)] AS ordinal_1
FROM sequences;

+---------------+----------+-----------+
| some_numbers  | offset_1 | ordinal_1 |
+---------------+----------+-----------+
| [0,1,1,2,3,5] |        1 |         0 |
+---------------+----------+-----------+
| [2,4,8,16,32] |        4 |         2 |
+---------------+----------+-----------+
| [5,10]        |       10 |         5 |
+---------------+----------+-----------+

Como procurar comprimentos

A função ARRAY_LENGTH() retorna o comprimento de uma matriz.

Aqui está um exemplo de consulta, assumindo a mesma definição da tabela sequences conforme exibido acima, com as mesmas linhas de amostra:

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 nivelar matrizes

Para converter uma 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 de ARRAYs enquanto preserva os valores das outras colunas em cada linha, use um CROSS JOIN para unir a tabela que contém a coluna ARRAY à saída UNNEST dessa coluna ARRAY.

Essa é uma correlação: o operador UNNEST faz referência à coluna de ARRAYs a partir 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 a partir da linha N em um conjunto de linhas contendo os elementos da ARRAY. Em seguida, CROSS JOIN une esse novo conjunto de linhas com a única linha N da tabela de origem.

Exemplo

O exemplo a seguir usa UNNEST para retornar uma linha para cada elemento na coluna da matriz. Por causa do CROSS JOIN, a coluna id contém os valores id para a linha em sequences que contém cada número.

SELECT id, flattened_numbers FROM sequences
CROSS 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 |
+------+-------------------+

Como consultar matrizes aninhadas

Se uma tabela tiver um ARRAY de STRUCTs, será possível nivelar o ARRAY para consultar os campos do STRUCT. Também é possível nivelar campos do tipo ARRAY de valores STRUCT.

Como consultar elementos STRUCT em uma ARRAY

O exemplo a seguir usa UNNEST com CROSS JOIN para nivelar uma ARRAY de STRUCTs.

SELECT race,
       participant.name,
       participant.splits
FROM
  (SELECT "800M" AS race,
    [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] AS splits),
     STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
     STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
     STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
     STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
     STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
     STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
     STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] as splits)]
     AS participants
  ) AS r
CROSS JOIN UNNEST(r.participants) AS participant;

+------+-------------+-----------------------+
| race | name        | splits                |
+------+-------------+-----------------------+
| 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.

Esse exemplo não envolve o nivelamento de uma matriz, mas representa um método comum para coletar informações a partir de um campo repetido.

Exemplo

SELECT race,
       (SELECT name
        FROM UNNEST(participants)
        ORDER BY (
          SELECT SUM(duration)
          FROM UNNEST(splits) AS duration) ASC
          LIMIT 1) AS fastest_racer
FROM
  (SELECT "800M" AS race,
    [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] AS splits),
     STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
     STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
     STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
     STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
     STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
     STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
     STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] as splits)]
     AS participants
  ) AS r;

+------+---------------+
| race | fastest_racer |
+------+---------------+
| 800M | Rudisha       |
+------+---------------+

Como consultar campos do tipo ARRAY em um STRUCT

Também é possível encontrar informações em campos aninhados. Por exemplo, a declaração a seguir retorna o corredor que teve a volta mais rápida em uma corrida de 800 m.

SELECT race,
       (SELECT name
        FROM UNNEST(participants),
          UNNEST(splits) AS duration
        ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM
  (SELECT "800M" AS race,
    [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] AS splits),
     STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
     STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
     STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
     STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
     STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
     STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
     STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] as splits)]
     AS participants
  ) AS r;

+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer                |
+------+-------------------------+

Perceba que a consulta anterior usa o operador vírgula (,) para executar um CROSS JOIN implícito. Isso é o equivalente ao exemplo a seguir, que usa um CROSS JOIN implícito.

SELECT race,
       (SELECT name
        FROM UNNEST(participants)
        CROSS JOIN UNNEST(splits) AS duration
        ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM
  (SELECT "800M" AS race,
    [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] AS splits),
     STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
     STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
     STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
     STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
     STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
     STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
     STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] as splits)]
     AS participants
  ) AS r;

+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer                |
+------+-------------------------+

Observe que o nivelamento de matrizes com CROSS JOIN exclui linhas que têm matrizes vazias ou NULL. Se você quiser incluir essas linhas, use LEFT JOIN.

SELECT
  name, sum(duration) as duration
FROM
  (SELECT "800M" AS race,
    [STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS splits),
     STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
     STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
     STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
     STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
     STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
     STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
     STRUCT("Nathan" as name, ARRAY<FLOAT64>[] as splits),
     STRUCT("David" as name, NULL as splits)]
     AS participants) AS races,
  races.participants LEFT JOIN participants.splits duration
GROUP BY name;

+-------------+--------------------+
| name        | duration           |
+-------------+--------------------+
| Murphy      | 102.9              |
| Rudisha     | 102.19999999999999 |
| David       | NULL               |
| Rotich      | 103.6              |
| Makhloufi   | 102.6              |
| Bosse       | 103.4              |
| Kipketer    | 106                |
| Nathan      | NULL               |
| Lewandowski | 104.2              |
+-------------+--------------------+

Como criar matrizes a partir de subconsultas

Uma tarefa comum ao trabalhar com matrizes é transformar o resultado de uma subconsulta em uma matriz. No Cloud Spanner SQL, você pode fazer isso usando a

função ARRAY().

Por exemplo, veja a seguinte operação na tabela sequences:

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, que 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.

Observação: nos exemplos a seguir, as linhas resultantes não estão ordenadas.

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 atendeu ao requisito x < 5 do filtro.

Você também pode filtrar matrizes usando SELECT DISTINCT para retornar somente elementos únicos dentro de uma matriz.

SELECT ARRAY(SELECT DISTINCT x
             FROM UNNEST(some_numbers) AS x) AS unique_numbers
FROM sequences
WHERE id = 1;

+----------------+
| unique_numbers |
+----------------+
| [0,1,2,3,5]    |
+----------------+

Também é possível filtrar linhas de matrizes usando a palavra-chave IN. Essa palavra-chave filtra linhas contendo matrizes determinando se um valor específico corresponde a um elemento na matriz.

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 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 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 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 a seguir retorna o valor de id referente às linhas em que a coluna da matriz contém o valor 2.

SELECT id AS matching_rows
FROM (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)
WHERE 2 IN UNNEST(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.

SELECT id AS matching_rows
FROM (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)
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 STRUCTs contém um campo cujo valor corresponde 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 uma STRUCT cujo campo b tenha um valor maior que 3.

SELECT id AS matching_rows
FROM (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)
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(some_numbers)
  WHERE b > 3
);

+---------------+
| matching_rows |
+---------------+
| 2             |
| 3             |
+---------------+

Matrizes e agregação

Com o Cloud Spanner SQL, você pode agregar valores em uma matriz usando ARRAY_AGG().

Pense na seguinte tabela fruits:

CREATE TABLE fruits (
  fruit STRING(MAX),
  id INT64 NOT NULL
) PRIMARY KEY(id);

Suponha que a tabela seja preenchida com os seguintes dados:

+----+--------------+
| id | fruit        |
+----+--------------+
| 1  | "apple"      |
| 2  | "pear"       |
| 3  | "banana"     |
+----+--------------+

Esta consulta mostra como usar ARRAY_AGG():

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.

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 de elementos da matriz para cada linha da tabela sequences.

SELECT some_numbers,
  (SELECT SUM(x)
   FROM UNNEST(s.some_numbers) x) AS sums
FROM sequences s;

+---------------+------+
| some_numbers  | sums |
+---------------+------+
| [0,1,1,2,3,5] |   12 |
+---------------+------+
| [2,4,8,16,32] |   62 |
+---------------+------+
| [5,10]        |   15 |
+---------------+------+

Como converter matrizes em strings

A função ARRAY_TO_STRING() permite que você converta ARRAY<STRING> em um único valor STRING ou 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:

SELECT ARRAY_TO_STRING(["Hello", "World"], " ") AS greetings;

+-------------+
| 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 NULL da matriz.

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. Isso pode ser feito 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 criar matrizes de outras matrizes

O Cloud Spanner SQL não é compatível com a criação direta de matrizes de matrizes. Em vez disso, é necessário criar uma matriz de structs. Cada struct precisa conter um campo do tipo ARRAY. Para entender melhor, pense na seguinte tabela points:

CREATE TABLE points (
  point ARRAY<INT64>,
  id INT64 NOT NULL
) PRIMARY KEY(id);

Suponha que a tabela seja preenchida com as seguintes linhas:

+----+----------+
| id | point    |
+----+----------+
| 1  | [1, 5]   |
| 2  | [2, 8]   |
| 3  | [3, 7]   |
| 4  | [4, 1]   |
| 5  | [5, 7]   |
+----+----------+

Agora, suponha que você queira criar uma matriz composta de cada point da tabela points. Para fazer isso, una a matriz retornada de cada linha em uma STRUCT, conforme mostrado abaixo.

SELECT ARRAY(
  SELECT STRUCT(point)
  FROM points)
  AS coordinates;

+--------------+
| coordinates  |
+--------------+
| point: [1,5] |
| point: [2,8] |
| point: [3,7] |
| point: [4,1] |
| point: [5,7] |
+--------------+
Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Documentação do Cloud Spanner