Fonctions d’agrégation JSON dans SQLite

Introduction

Après avoir appris à stocker, interroger et modifier des données JSON, vous pourriez avoir besoin de regrouper plusieurs valeurs au sein d’une structure JSON. SQLite propose des fonctions d’agrégation spécifiques au JSON qui permettent de construire des tableaux et des objets à partir de résultats de requêtes.

Les fonctions d’agrégation JSON

SQLite propose deux fonctions d’agrégation principales pour le JSON :

1. json_group_array() - Crée un tableau JSON à partir d’un ensemble de valeurs
2. json_group_object() - Crée un objet JSON à partir de paires clé-valeur

Ces fonctions sont particulièrement utiles pour transformer des données tabulaires en structures JSON hiérarchiques.

La fonction json_group_array()

La fonction json_group_array() agrège une collection de valeurs en un tableau JSON.

Syntaxe

json_group_array(value)

Exemples simples

-- Créer une table d'exemple
CREATE TABLE produits (
    id INTEGER PRIMARY KEY,
    nom TEXT,
    prix REAL,
    categorie TEXT
);

-- Insérer quelques données
INSERT INTO produits (nom, prix, categorie) VALUES
('Smartphone', 499.99, 'Électronique'),
('Tablette', 299.99, 'Électronique'),
('Casque audio', 79.99, 'Accessoires'),
('Chargeur', 19.99, 'Accessoires');

-- Créer un tableau des noms de produits
SELECT json_group_array(nom) AS liste_produits FROM produits;
-- Résultat : ["Smartphone","Tablette","Casque audio","Chargeur"]

Utilisation avec GROUP BY

La fonction est particulièrement utile avec GROUP BY pour créer des tableaux par groupe :

-- Créer un tableau de produits par catégorie
SELECT
    categorie,
    json_group_array(nom) AS produits_par_categorie
FROM produits
GROUP BY categorie;

-- Résultat :
-- Accessoires | ["Casque audio","Chargeur"]
-- Électronique | ["Smartphone","Tablette"]

Création de structures plus complexes

Vous pouvez également créer des structures plus complexes en combinant json_object() avec json_group_array() :

-- Créer un tableau d'objets avec les détails des produits
SELECT json_group_array(
           json_object(
               'id', id,
               'nom', nom,
               'prix', prix
           )
       ) AS catalogue_produits
FROM produits;

-- Résultat :
-- [{"id":1,"nom":"Smartphone","prix":499.99},{"id":2,"nom":"Tablette","prix":299.99},{"id":3,"nom":"Casque audio","prix":79.99},{"id":4,"nom":"Chargeur","prix":19.99}]

La fonction json_group_object()

La fonction json_group_object() crée un objet JSON en prenant deux colonnes : une pour les clés et une pour les valeurs.

Syntaxe

json_group_object(key, value)

Exemples simples

-- Créer un objet qui associe les IDs aux noms de produits
SELECT json_group_object(id, nom) AS produits_par_id
FROM produits;

-- Résultat :
-- {"1":"Smartphone","2":"Tablette","3":"Casque audio","4":"Chargeur"}

-- Créer un objet qui associe les catégories aux prix moyens
SELECT json_group_object(
           categorie,
           ROUND(AVG(prix), 2)
       ) AS prix_moyen_par_categorie
FROM produits
GROUP BY categorie;

-- Résultat :
-- {"Accessoires":49.99,"Électronique":399.99}

Construire des structures hiérarchiques

En combinant les fonctions d’agrégation JSON avec GROUP BY, vous pouvez créer des structures hiérarchiques complexes :

-- Créer une hiérarchie catégorie -> produits
SELECT json_object(
           'categories', json_group_object(
               categorie,
               json_group_array(
                   json_object('id', id, 'nom', nom, 'prix', prix)
               )
           )
       ) AS catalogue
FROM produits
GROUP BY categorie;

-- Résultat :
-- {"categories":{"Accessoires":[{"id":3,"nom":"Casque audio","prix":79.99},{"id":4,"nom":"Chargeur","prix":19.99}],"Électronique":[{"id":1,"nom":"Smartphone","prix":499.99},{"id":2,"nom":"Tablette","prix":299.99}]}}

Cas d’utilisation pratiques

1. Génération de rapports JSON

Les fonctions d’agrégation JSON sont parfaites pour générer des rapports structurés :

-- Une table de commandes et leurs articles
CREATE TABLE commandes (
    id INTEGER PRIMARY KEY,
    client_id INTEGER,
    date TEXT
);

CREATE TABLE articles_commande (
    id INTEGER PRIMARY KEY,
    commande_id INTEGER,
    produit_id INTEGER,
    quantite INTEGER,
    prix_unitaire REAL
);

-- Générer un rapport de commandes au format JSON
SELECT json_group_array(
           json_object(
               'commande_id', c.id,
               'client_id', c.client_id,
               'date', c.date,
               'articles', (
                   SELECT json_group_array(
                              json_object(
                                  'produit_id', ac.produit_id,
                                  'quantite', ac.quantite,
                                  'prix_unitaire', ac.prix_unitaire,
                                  'sous_total', ac.quantite * ac.prix_unitaire
                              )
                          )
                   FROM articles_commande ac
                   WHERE ac.commande_id = c.id
               ),
               'total', (
                   SELECT SUM(quantite * prix_unitaire)
                   FROM articles_commande
                   WHERE commande_id = c.id
               )
           )
       ) AS rapport_commandes
FROM commandes c;

2. Construction d’API JSON à partir de données relationnelles

Vous pouvez utiliser ces fonctions pour transformer des données relationnelles en un format adapté aux API REST :

-- Table utilisateurs et publications
CREATE TABLE utilisateurs (
    id INTEGER PRIMARY KEY,
    nom TEXT,
    email TEXT
);

CREATE TABLE publications (
    id INTEGER PRIMARY KEY,
    utilisateur_id INTEGER,
    titre TEXT,
    contenu TEXT,
    date_creation TEXT
);

-- Créer une API-style JSON pour les utilisateurs et leurs publications
SELECT json_group_array(
           json_object(
               'id', u.id,
               'nom', u.nom,
               'email', u.email,
               'publications', (
                   SELECT json_group_array(
                              json_object(
                                  'id', p.id,
                                  'titre', p.titre,
                                  'contenu', p.contenu,
                                  'date_creation', p.date_creation
                              )
                          )
                   FROM publications p
                   WHERE p.utilisateur_id = u.id
               )
           )
       ) AS api_utilisateurs
FROM utilisateurs u;

3. Statistiques et agrégations avancées

-- Table de ventes
CREATE TABLE ventes (
    id INTEGER PRIMARY KEY,
    produit_id INTEGER,
    date TEXT,
    quantite INTEGER,
    montant REAL,
    region TEXT
);

-- Générer des statistiques par région et par mois
SELECT
    strftime('%Y-%m', date) AS mois,
    json_group_object(
        region,
        json_object(
            'ventes_totales', SUM(montant),
            'quantite_totale', SUM(quantite),
            'panier_moyen', ROUND(AVG(montant), 2),
            'produits_vendus', json_group_object(
                produit_id,
                SUM(quantite)
            )
        )
    ) AS statistiques_par_region
FROM ventes
GROUP BY mois
ORDER BY mois;

Les fonctions table-valued pour décomposer le JSON

SQLite fournit aussi des fonctions table-valued (fonctions qui renvoient des tables virtuelles) pour décomposer les structures JSON :

1. json_each() - Décompose un niveau de tableau ou d’objet JSON
2. json_tree() - Décompose récursivement une structure JSON à tous les niveaux

Utilisation avec les fonctions d’agrégation

En combinant ces fonctions avec les fonctions d’agrégation, vous pouvez transformer des données complexes :

-- Table avec des données JSON
CREATE TABLE analytics (
    id INTEGER PRIMARY KEY,
    date TEXT,
    metrics TEXT -- JSON contenant diverses métriques
);

-- Insertion d'un exemple
INSERT INTO analytics (date, metrics) VALUES
('2023-01-01', '{"vues":1250, "clics":85, "conversions":12, "sources":{"direct":450, "search":580, "social":220}}');

-- Extraire et regrouper les métriques
SELECT
    strftime('%Y-%m', date) AS mois,
    json_group_object(
        key,
        CASE
            WHEN json_type(value) = 'object' THEN
                (SELECT json_group_object(k, v)
                 FROM json_each(value)
                 WHERE json_each.key = key)
            ELSE value
        END
    ) AS metriques_mensuelles
FROM analytics,
     json_each(metrics)
GROUP BY mois;

Différences entre les versions TEXT et JSONB

À partir de SQLite 3.45.0, les fonctions d’agrégation sont également disponibles dans leurs variantes binaires JSONB :

• jsonb_group_array() - Version JSONB de json_group_array()
• jsonb_group_object() - Version JSONB de json_group_object()

Ces fonctions fonctionnent de la même manière mais renvoient le résultat au format JSONB, qui est plus efficace en termes de performances et d’espace disque.

-- Exemple avec JSONB (SQLite 3.45.0+)
SELECT jsonb_group_array(nom) AS liste_produits_jsonb FROM produits;

NOTE

Pour en savoir plus sur JSONB et ses avantages, consultez le tutoriel Format JSONB pour les performances.

Bonnes pratiques

1. Sélection sélective : Ne créez que les structures JSON dont vous avez réellement besoin pour éviter des résultats trop volumineux.

2. Préférer les sous-requêtes corrélées pour des structures imbriquées complexes, comme dans nos exemples.

3. Utiliser les types appropriés : Convertissez les valeurs numériques avant de les agréger pour garantir le bon format JSON.

4. Performances : Pour les grands ensembles de données, envisagez d’utiliser les fonctions JSONB pour de meilleures performances.

5. Lisibilité du code : Pour les requêtes complexes, décomposez les différentes parties en utilisant des CTEs (WITH) pour améliorer la lisibilité.

Prochaines étapes

Vous avez maintenant une compréhension avancée des fonctions JSON dans SQLite. Pour améliorer davantage les performances de vos applications, découvrez le format JSONB, qui permet de stocker le JSON sous une forme binaire plus efficace.