In GoogleSQL per BigQuery, un array è un elenco ordinato composto da zero o più valori dello stesso tipo di dati. Puoi creare array di un tipo di dati semplice, come INT64, o di un tipo di dati complesso, come STRUCT. Tuttavia, gli array di array non sono supportati. Per saperne di più sul tipo di dati ARRAY, inclusa la gestione di NULL, vedi Tipo di array.
Con GoogleSQL, puoi creare valori letterali di array, creare array da sottoquery utilizzando la funzione ARRAY e aggregare valori in un array utilizzando la funzione ARRAY_AGG.
Puoi combinare gli array utilizzando funzioni come ARRAY_CONCAT() e convertirli in stringhe utilizzando ARRAY_TO_STRING().
Accesso agli elementi dell'array
Considera la seguente tabella 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 il tipo di indicizzazione che vuoi utilizzare: index o OFFSET(index) per gli indici basati su zero oppure ORDINAL(index) per gli 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 | *--------------------+---------+----------+-----------*/ Trovare le lunghezze
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 di elementi di un array in righe di una tabella
Per convertire un ARRAY in un insieme di righe, operazione nota anche come "appiattimento", utilizza l'operatore UNNEST. UNNEST accetta un ARRAY e restituisce una tabella con una singola riga per ogni elemento in ARRAY.
Poiché UNNEST distrugge l'ordine degli elementi ARRAY, potresti voler ripristinare l'ordine nella tabella. Per farlo, utilizza la clausola facoltativa WITH OFFSET per restituire una colonna aggiuntiva con l'offset per ogni elemento dell'array, quindi utilizza la clausola ORDER BY per ordinare le righe in base al loro 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 tipo ARRAY preservando i valori delle altre colonne di ogni riga, utilizza una INNER JOIN correlata per unire la tabella contenente la colonna ARRAY all'output UNNEST di questa colonna ARRAY.
Con un'unione correlata, l'operatore UNNEST fa riferimento alla colonna digitata ARRAY di ogni riga della tabella di origine, che viene visualizzata 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 una INNER JOIN o CROSS JOIN correlata combina 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 della matrice. A causa di INNER 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 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 | *------+-------------------*/ Tieni presente che per le unioni correlate l'operatore UNNEST è facoltativo e INNER JOIN può essere espresso come CROSS JOIN o come un'unione incrociata con virgola. Utilizzando la notazione abbreviata del cross join con virgola, 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 sugli array nidificati
Se una tabella contiene un ARRAY di STRUCT, puoi appiattire il ARRAY per eseguire query sui campi di STRUCT. Puoi anche appiattire i campi di tipo ARRAY dei valori STRUCT.
Esecuzione di query sugli elementi STRUCT in un array
L'esempio seguente utilizza UNNEST con INNER JOIN per appiattire 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 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]} | *------+---------------------------------------*/ Puoi trovare informazioni specifiche dai campi ripetuti. Ad esempio, la query seguente restituisce il corridore più veloce in una gara di 800 metri.
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 una struttura
Puoi anche ottenere informazioni da campi ripetuti nidificati. Ad esempio, la seguente istruzione restituisce il corridore che ha fatto 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 virgola (,) per eseguire un cross join e appiattire l'array. Ciò equivale a utilizzare un CROSS JOIN esplicito o il seguente esempio che utilizza un INNER 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) 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 | *------+-------------------------*/ L'appiattimento degli array con INNER JOIN esclude le righe con array vuoti o NULL. Se vuoi includere queste righe, utilizza 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 | *-------------+--------------------*/ Costruire array
Puoi creare un array utilizzando i valori letterali dell'array o le funzioni dell'array. Per saperne di più sulla creazione di array, vedi Tipo di array.
Creare array dalle sottoquery
Un'attività comune quando si utilizzano gli array è la conversione del risultato di una sottoquery in un array. In GoogleSQL, puoi farlo 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 Sequences. Questa tabella contiene una colonna, some_numbers, di tipo ARRAY<INT64>.
La query stessa 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 una matrice utilizzando l'operatore ARRAY().
Filtrare gli 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] | | [] | *------------------------*/ Nota che la terza riga contiene un array vuoto, perché gli elementi nella riga originale corrispondente ([5, 10]) non soddisfano il requisito del filtro x < 5.
Puoi anche filtrare gli array utilizzando SELECT DISTINCT per restituire solo gli 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 le righe degli array utilizzando la parola chiave IN. Questa parola chiave filtra le righe contenenti array 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 di nuovo 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 che corrisponde a una condizione, utilizza l'operatore EXISTS con UNNEST.
Scansione per 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 dell'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 | *---------------*/ Scansione dei valori che soddisfano una condizione
Per analizzare un array alla ricerca di valori che corrispondono a una condizione, utilizza UNNEST per restituire una tabella degli elementi dell'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 | *---------------*/ Scansione dei valori del campo STRUCT che soddisfano una condizione
Per cercare un array di valori STRUCT per un campo il cui valore corrisponde a una condizione, utilizza UNNEST per restituire una tabella con una colonna per ogni campo STRUCT, poi filtra le righe non corrispondenti dalla tabella utilizzando WHERE EXISTS.
Esempio
L'esempio seguente restituisce le righe in cui la colonna dell'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 un 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 di 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 di aggregazione, ARRAY_CONCAT_AGG(), che concatena gli elementi di una colonna di array tra le 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 in cui il valore risultante è la concatenazione ordinata degli elementi dell'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'array di input.
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 | *------------------+--------------+---------*/ Combinare gli 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 è una matrice di numeri interi e la seconda colonna contiene due matrici nidificate 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. Il seguente esempio inserisce il numero 5 nella colonna regular_array e inserisce 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 di array
Dati due array di dimensioni uguali, puoi unirli in un unico array costituito da coppie di elementi degli array di input, presi dalle posizioni corrispondenti. Questa operazione è a volte chiamata compressione.
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 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 }] | *------------------------------*/ Puoi utilizzare matrici di input di lunghezze diverse, purché la prima matrice sia uguale o inferiore alla lunghezza della seconda matrice. La lunghezza dell'array compresso sarà pari a quella dell'array di input più corto.
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 una matrice che non hanno un elemento associato nell'altra matrice verranno accoppiati 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 }] | *-------------------------------*/ Creare array di array
GoogleSQL non supporta la creazione diretta di array di array. Devi invece creare un array di struct, in cui ogni struct contiene un campo di tipo ARRAY. Per illustrare questo concetto, considera la seguente 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 nella tabella Points. Per farlo, racchiudi l'array restituito da ogni riga in un 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]}] | *-------------------*/