Utilizzare gli array

In GoogleSQL per BigQuery, un array è un elenco ordinato costituito da zero o più valori dello stesso tipo di dati. Puoi costruire array di tipi di dati semplici, ad esempio INT64 e tipi di dati complessi come i STRUCT. Lo stato attuale a questo è il tipo di dati ARRAY perché gli array di array non sono supportati. Per scoprire di più sul ARRAY tipo di dati, tra cui gestione di NULL, vedi Tipo di array.

Con GoogleSQL, puoi creare valori letterali di array, per creare array da sottoquery utilizzando Funzione ARRAY, e aggregare i valori in una matrice utilizzando ARRAY_AGG personalizzata.

Puoi combinare gli array utilizzando funzioni come ARRAY_CONCAT() e convertire gli array in stringhe utilizzando ARRAY_TO_STRING().

Accesso agli elementi dell'array

Considera la seguente tabella emulata denominata Sequences. Questa tabella contiene la colonna some_numbers del tipo di dati 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]             |
 *---------------------*/

Per accedere agli elementi dell'array nella colonna some_numbers, specifica quale tipo di indicizzazione che vuoi utilizzare: index o OFFSET(index) per indici in base zero o ORDINAL(index) per indici basati su uno.

Ad esempio:

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         |
 *--------------------+---------+----------+-----------*/

Nota: OFFSET e ORDINAL genereranno errori se l'indice è fuori da intervallo. Per evitare che ciò accada, puoi utilizzare SAFE_OFFSET o SAFE_ORDINAL per restituire NULL anziché segnalare un errore.

Durata dei risultati

La funzione ARRAY_LENGTH restituisce la lunghezza di un array.

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
       ARRAY_LENGTH(some_numbers) AS len
FROM Sequences;

/*--------------------+--------*
 | some_numbers       | len    |
 +--------------------+--------+
 | [0, 1, 1, 2, 3, 5] | 6      |
 | [2, 4, 8, 16, 32]  | 5      |
 | [5, 10]            | 2      |
 *--------------------+--------*/

Conversione degli elementi di un array in righe di una tabella

Per convertire un ARRAY in un insieme di righe, noto anche come "appiattimento", utilizza la UNNEST operatore. UNNEST prende ARRAY e restituisce una tabella con una singola riga per ogni elemento in ARRAY.

Poiché UNNEST distrugge l'ordine degli elementi ARRAY, potresti dover ripristinare l'ordine nella tabella. Per farlo, utilizza l'attributo facoltativo WITH OFFSET per restituire una colonna aggiuntiva con l'offset per ogni elemento dell'array, e poi usare la clausola ORDER BY per ordinare le righe in base al relativo offset.

Esempio

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      |
 *----------+--------*/

Per appiattire un'intera colonna di ARRAY mantenendo invariati i valori delle altre colonne in ogni riga, utilizza un join croce correlato per unire la tabella contenente la colonna ARRAY all'output UNNEST di quella colonna.

Con un join correlativo, l'operatore UNNEST fa riferimento alla colonna digitata ARRAY da ogni riga della tabella di origine, che appare in precedenza nella clausola FROM. Per ogni riga N nella tabella di origine, UNNEST appiattisce ARRAY dalla riga N in un insieme di righe contenenti gli elementi ARRAY, quindi il join tra tabelle unisce questo nuovo insieme di righe con la singola riga N della tabella di origine.

Esempi

L'esempio seguente utilizza UNNEST per restituire una riga per ogni elemento nella colonna dell'array. A causa di CROSS JOIN, la colonna id contiene i valori id per la riga in Sequences che contiene ogni numero.

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
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 |
 *------+-------------------*/

Tieni presente che per i join tra tabelle correlate l'operatore UNNEST è facoltativo e CROSS JOIN può essere espresso come un join tra tabelle con virgola. Utilizzando questa notazione abbreviata, l'esempio precedente viene consolidato come segue:

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 |
 *------+-------------------*/

Esecuzione di query su array nidificati

Se una tabella contiene un ARRAY di STRUCT, puoi appiattire il ARRAY per eseguire query sui campi del STRUCT. Puoi anche appiattire i campi di tipo ARRAY dei valori STRUCT.

Esecuzione di query su elementi STRUCT in un array

L'esempio seguente utilizza UNNEST con CROSS JOIN per suddividere un ARRAY di STRUCT.

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

Puoi trovare informazioni specifiche dai campi ripetuti. Ad esempio, la seguente query restituisce il pilota più veloce in una gara degli 800 metri.

Nota: questo esempio non prevede la suddivisione di un array, ma rappresentano un modo comune per ottenere informazioni da un campo ripetuto.

Esempio

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       |
 *------+---------------*/

Eseguire query sui campi di tipo ARRAY in una struct

Puoi ottenere informazioni anche da campi nidificati e ripetuti. Ad esempio, La seguente affermazione restituisce il corridore che ha registrato il giro più veloce in una gara degli 800 metri.

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                |
 *------+-------------------------*/

Nota che la query precedente utilizza l'operatore della virgola (,) per eseguire una CROSS JOIN implicito. È equivalente all'esempio seguente, che utilizza un CROSS JOIN esplicito.

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)
 CROSS 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                |
 *------+-------------------------*/

Gli array di appiattimento con CROSS JOIN esclude le righe vuote o gli array NULL. Se vuoi includere queste righe, utilizza un 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
  name, sum(duration) AS finish_time
FROM Races CROSS JOIN Races.participants LEFT JOIN participants.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              |
 *-------------+--------------------*/

Creazione di array

Puoi costruire un array utilizzando valori letterali di array o funzioni di array. Per scoprire di più sulla creazione di array, consulta Tipo di array.

Creazione di array da sottoquery

Un'attività comune quando si lavora con gli array è trasformare il risultato di una sottoquery in un un array di dati. In GoogleSQL, puoi ottenere questo risultato utilizzando ARRAY().

Ad esempio, considera la seguente operazione sulla tabella 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]            |
 *--------------------+---------------------*/

Questo esempio inizia con una tabella denominata Sequenze. Questa tabella contiene una colonna, some_numbers, di tipo ARRAY<INT64>.

La query stessa contiene una sottoquery. Questa sottoquery seleziona ogni riga nella some_numbers colonna e utilizzi UNNEST per restituire il valore come un insieme di righe. Poi moltiplica ogni valore per due e poi ricombina nuovamente le righe in un array utilizzando l'operatore ARRAY().

Filtrare gli array

L'esempio seguente utilizza una clausola WHERE nella sottoquery dell'operatore ARRAY() per filtrare le righe restituite.

Nota: nei seguenti esempi, le righe risultanti sono non ordinato.

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]                 |
 | []                     |
 *------------------------*/

Nota che la terza riga contiene un array vuoto, perché gli elementi nel la riga originale corrispondente ([5, 10]) non ha soddisfatto il requisito del filtro di x < 5.

Puoi anche filtrare gli array utilizzando SELECT DISTINCT per restituire solo gli elementi unici all'interno di un array.

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers)
SELECT ARRAY(SELECT DISTINCT x
             FROM UNNEST(some_numbers) AS x) AS unique_numbers
FROM Sequences;

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

Puoi anche filtrare le righe di array utilizzando il metodo IN parola chiave. Questo le parole chiave filtrano le righe contenenti array determinando se una specifica corrisponde a un elemento dell'array.

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT
   ARRAY(SELECT x
         FROM UNNEST(some_numbers) AS x
         WHERE 2 IN UNNEST(some_numbers)) AS contains_two
FROM Sequences;

/*--------------------*
 | contains_two       |
 +--------------------+
 | [0, 1, 1, 2, 3, 5] |
 | [2, 4, 8, 16, 32]  |
 | []                 |
 *--------------------*/

Nota ancora una volta che la terza riga contiene un array vuoto, perché l'array la riga originale corrispondente ([5, 10]) non conteneva 2.

Scansione degli array in corso...

Per verificare se un array contiene un valore specifico, utilizza IN con UNNEST. Per verificare se un array contiene un valore di una condizione corrispondente, utilizza l'operatore EXISTS con UNNEST.

Ricerca di valori specifici

Per scansionare un array alla ricerca di un valore specifico, utilizza l'operatore IN con UNNEST.

Esempio

L'esempio seguente restituisce true se l'array contiene il numero 2.

SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;

/*----------------*
 | contains_value |
 +----------------+
 | true           |
 *----------------*/

Per restituire le righe di una tabella in cui la colonna di array contiene un valore specifico, filtra i risultati di IN UNNEST usando la clausola WHERE.

Esempio

L'esempio seguente restituisce il valore id per le righe in cui la colonna dell'array contiene il valore 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             |
 *---------------*/

Analisi dei valori che soddisfano una condizione

Per scansionare un array ai valori che corrispondono a una condizione, utilizza UNNEST per restituire un degli elementi nell'array, usa WHERE per filtrare la tabella risultante una sottoquery e utilizza EXISTS per verificare se la tabella filtrata contiene righe.

Esempio

L'esempio seguente restituisce il valore id per le righe in cui l'array contiene valori maggiori di 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             |
 *---------------*/

Analisi dei valori di campo STRUCT che soddisfano una condizione in corso...

Per cercare un campo il cui valore corrisponde a una condizione in un array di STRUCT, utilizza UNNEST per restituire una tabella con una colonna per ogni campo STRUCT, poi applica un filtro righe non corrispondenti della tabella utilizzando WHERE EXISTS.

Esempio

L'esempio seguente restituisce le righe in cui la colonna di array contiene un STRUCT il cui campo b ha un valore maggiore di 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             |
 *---------------*/

Array e aggregazione

Con GoogleSQL, puoi aggregare i valori in un array utilizzando 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] |
 *-----------------------*/

L'array restituito da ARRAY_AGG() è in ordine arbitrario, poiché l'ordine in che la funzione concatena i valori non è garantito. Per ordinare gli elementi dell'array, utilizza ORDER BY. Ad esempio:

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

Puoi anche applicare funzioni di aggregazione come SUM() agli elementi in una un array di dati. Ad esempio, la seguente query restituisce la somma degli elementi di un array per ogni riga della tabella Sequences.

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
  (SELECT SUM(x)
   FROM UNNEST(s.some_numbers) AS x) AS sums
FROM Sequences AS s;

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

GoogleSQL supporta anche una funzione di aggregazione, ARRAY_CONCAT_AGG(), che concatena gli elementi di una colonna dell'array nelle righe.

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

Nota: l'array restituito da ARRAY_CONCAT_AGG() è non deterministico, poiché l'ordine in cui la funzione concatena i valori non è garantito.

Conversione di array in stringhe

La funzione ARRAY_TO_STRING() consente di convertire un ARRAY<STRING> in un singolo valore STRING o ARRAY<BYTES> a un singolo valore BYTES in cui il valore risultante è la concatenazione ordinata degli elementi array.

Il secondo argomento è il separatore che la funzione inserirà tra gli input per produrre l'output. Questo secondo argomento deve essere dello stesso tipo degli elementi del primo argomento.

Esempio:

WITH Words AS
  (SELECT ["Hello", "World"] AS greeting)
SELECT ARRAY_TO_STRING(greeting, " ") AS greetings
FROM Words;

/*-------------*
 | greetings   |
 +-------------+
 | Hello World |
 *-------------*/

Il terzo argomento facoltativo sostituisce i valori NULL nell'input un array di dati.

• Se ometti questo argomento, la funzione ignora gli elementi dell'array NULL.

• Se fornisci una stringa vuota, la funzione inserisce un separatore per gli elementi dell'array NULL.

Esempio:

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   |
 *------------------+--------------+---------*/

Combinazione di array

In alcuni casi, potresti voler combinare più array in un unico array. Puoi farlo utilizzando la funzione ARRAY_CONCAT().

SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) AS count_to_six;

/*--------------------------------------------------*
 | count_to_six                                     |
 +--------------------------------------------------+
 | [1, 2, 3, 4, 5, 6]                               |
 *--------------------------------------------------*/

Aggiornamento degli array

Considera la seguente tabella denominata arrays_table. La prima colonna della tabella è un array di interi e la seconda colonna contiene due array di interi nidificati.

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

Puoi aggiornare gli array in una tabella utilizzando l'istruzione UPDATE. Le seguenti nel caso di un esempio inserisce il numero 5 nella colonna regular_array e inserisce gli elementi dal campo first_array di nested_arrays nel 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]         |
 *---------------*---------------------------*----------------------------*/

Compressione degli array

Dati due array di uguali dimensioni, è possibile unirli in un unico array ed è costituita da coppie di elementi da array di input, ricavati dal loro posizioni corrispondenti. Questa operazione è a volte chiamata creazione di un file ZIP.

Puoi comprimere gli array con UNNEST e WITH OFFSET. In questo esempio, ogni coppia di valori viene memorizzata come STRUCT in un array.

WITH
  Combinations AS (
    SELECT
      ['a', 'b'] AS letters,
      [1, 2, 3] AS numbers
  )
SELECT
  ARRAY(
    SELECT AS STRUCT
      letters[SAFE_OFFSET(index)] AS letter,
      numbers[SAFE_OFFSET(index)] AS number
    FROM Combinations
    CROSS JOIN
      UNNEST(
        GENERATE_ARRAY(
          0,
          LEAST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
    ORDER BY index
  );

/*------------------------------*
 | pairs                        |
 +------------------------------+
 | [{ letter: "a", number: 1 }, |
 |  { letter: "b", number: 2 }] |
 *------------------------------*/

Puoi utilizzare array di input di lunghezze diverse, purché la prima matrice sia uguale o inferiore alla lunghezza della seconda matrice. L'array compresso avrà la lunghezza dell'array di input più breve.

Per ottenere un array compresso che includa tutti gli elementi anche quando gli array di input hanno lunghezze diverse, cambia LEAST in GREATEST. Gli elementi di una delle matrici che non hanno un elemento associato nell'altra matrice verranno accoppiati con NULL.

WITH
  Combinations AS (
    SELECT
      ['a', 'b'] AS letters,
      [1, 2, 3] AS numbers
  )
SELECT
  ARRAY(
    SELECT AS STRUCT
      letters[SAFE_OFFSET(index)] AS letter,
      numbers[SAFE_OFFSET(index)] AS number
    FROM Combinations
    CROSS JOIN
      UNNEST(
        GENERATE_ARRAY(
          0,
          GREATEST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
    ORDER BY index
  );

/*-------------------------------*
 | pairs                         |
 +-------------------------------+
 | [{ letter: "a", number: 1 },  |
 |  { letter: "b", number: 2 },  |
 |  { letter: null, number: 3 }] |
 *-------------------------------*/

Creazione di array di array

GoogleSQL non supporta la creazione diretta di array di array. Devi invece creare un array di struct, con ogni struct contenente un campo di tipo ARRAY. Per spiegare meglio, considera quanto segue: Tabella Points:

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

Ora, supponiamo che tu voglia creare un array composto da ogni point Tabella Points. A questo scopo, aggrega l'array restituito da ogni riga in una STRUCT, come mostrato di seguito.

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