Extraction de données imbriquées : jsonb_to_recordset()

On passe maintenant à des scénarios un peu plus velus avec les données au format JSONB — genre extraire des données imbriquées et les transformer en lignes de table. Tu te demandes pourquoi s’embêter ? C’est simple ! Imagine qu’on te file un objet JSON avec un tableau d’achats et qu’on te demande de calculer le total ou de tout afficher en mode tableau pour un rapport. On va voir ça tout de suite !

Pourquoi ne pas juste bosser avec le JSON comme du texte ou une structure ? Mate ce cas : dans plein d’applis réelles, les données sont stockées sous forme de tableaux JSON :

[
  { "id": 1, "product_name": "Laptop", "price": 1200 },
  { "id": 2, "product_name": "Smartphone", "price": 800 },
  { "id": 3, "product_name": "Tablet", "price": 400 }
]

C’est pratique, mais pour analyser, on a souvent besoin de transformer ce tableau en table pour filtrer, trier ou agréger. Imagine : «Tous les achats de plus de 500 dollars». Le JSONB tout seul, c’est pas super pratique pour ça. C’est là que jsonb_to_recordset() débarque !

Travailler avec jsonb_to_recordset()

La fonction jsonb_to_recordset() permet de transformer un tableau d’objets JSONB en lignes de table. En gros, chaque élément du tableau devient une ligne, et les clés deviennent des colonnes. Cette fonction est juste indispensable quand t’as des données bien imbriquées ou des tableaux d’objets à gérer.

Syntaxe

SELECT *
FROM jsonb_to_recordset('[ tableau JSONB ]') AS alias(colonne1 TYPE, colonne2 TYPE, ...);

• [ tableau JSONB ] : le tableau d’objets JSON dont tu veux extraire les données.
• AS alias : on crée un nom temporaire pour la table résultante.
• colonne1 TYPE, colonne2 TYPE : tu définis les noms de colonnes et leurs types (genre INTEGER, TEXT, NUMERIC).

Exemple : transformer un tableau JSONB en lignes

Imaginons qu’on a la table suivante :

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT,
    products JSONB
);

Et dedans, on a ces données :

Maintenant, objectif : afficher la liste de tous les produits de toutes les commandes en mode tableau. Voilà comment faire avec jsonb_to_recordset() :

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC);

Résultat :

Exemple : filtrer les données

On corse un peu : on veut afficher seulement les produits des commandes qui coûtent plus de 100 dollars :

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
WHERE
    p.price > 100;

Résultat :

Exemple : agrégation des données

Et si on veut calculer le total de tous les produits par commande ? Facile, on utilise les fonctions d’agrégation :

SELECT
    o.customer_name,
    SUM(p.price) AS total_amount
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
GROUP BY
    o.customer_name;

Résultat :

Remarques importantes

Assure-toi que la structure du tableau JSON est la même pour tous les objets. Si un objet a des clés différentes ou des structures imbriquées, tu risques d’avoir des erreurs ou des résultats bizarres.

Définis bien les types de données pour les colonnes extraites. Par exemple, si une clé contient une date, utilise DATE, pour les nombres — NUMERIC ou INTEGER.

Rappelle-toi que jsonb_to_recordset() ne marche qu’avec des tableaux JSONB ; ça ne fonctionne pas avec un objet tout seul.

Erreurs classiques et comment les éviter

Mauvaise utilisation des types de données : si dans ton tableau JSONB t’as des valeurs de types différents (genre une chaîne à la place d’un nombre), ça va planter. Le mieux, c’est de convertir les données au bon format avant d’utiliser la fonction.

Accès à de mauvaises clés : si une clé manque dans un des objets du tableau, ça va râler. Vérifie bien la structure de tes données avant de lancer la requête.

Pas de données : si la colonne JSONB est vide (NULL), la fonction ne renverra rien. Dans ce cas, pense à ajouter des vérifs, genre COALESCE().

Cas d’usage concrets

jsonb_to_recordset() est super utilisé dans la vraie vie, genre pour gérer des commandes, analyser des rapports, logger les actions des users ou traiter des API externes. Par exemple :

• Dans les boutiques en ligne, tu peux facilement transformer des tableaux de produits en tables et faire des rapports.
• Un REST API peut te renvoyer des données en JSON, que tu peux analyser direct avec PostgreSQL.
• Les applis d’analytics utilisent souvent cette fonction pour gérer des données bien complexes et imbriquées.