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, come INT64
, e tipi di dati complessi, come STRUCT
. L'eccezione attuale è il tipo di dati ARRAY
, perché gli array di array non sono supportati. Per scoprire di più sul tipo di dati ARRAY
, inclusa la gestione di NULL
, consulta Tipo di array.
Con GoogleSQL, puoi costruire valori letterali array, creare array da sottoquery utilizzando la funzione ARRAY
e aggregare i valori in un array utilizzando la funzione ARRAY_AGG
.
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 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 il tipo di indicizzazione che vuoi utilizzare: index
o OFFSET(index)
per gli indici in base zero oppure ORDINAL(index)
per gli indici a base zero.
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 |
*--------------------+---------+----------+-----------*/
Lunghezze 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 |
*--------------------+--------*/
Convertire gli elementi di un array in righe di una tabella
Per convertire un ARRAY
in un insieme di righe, chiamato anche "appiattimento", utilizza l'operatore UNNEST
. UNNEST
prende un ARRAY
e restituisce una tabella con una singola riga per
ogni elemento in ARRAY
.
Poiché UNNEST
elimina l'ordine degli elementi ARRAY
, ti consigliamo di ripristinare l'ordine nella tabella. Per farlo, utilizza la clausola WITH OFFSET
facoltativa per restituire una colonna aggiuntiva con l'offset per ogni elemento dell'array, quindi usa 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 suddividere un'intera colonna di ARRAY
mantenendo i valori delle altre colonne in ogni riga, utilizza un cross join correlato per unire la tabella contenente la colonna ARRAY
all'output UNNEST
della colonna ARRAY
.
Con un join correlated, l'operatore UNNEST
fa riferimento alla colonna di tipo ARRAY
di ogni riga nella tabella di origine, visualizzata in precedenza nella clausola FROM
. Per ogni riga N
nella tabella di origine,
UNNEST
riunisce ARRAY
dalla riga N
in un insieme di righe contenenti gli
elementi ARRAY
. Successivamente, il cross join unisce questo nuovo insieme di righe alla
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 cross join correlati, l'operatore UNNEST
è facoltativo e CROSS JOIN
può essere espresso come una virgola (cross join). Utilizzando questa notazione breve, 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 valore ARRAY
di STRUCT
, puoi
appiattire ARRAY
per eseguire query sui campi di STRUCT
.
Puoi anche suddividere i campi di tipo ARRAY
di STRUCT
valori.
Esecuzione di query sugli elementi STRUCT
in un array
L'esempio seguente utilizza UNNEST
con CROSS JOIN
per suddividere un valore 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 provenienti da campi ripetuti. Ad esempio, la seguente query restituisce il partecipante più veloce in una gara di 800 milioni.
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 |
*------+---------------*/
Esecuzione di query sui campi di tipo ARRAY
in uno struct
Puoi anche ottenere informazioni da campi ripetuti nidificati. Ad esempio, la seguente istruzione restituisce il runner che ha effettuato il giro più veloce in una gara di 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 |
*------+-------------------------*/
Tieni presente che la query precedente utilizza l'operatore della virgola (,
) per eseguire un 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 |
*------+-------------------------*/
La suddivisione degli array con CROSS JOIN
esclude le righe che hanno array vuoti o NULL. Se vuoi includere queste righe, utilizza un elemento 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 array o funzioni di matrice. Per scoprire di più sulla costruzione di array, consulta Tipo di array.
Creazione di array da sottoquery
Un'attività comune quando si lavora con gli array è la trasformazione di un risultato di una sottoquery in un array. In GoogleSQL, puoi eseguire questa operazione utilizzando la funzione 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 contiene una sottoquery. Questa sottoquery seleziona ogni riga nella colonna some_numbers
e utilizza UNNEST
per restituire l'array come insieme di righe. Successivamente, moltiplica ogni valore per due e poi ricombina le righe in un array utilizzando l'operatore ARRAY()
.
Applicazione di filtri agli array
L'esempio seguente utilizza una clausola WHERE
nella sottoquery dell'operatore ARRAY()
per filtrare le righe restituite.
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] |
| [] |
*------------------------*/
Tieni presente che la terza riga contiene un array vuoto, perché gli elementi nella riga originale corrispondente ([5, 10]
) non hanno soddisfatto il requisito del filtro di x < 5
.
Puoi anche filtrare gli array utilizzando SELECT DISTINCT
per restituire solo
elementi univoci 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 righe di array utilizzando la parola chiave IN
. Questa parola chiave filtra le righe contenenti matrici determinando se un valore specifico 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 nella riga originale corrispondente ([5, 10]
) non conteneva 2
.
Scansione degli array
Per verificare se un array contiene un valore specifico, utilizza l'operatore IN
con UNNEST
. Per verificare se un array contiene un valore corrispondente a una condizione, utilizza l'operatore EXISTS
con UNNEST
.
Ricerca di valori specifici in corso...
Per eseguire la scansione di un array per 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 array contiene un valore specifico, filtra i risultati di IN UNNEST
utilizzando 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 |
*---------------*/
Ricerca di valori che soddisfano una condizione
Per analizzare un array e individuare i valori che corrispondono a una condizione, utilizza UNNEST
per restituire una tabella degli elementi nell'array, utilizza WHERE
per filtrare la tabella risultante in 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 la colonna dell'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 |
*---------------*/
Ricerca di 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
, quindi filtra le righe non corrispondenti della tabella utilizzando WHERE EXISTS
.
Esempio
L'esempio seguente restituisce le righe in cui la colonna di array contiene un elemento 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 cui 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 un
array. Ad esempio, la seguente query restituisce la somma degli elementi dell'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 aggregata, ARRAY_CONCAT_AGG()
, che concatena gli elementi di una colonna array tra 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] |
*--------------------------------------------------*/
Conversione di array in stringhe
La funzione ARRAY_TO_STRING()
consente di convertire un ARRAY<STRING>
in un
singolo valore STRING
o un ARRAY<BYTES>
in un singolo valore BYTES
, dove il
valore risultante è la concatenazione ordinata degli elementi dell'array.
Il secondo argomento è il separatore che la funzione inserisce 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'array di input.
Se ometti questo argomento, la funzione ignora gli elementi 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 usando 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 nella tabella contiene un array di numeri interi, mentre la seconda colonna contiene due array nidificati di numeri interi.
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
. L'esempio seguente inserisce il numero 5 nella colonna regular_array
e gli elementi del campo first_array
della colonna 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 array
Dati due array di uguali dimensioni, puoi unirli in un unico array composto da coppie di elementi da array di input, presi dalle loro posizioni corrispondenti. Questa operazione viene a volte chiamata compressione.
Puoi comprimere gli array con UNNEST
e WITH OFFSET
. In questo esempio, ogni coppia di valori è archiviata 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é il primo array sia uguale o inferiore a quello del secondo. L'array compresso sarà 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, modifica LEAST
in GREATEST
. Gli elementi di uno dei due array senza elementi associati nell'altro array 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 la seguente tabella Points
:
/*----------*
| point |
+----------+
| [1, 5] |
| [2, 8] |
| [3, 7] |
| [4, 1] |
| [5, 7] |
*----------*/
Ora, supponiamo di voler creare un array composto da ogni point
nella tabella Points
. A questo scopo, aggrega l'array restituito da ogni riga in un elemento
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]}] |
*-------------------*/