Utiliser des tableaux

En GoogleSQL pour BigQuery, un tableau est une liste ordonnée composée de zéro ou plusieurs valeurs du même type de données. Vous pouvez construire des tableaux de types de données simples, tels que INT64, ou de types de données complexes, tels que STRUCT. Toutefois, les tableaux de tableaux ne sont pas acceptés. Pour en savoir plus sur le type de données ARRAY, y compris sur la gestion NULL, consultez la section Type de tableau.

GoogleSQL permet de construire des littéraux de tableau, de créer des tableaux à partir de sous-requêtes à l'aide de la fonction ARRAY et d'agréger des valeurs dans un tableau à l'aide de la fonction ARRAY_AGG.

Vous pouvez combiner des tableaux à l'aide de fonctions telles que ARRAY_CONCAT() et convertir des tableaux en chaînes à l'aide de la fonction ARRAY_TO_STRING().

Accéder aux éléments de tableau

Prenons l'exemple de table suivant appelé Sequences. Cette table contient la colonne some_numbers comportant des données de type 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]             |
 *---------------------*/

Pour accéder aux éléments de tableau dans la colonne some_numbers, spécifiez le type d'indexation que vous souhaitez utiliser : soit index ou OFFSET(index) pour les index basés sur zéro, soit ORDINAL(index) pour les index basés sur un.

Par exemple :

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

Remarque : Les fonctions OFFSET et ORDINAL génèrent des erreurs si l'index est en dehors de la plage. Pour éviter cela, vous pouvez utiliser SAFE_OFFSET ou SAFE_ORDINAL pour que NULL soit renvoyé plutôt qu'une erreur soit générée.

Connaître les longueurs

La fonction ARRAY_LENGTH renvoie la longueur d'un tableau.

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

Convertir les éléments d'un tableau en lignes d'une table

Pour convertir un objet ARRAY en un ensemble de lignes (processus appelé "aplatissement"), utilisez l'opérateur UNNEST. UNNEST prend un objet ARRAY et renvoie une table avec une seule ligne pour chaque élément de l'ARRAY.

Comme UNNEST détruit l'ordre des éléments de l'objet ARRAY, vous souhaiterez peut-être rétablir l'ordre dans la table. Pour ce faire, utilisez la clause optionnelle WITH OFFSET pour afficher une colonne supplémentaire contenant le décalage de chaque élément de tableau, puis utilisez la clause ORDER BY pour classer les lignes en fonction de leur décalage.

Exemple

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

Pour aplatir une colonne entière de type ARRAY tout en conservant les valeurs des autres colonnes de chaque ligne, utilisez une INNER JOIN corrélée pour joindre la table contenant la colonne ARRAY au résultat de l'opérateur UNNEST appliqué à cette colonne ARRAY.

Avec une jointure corrélée, l'opérateur UNNEST référence la colonne typée ARRAY de chaque ligne de la table source, qui apparaît précédemment dans la clause FROM. Pour chaque ligne N de la table source, l'opérateur UNNEST aplatit l'objet ARRAY à partir de la ligne N en un ensemble de lignes contenant les éléments de l'objet ARRAY, puis une sous-requête corrélée INNER JOIN ou CROSS JOIN combine ce nouvel ensemble de lignes avec la ligne unique N de la table source.

Exemples

L'exemple suivant utilise UNNEST pour renvoyer une ligne pour chaque élément de la colonne de tableaux. Avec la commande INNER JOIN, la colonne id contient les valeurs id de la ligne dans la table Sequences comportant chaque nombre.

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

Notez que pour les jointures corrélées, l'opérateur UNNEST est facultatif et le champ INNER JOIN peut être exprimé en tant que CROSS JOIN ou jointure croisée séparée par des virgules. En utilisant la notation abrégée de jointure croisée avec virgule, l'exemple précédent est consolidé comme suit :

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

Lancer des requêtes sur des tableaux imbriqués

Si une table contient un objet ARRAY de données STRUCT, vous pouvez aplatir l'objet ARRAY pour effectuer des requêtes sur les champs de données STRUCT. Vous pouvez également aplatir les champs ARRAY de valeurs STRUCT.

Rechercher des éléments STRUCT dans un tableau

L'exemple suivant utilise UNNEST en combinaison avec INNER JOIN pour aplatir un ARRAY d'éléments 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]}      |
 *------+---------------------------------------*/

Vous pouvez trouver des informations spécifiques à partir de champs répétés. Par exemple, la requête suivante renvoie le coureur le plus rapide lors d'une course sur 800 m.

Remarque : Cet exemple n'implique pas l'aplatissement d'un tableau, mais représente un moyen courant d'obtenir des informations à partir d'un champ répété.

Exemple

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

Lancer des requêtes sur des champs de type ARRAY dans un objet struct

Vous pouvez également obtenir des informations à partir de champs répétés imbriqués. Par exemple, l'instruction suivante renvoie le coureur qui a réalisé le tour de piste le plus rapide lors d'une course sur 800 m.

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

Notez que la requête précédente utilise l'opérateur "virgule" (,) pour effectuer une jointure croisée et aplatir le tableau. Cela équivaut à utiliser un CROSS JOIN explicite ou l'exemple suivant qui utilise un INNER JOIN explicite :

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'aplatissement de tableaux avec INNER JOIN exclut les lignes contenant des tableaux vides ou NULL. Si vous souhaitez inclure ces lignes, utilisez 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              |
 *-------------+--------------------*/

Construire des tableaux

Vous pouvez créer un élément array à l'aide de littéraux ou de fonctions de tableau. Pour en savoir plus sur la construction de tableaux, consultez la section Type de tableau.

Créer des tableaux à partir de sous-requêtes

Une tâche courante lors de l'utilisation de tableaux consiste à convertir un résultat de sous-requête en tableau. En GoogleSQL, vous pouvez effectuer cette opération à l'aide de la fonction ARRAY().

Prenons l'exemple de l'opération suivante sur la table 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]            |
 *--------------------+---------------------*/

Cet exemple utilise une table nommée "Sequences". Cette table contient une colonne some_numbers de type ARRAY<INT64>.

La requête elle-même contient une sous-requête. Cette sous-requête sélectionne chaque ligne de la colonne some_numbers et utilise UNNEST pour renvoyer le tableau sous forme d'un ensemble de lignes. Ensuite, elle multiplie chaque valeur par deux, puis recombine les lignes dans un tableau à l'aide de l'opérateur ARRAY().

Filtrer des tableaux

L'exemple suivant utilise une clause WHERE dans la sous-requête de l'opérateur ARRAY() pour filtrer les lignes renvoyées.

Remarque : Dans les exemples suivants, les lignes renvoyées ne sont pas ordonnées.

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

Notez que la troisième ligne contient un tableau vide, car les éléments de la ligne d'origine correspondante ([5, 10]) ne répondaient pas à l'exigence de filtre x < 5.

Vous pouvez également filtrer des tableaux en utilisant SELECT DISTINCT pour ne renvoyer que des éléments uniques au sein d'un tableau.

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

Vous pouvez également filtrer des lignes de tableaux en utilisant le mot clé IN. Ce mot clé filtre les lignes contenant des tableaux en déterminant si une valeur spécifique correspond à un élément du tableau.

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

Notez à nouveau que la troisième ligne contient un tableau vide, car le tableau de la ligne d'origine correspondante ([5, 10]) ne contenait pas 2.

Analyser des tableaux

Pour vérifier si un tableau contient une valeur spécifique, utilisez l'opérateur IN avec UNNEST. Pour vérifier si un tableau contient une valeur correspondant à une condition, utilisez l'opérateur EXISTS avec UNNEST.

Rechercher des valeurs spécifiques

Pour rechercher une valeur spécifique dans un tableau, utilisez l'opérateur IN avec UNNEST.

Exemple

L'exemple suivant renvoie true si le tableau contient le nombre 2.

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

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

Pour afficher les lignes d'une table dont la colonne de tableau contient une valeur spécifique, filtrez les résultats de IN UNNEST à l'aide de la clause WHERE.

Exemple

L'exemple suivant renvoie la valeur id pour les lignes dans lesquelles la colonne de tableau contient la valeur 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             |
 *---------------*/

Rechercher des valeurs qui répondent à une condition

Pour rechercher dans un tableau des valeurs correspondant à une condition, utilisez UNNEST pour renvoyer une table des éléments du tableau, WHERE pour filtrer la table résultante dans une sous-requête et EXISTS pour vérifier si la table filtrée contient des lignes.

Exemple

L'exemple suivant renvoie la valeur id pour les lignes dans lesquelles la colonne de tableau contient des valeurs supérieures à 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             |
 *---------------*/

Rechercher des valeurs de champ STRUCT répondant à une condition

Pour rechercher dans un tableau de valeurs STRUCT un champ dont la valeur répond à une condition, utilisez UNNEST pour renvoyer une table avec une colonne pour chaque champ STRUCT, puis filtrez les lignes non correspondantes de la table à l'aide de WHERE EXISTS.

Exemple

L'exemple suivant renvoie les lignes pour lesquelles la colonne de tableau contient un STRUCT dont le champ b possède une valeur supérieure à 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             |
 *---------------*/

Tableaux et agrégation

Avec GoogleSQL, vous pouvez agréger des valeurs dans un tableau à l'aide de 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] |
 *-----------------------*/

Le tableau renvoyé par ARRAY_AGG() est dans un ordre arbitraire, car l'ordre dans lequel la fonction concatène des valeurs n'est pas garanti. Pour trier les éléments du tableau, utilisez ORDER BY. Exemple :

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

Vous pouvez également appliquer des fonctions d'agrégation telles que SUM() aux éléments d'un tableau. Par exemple, la requête suivante renvoie la somme des éléments du tableau pour chaque ligne de la table 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 accepte également une fonction d'agrégation, ARRAY_CONCAT_AGG(), qui concatène les éléments d'une colonne de tableau sur plusieurs lignes.

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

Remarque : Le tableau renvoyé par ARRAY_CONCAT_AGG() n'est pas déterministe, car l'ordre dans lequel la fonction concatène les valeurs n'est pas garanti.

Convertir des tableaux en chaînes

La fonction ARRAY_TO_STRING() permet de convertir un ARRAY<STRING> en une seule valeur STRING ou un ARRAY<BYTES> en une seule valeur BYTES où la valeur résultante est la concaténation ordonnée des éléments du tableau.

Le deuxième argument est le séparateur que la fonction insérera entre les entrées pour produire le résultat. Il doit être du même type que les éléments du premier argument.

Exemple :

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

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

Le troisième argument facultatif remplace les valeurs NULL dans le tableau d'entrée.

• Si vous omettez cet argument, la fonction ignore les éléments de tableau NULL.

• Si vous fournissez une chaîne vide, la fonction insère un séparateur pour les éléments de tableau NULL.

Exemple :

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

Combiner des tableaux

Dans certains cas, vous pouvez combiner plusieurs tableaux en un seul. Pour ce faire, utilisez la fonction ARRAY_CONCAT().

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

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

Mettre à jour des tableaux

Prenons l'exemple de table suivant appelé arrays_table. La première colonne de la table est un tableau d'entiers et la deuxième colonne contient deux tableaux d'entiers imbriqués.

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

Vous pouvez mettre à jour des tableaux dans une table à l'aide de l'instruction UPDATE. L'exemple suivant insère le chiffre 5 dans la colonne regular_array et insère les éléments du champ first_array de la colonne nested_arrays dans le champ 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]         |
 *---------------*---------------------------*----------------------------*/

Compresser des tableaux

Vous pouvez fusionner deux tableaux de taille égale en un seul tableau composé de paires d'éléments provenant de tableaux d'entrée, issus de leurs positions correspondantes. Cette opération est parfois appelée compression.

Vous pouvez compresser des tableaux avec UNNEST et WITH OFFSET. Dans cet exemple, chaque paire de valeurs est stockée en tant que STRUCT dans un tableau.

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

Vous pouvez utiliser des tableaux d'entrée de longueurs différentes tant que la longueur du premier tableau est égale ou inférieure à celle du deuxième tableau. La longueur du tableau compressé correspondra à celle du tableau d'entrée le plus court.

Pour obtenir un tableau compressé qui inclut tous les éléments, même lorsque les tableaux d'entrée sont de longueurs différentes, remplacez LEAST par GREATEST. Les éléments de l'un des tableaux qui n'ont pas d'élément associé dans l'autre tableau seront associés à 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 }] |
 *-------------------------------*/

Construire des tableaux de tableaux

GoogleSQL n'est pas compatible avec la construction directe de tableaux de tableaux. À la place, vous créez un tableau de structures, chaque structure contenant un champ de type ARRAY. Pour illustrer cela, prenons pour exemple le tableau Points suivant :

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

Supposons maintenant que vous vouliez créer un tableau composé de chaque point de la table Points. Pour ce faire, placez le tableau renvoyé par chaque ligne dans un objet STRUCT, comme indiqué ci-dessous.

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