Exemples de requêtes complexes avec JSONB

Dans les leçons précédentes, on a vu les bases de JSONB : comment créer, modifier et extraire des données. Maintenant, place au vrai challenge — les requêtes complexes qui montrent toute la puissance de ce type de données.

Imagine une boutique en ligne avec un catalogue de produits. Chaque produit a des infos de base (nom, ID), mais les caractéristiques peuvent être super différentes : un laptop a de la RAM et un CPU, des fringues ont des tailles et des matériaux, les livres ont des auteurs et des genres. Tout mettre dans des tables séparées ? Pas pratique. En JSONB ? Parfait ! Mais comment trouver tous les produits d'une certaine marque, les trier par prix ou faire des stats par catégorie ? Comment bosser avec des données qui ne sont pas dans des colonnes classiques, mais planquées dans une structure JSON ?

Aujourd'hui, on va décortiquer des scénarios réels : de la simple filtration à des requêtes bien velues avec groupement et agrégation. Tu vas voir comment JSONB transforme PostgreSQL en un outil ultra-flexible pour gérer n'importe quelles données.

Filtration des données dans JSONB

Filtrer des données, c'est comme passer le thé au tamis : tu gardes que ce qui t'intéresse et tu vires le reste. Avec JSONB, c'est encore plus fun, parce qu'on peut filtrer non seulement sur des colonnes classiques, mais aussi sur des données planquées bien profond dans la structure JSON.

Opérateurs pour filtrer du JSONB :

• @> — "JSONB-contient". Vérifie si l'objet JSONB contient le sous-ensemble indiqué.
• ? — "Clé présente". Vérifie si la clé indiquée existe dans l'objet JSONB.
• ?| — "Au moins une clé présente". Vérifie si au moins une des clés indiquées existe.
• ?& — "Toutes les clés présentes". Vérifie que toutes les clés indiquées existent.

Exemple : filtrer par clé et valeur. Imaginons qu'on a une table products avec une colonne details qui stocke les infos des produits en JSONB :

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    details JSONB
);

Exemple de données :

INSERT INTO products (name, details) VALUES
('Laptop', '{"brand": "Dell", "price": 1200, "specs": {"ram": "16GB", "cpu": "i7"}}'),
('Smartphone', '{"brand": "Apple", "price": 800, "specs": {"ram": "4GB", "cpu": "A15"}}'),
('Tablet', '{"brand": "Samsung", "price": 500, "specs": {"ram": "8GB", "cpu": "Exynos"}}');

Résultat :

Pour trouver tous les produits avec brand "Apple" :

SELECT *
FROM products 
WHERE details @> '{"brand": "Apple"}';

Résultat :

Si tu veux trouver tous les produits qui ont la clé specs, utilise l'opérateur ? :

SELECT *
FROM products 
WHERE details ? 'specs';

Résultat :

Toutes les lignes contiennent le champ details et la clé specs.

Tri des données dans JSONB

Parfois, tu veux trier les données non pas sur des colonnes classiques, mais sur des valeurs qui sont à l'intérieur du JSONB. Pour ça, tu peux utiliser les opérateurs ->> (extraction de la valeur en texte) et CAST pour convertir la valeur texte dans le type voulu.

Exemple : on trie les produits par prix :

SELECT *
FROM products 
ORDER BY (details->>'price')::INTEGER;

Résultat :

Groupement des données dans JSONB

Le groupement permet d'agréger des données et de sortir des stats. Par exemple, tu peux savoir combien de produits il y a pour chaque marque.

Exemple : on compte le nombre de produits pour chaque marque :

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
GROUP BY details->>'brand';

Résultat :

Exemples pratiques

Filtration et groupement. On compte combien de produits à plus de 600 pour chaque marque :

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
WHERE (details->>'price')::INTEGER > 600
GROUP BY details->>'brand';

Résultat :

Tri après groupement. Maintenant, on trie les marques par nombre de produits :

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
GROUP BY details->>'brand'
ORDER BY product_count DESC;

Requête complexe : filtration, tri, groupement

Imaginons que tu veux trouver les marques qui ont des produits à plus de 600 et choisir le produit le moins cher pour chaque marque. Voilà comment faire :

WITH filtered_products AS (
    SELECT *
    FROM products
    WHERE (details->>'price')::INTEGER > 600
)
SELECT
    details->>'brand' AS brand,
    MIN((details->>'price')::INTEGER) AS min_price
FROM filtered_products
GROUP BY details->>'brand'
ORDER BY min_price;

Résultat :

Erreurs courantes et astuces

Erreur : Mauvaise utilisation des opérateurs. Ne confonds pas les opérateurs -> et ->> : le premier renvoie un objet, le second — une valeur texte.

Erreur : Problèmes de perf. Si tu fais souvent des requêtes complexes, crée un index GIN sur la colonne JSONB.

Erreur : Problèmes de types. Les valeurs extraites du JSONB sont des chaînes, donc pense à utiliser CAST.

Exemple de création d'index :

CREATE INDEX idx_products_details ON products USING GIN (details);

Maintenant, la filtration du genre details @> '{"brand": "Apple"}' sera bien plus rapide.