Utilizzare gli array

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, consulta Tipo di array.

Con GoogleSQL, puoi creare valori letterali di array, creare array da sottoquery utilizzando la ARRAY funzione, e aggregare i valori in un array utilizzando la ARRAY_AGG funzione.

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

Accedere 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 quale tipo di indicizzazione vuoi utilizzare: o index o OFFSET(index) per gli indici basati su zero oppure ORDINAL(index) per gli indici basati su uno:

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 generano errori se l'indice non è compreso nell'intervallo. Per evitare questo problema, puoi utilizzare SAFE_OFFSET o SAFE_ORDINAL per restituire NULL anziché generare un errore.

Per accedere al primo o all'ultimo elemento di un array, utilizza la ARRAY_FIRST o ARRAY_LAST funzione:

SELECT
  some_numbers,
  ARRAY_FIRST(some_numbers) AS first_element,
  ARRAY_LAST(some_numbers) AS last_element
FROM Sequences;

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

Con le funzioni ARRAY_FIRST e ARRAY_LAST, se un array è vuoto, la funzione genera un errore:

WITH
  Sequences AS (
    SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
    UNION ALL
    SELECT [2, 4, 8, 16, 32]
    UNION ALL
    SELECT [] -- Empty array
  )
SELECT
  some_numbers,
  ARRAY_LAST(some_numbers) AS last_element
FROM Sequences;

-- Error: ARRAY_LAST can't get the last element of an empty array.

Per gestire gli array vuoti quando accedi al primo e all'ultimo elemento, puoi utilizzare la ARRAY_LENGTH funzione all'interno di un SAFE_OFFSET di -1. La query restituisce valori NULL per tutti gli array vuoti anziché un errore:

SELECT
  some_numbers,
  some_numbers[SAFE_OFFSET(ARRAY_LENGTH(some_numbers) - 1)] AS last_element
FROM Sequences;

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

ARRAY_LENGTH(array) restituisce il numero di elementi nell'array. Poiché gli offset degli array sono basati su 0, ARRAY_LENGTH(array) - 1 fornisce l'offset dell'ultimo elemento. Se l'array è vuoto, ARRAY_LENGTH è 0 e l'offset diventa -1. SAFE_OFFSET(-1) restituisce NULL, quindi questo approccio gestisce in modo sicuro gli array vuoti.

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

Convertire gli elementi di un array in righe di una tabella

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

Poiché UNNEST distrugge l'ordine degli elementi ARRAY, potresti voler ripristinare l'ordine della tabella. Per farlo, utilizza la clausola facoltativa WITH OFFSET per restituire una colonna aggiuntiva con l'offset di 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 mantenendo i valori delle altre colonne in ogni riga, utilizza un INNER JOIN correlato per unire la tabella contenente la colonna ARRAY all'output UNNEST di quella colonna ARRAY.

Con un join correlato, l'operatore UNNEST fa riferimento alla colonna tipizzata 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 il ARRAY dalla riga N in un insieme di righe contenenti gli ARRAY elementi, quindi un INNER JOIN o CROSS JOIN correlato 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 dell'array. 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 i join correlati l'operatore UNNEST è facoltativo e INNER JOIN può essere espresso come CROSS JOIN o come join incrociato con virgola. Utilizzando la notazione abbreviata del join incrociato 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 |
 +------+-------------------*/

Eseguire query sugli array nidificati

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

Eseguire 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 seguente query restituisce il corridore più veloce in una gara di 800 metri.

Nota: questo esempio non prevede l'appiattimento di un array, ma rappresenta 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 anche ottenere informazioni dai campi ripetuti nidificati. Ad esempio, l'istruzione seguente 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 join incrociato e appiattire l'array. Questo è equivalente all'utilizzo di un CROSS JOIN esplicito o dell'esempio seguente 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              |
 +-------------+--------------------*/

Creare array

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

Creare array da sottoquery

Un'attività comune quando si utilizzano gli array è trasformare il risultato di una sottoquery in un array. In GoogleSQL, puoi farlo utilizzando la ARRAY() funzione.

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 some_numbers colonna e utilizza UNNEST per restituire l' array come un insieme di righe. Poi, moltiplica ogni valore per due e quindi ricombina 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: negli esempi seguenti, le righe risultanti non sono ordinate.

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 soddisfacevano il requisito del filtro 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 le righe degli array utilizzando la IN parola chiave. 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]  |
 | []                 |
 +--------------------*/

Tieni presente che la terza riga contiene un array vuoto, perché l'array nella riga originale corrispondente ([5, 10]) non conteneva 2.

Scansionare gli array

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

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

Scansionare i valori che soddisfano una condizione

Per scansionare un array alla ricerca di valori che soddisfano 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             |
 +---------------*/

Scansionare i valori dei campi STRUCT che soddisfano una condizione

Per cercare in un array di valori STRUCT un campo il cui valore soddisfa una condizione, utilizza UNNEST per restituire una tabella con una colonna per ogni campo STRUCT, quindi filtra le righe non corrispondenti dalla tabella utilizzando WHERE EXISTS.

Esempio

L'esempio seguente restituisce le righe in cui la colonna dell'array contiene una 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:

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 dell'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]                               |
 +--------------------------------------------------*/

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

Convertire gli 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 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 singolo 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]                               |
 +--------------------------------------------------*/

Aggiornare gli array

Considera la seguente tabella denominata arrays_table. La prima colonna della tabella è un array di numeri interi e 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 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]         |
 +---------------*---------------------------*----------------------------*/

Comprimere gli array

Dati due array di dimensioni uguali, puoi unirli in un singolo array composto da coppie di elementi degli array di input, presi dalle 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 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 array di input di lunghezze diverse, purché la lunghezza del primo array sia uguale o inferiore alla lunghezza del secondo array. La lunghezza dell'array compresso sarà uguale alla 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 che non hanno un elemento associato 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
    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, con ogni struct contenente 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]   |
 +----------*/

Supponiamo ora di voler creare un array composto da ogni point nella tabella Points. Per farlo, racchiudi 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]}]  |
 +-------------------*/