Aplatir les tableaux imbriqués

Lorsque vous utilisez des tableaux imbriqués, vous avez souvent besoin de développer des éléments de tableau imbriqué dans un seul tableau, ou de développer le tableau en plusieurs lignes.

Utilisez la fonction d'aplatissement

Pour aplatir les éléments d'un tableau imbriqué dans un seul tableau de valeurs, utilisez la fonction flatten. Cette requête renvoie une ligne pour chaque élément du tableau.

SELECT flatten(ARRAY[ ARRAY[1,2], ARRAY[3,4] ]) AS items

Cette requête renvoie :

+-----------+
| items     |
+-----------+
| [1,2,3,4] |
+-----------+

Utilisation CROSS JOIN et UNNEST

Pour aplatir un tableau en plusieurs lignes, utilisez CROSS JOIN conjointement avec l'opérateur UNNEST, comme dans cet exemple :

WITH dataset AS (
  SELECT
    'engineering' as department,
    ARRAY['Sharon', 'John', 'Bob', 'Sally'] as users
)
SELECT department, names FROM dataset
CROSS JOIN UNNEST(users) as t(names)

Cette requête renvoie :

+----------------------+
| department  | names  |
+----------------------+
| engineering | Sharon |
+----------------------|
| engineering | John   |
+----------------------|
| engineering | Bob    |
+----------------------|
| engineering | Sally  |
+----------------------+

Pour aplatir un tableau de paires clé-valeur, transposez les clés sélectionnées en colonnes, comme dans cet exemple :

WITH
dataset AS (
  SELECT
    'engineering' as department,
     ARRAY[
      MAP(ARRAY['first', 'last', 'age'],ARRAY['Bob', 'Smith', '40']),
      MAP(ARRAY['first', 'last', 'age'],ARRAY['Jane', 'Doe', '30']),
      MAP(ARRAY['first', 'last', 'age'],ARRAY['Billy', 'Smith', '8'])
     ] AS people
  )
SELECT names['first'] AS
 first_name,
 names['last'] AS last_name,
 department FROM dataset
CROSS JOIN UNNEST(people) AS t(names)

Cette requête renvoie :

+--------------------------------------+
| first_name | last_name | department  |
+--------------------------------------+
| Bob        | Smith     | engineering |
| Jane       | Doe       | engineering |
| Billy      | Smith     | engineering |
+--------------------------------------+

Depuis la liste des employés, sélectionnez l'employé ayant obtenu les meilleurs scores combinés. UNNEST peut être utilisé dans la clause FROM sans être précédé par CROSS JOIN, car il s'agit de l'opérateur de jointure par défaut qui est donc implicite.

WITH
dataset AS (
  SELECT ARRAY[
    CAST(ROW('Sally', 'engineering', ARRAY[1,2,3,4]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))),
    CAST(ROW('John', 'finance', ARRAY[7,8,9]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))),
    CAST(ROW('Amy', 'devops', ARRAY[12,13,14,15]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER)))
  ] AS users
),
users AS (
 SELECT person, score
 FROM
   dataset,
   UNNEST(dataset.users) AS t(person),
   UNNEST(person.scores) AS t(score)
)
SELECT person.name, person.department, SUM(score) AS total_score FROM users
GROUP BY (person.name, person.department)
ORDER BY (total_score) DESC
LIMIT 1

Cette requête renvoie :

+---------------------------------+
| name | department | total_score |
+---------------------------------+
| Amy  | devops     | 54          |
+---------------------------------+

Depuis la liste des employés, sélectionnez l'employé ayant obtenu le meilleur score individuel.

WITH
dataset AS (
 SELECT ARRAY[
   CAST(ROW('Sally', 'engineering', ARRAY[1,2,3,4]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))),
   CAST(ROW('John', 'finance', ARRAY[7,8,9]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))),
   CAST(ROW('Amy', 'devops', ARRAY[12,13,14,15]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER)))
 ] AS users
),
users AS (
 SELECT person, score
 FROM
   dataset,
   UNNEST(dataset.users) AS t(person),
   UNNEST(person.scores) AS t(score)
)
SELECT person.name, score FROM users
ORDER BY (score) DESC
LIMIT 1

Cette requête renvoie :

+--------------+
| name | score |
+--------------+
| Amy  | 15    |
+--------------+

Considérations pour CROSS JOIN et UNNEST

Si UNNEST est utilisé sur un ou plusieurs tableaux de la requête, et que l’un des tableaux est NULL, la requête ne renvoie aucune ligne. Si UNNEST est utilisé sur un tableau qui est une chaîne vide, la chaîne vide est renvoyée.

Par exemple, dans la requête suivante, le deuxième tableau étant null, la requête ne renvoie aucune ligne.

SELECT 
    col1, 
    col2 
FROM UNNEST (ARRAY ['apples','oranges','lemons']) AS t(col1)
CROSS JOIN UNNEST (ARRAY []) AS t(col2)

Dans l’exemple suivant, le deuxième tableau est modifié pour contenir une chaîne vide. Pour chaque ligne, la requête renvoie la valeur dans col1 et une chaîne vide pour la valeur dans col2. La chaîne vide du deuxième tableau est requise pour que les valeurs du premier tableau soient renvoyées.

SELECT 
    col1, 
    col2 
FROM UNNEST (ARRAY ['apples','oranges','lemons']) AS t(col1)
CROSS JOIN UNNEST (ARRAY ['']) AS t(col2)