ARRAY_AGG et JSON en SQL : agréger vos données

Agréger des lignes en tableau ou en objet JSON directement dans une requête SQL, c'est possible — et c'est ce que font ARRAY_AGG et les fonctions JSON natives de votre SGBD. Ces outils transforment des résultats multi-lignes en structures de données lisibles en une seule passe, sans post-traitement applicatif. En entretien data, maîtriser ces fonctions vous distingue immédiatement des candidats qui ne connaissent que GROUP BY et COUNT.

Cet article explore ARRAY_AGG, JSON_AGG, JSON_OBJECT_AGG et leurs équivalents dans les principaux SGBD (PostgreSQL, MySQL, SQLite, BigQuery). Vous trouverez des exemples concrets, un tableau comparatif entre bases de données, et les erreurs classiques à éviter.

Pourquoi agréger en tableau ou en JSON ?

Dans une base relationnelle classique, chaque relation est représentée par des lignes séparées. Pourtant, de nombreux cas métier nécessitent de consolider ces lignes en une seule cellule structurée : liste des commandes d'un client, tags associés à un article, historique d'actions d'un utilisateur.

Sans ARRAY_AGG ou JSON_AGG, vous êtes forcé de récupérer toutes les lignes côté application et de les regrouper manuellement. C'est coûteux en mémoire réseau et en logique de code. Avec ces fonctions, le SGBD fait le travail en une seule requête.

Ces cas d'usage apparaissent fréquemment dans les APIs REST, les exports de données et les pipelines ETL. Les connaître en entretien montre que vous pensez performance et maintenabilité.

ARRAY_AGG : regrouper des valeurs en tableau

Syntaxe de base

SELECT
  client_id,
  ARRAY_AGG(produit_nom) AS produits_achetes
FROM commandes
GROUP BY client_id;

Ce résultat retourne une ligne par client avec un tableau de tous les produits commandés. Sans ARRAY_AGG, il faudrait autant de lignes que de commandes par client.

Trier les éléments du tableau

ARRAY_AGG accepte un ORDER BY interne, ce qui est particulièrement utile pour les séquences chronologiques :

SELECT
  utilisateur_id,
  ARRAY_AGG(page_visitee ORDER BY visite_at ASC) AS parcours
FROM visites
GROUP BY utilisateur_id;

Cette requête reconstruit le parcours de navigation de chaque utilisateur dans l'ordre chronologique — un cas classique d'analyse de tunnel. Vous pouvez approfondir ce type d'analyse dans l'article sur l'analyse d'un tunnel de conversion e-commerce.

Éliminer les doublons avec DISTINCT

SELECT
  article_id,
  ARRAY_AGG(DISTINCT tag_nom) AS tags_uniques
FROM articles_tags
GROUP BY article_id;

DISTINCT à l'intérieur d'ARRAY_AGG filtre les valeurs dupliquées avant de construire le tableau. Notez que DISTINCT et ORDER BY ne sont pas combinables dans la même ARRAY_AGG sous PostgreSQL.

JSON_AGG : agréger des lignes en tableau JSON

Principe et cas d'usage

JSON_AGG (PostgreSQL, BigQuery) est similaire à ARRAY_AGG, mais produit un tableau JSON plutôt qu'un tableau natif. Elle est idéale pour construire des réponses API directement en base.

SELECT
  client_id,
  JSON_AGG(
    JSON_BUILD_OBJECT(
      'commande_id', commande_id,
      'montant', montant,
      'date', commande_date
    ) ORDER BY commande_date DESC
  ) AS commandes_json
FROM commandes
GROUP BY client_id;

Cette requête produit une colonne contenant un tableau JSON structuré par client — exactement le format attendu par la plupart des APIs REST modernes.

Filtrer avec FILTER

PostgreSQL permet d'ajouter un filtre conditionnel directement dans l'agrégation :

SELECT
  client_id,
  JSON_AGG(produit_nom) FILTER (WHERE statut = 'livre') AS produits_livres
FROM commandes
GROUP BY client_id;

Le mot-clé FILTER (WHERE ...) applique une condition avant l'agrégation. C'est plus propre et plus performant qu'un CASE WHEN imbriqué. Cette technique se rapproche des usages avancés décrits dans le guide CASE WHEN SQL avancé.

JSON_OBJECT_AGG : construire des objets clé-valeur

JSON_OBJECT_AGG(clé, valeur) construit un objet JSON unique à partir de paires de colonnes. Très utile pour pivoter des données dynamiquement.

SELECT
  produit_id,
  JSON_OBJECT_AGG(langue_code, traduction_texte) AS traductions
FROM traductions
GROUP BY produit_id;

Résultat pour un produit donné :

{"fr": "Chaise", "en": "Chair", "de": "Stuhl"}

C'est une alternative élégante au PIVOT statique quand les clés ne sont pas connues à l'avance.

Comparatif par SGBD