Estrazione di dati annidati: jsonb_to_recordset()

Adesso passiamo a scenari un po' più tosti con i dati in formato JSONB — estrarre dati annidati e trasformarli in righe di tabella. Ti chiedi: ma perché dovrei farlo? Semplice! Immagina che ti danno un oggetto JSON con un array di acquisti e ti chiedono di calcolare la somma totale di tutti gli acquisti o di mostrarli in formato tabellare per un report. Vediamo subito come si fa!

Perché non lavorare semplicemente con JSON come testo o struttura? Facciamo un esempio. In tante app reali i dati vengono salvati come array JSON:

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

Comodo, sì, ma quando devi analizzare i dati spesso ti serve trasformare l'array in una tabella per operazioni come filtro, ordinamento e aggregazione. Immagina: «Tutti gli ordini sopra i 500 dollari». JSONB da solo non ti permette di farlo in modo così easy. Ed è qui che jsonb_to_recordset() ti salva la vita.

Lavorare con jsonb_to_recordset()

La funzione jsonb_to_recordset() ti permette di trasformare un array di oggetti JSONB in righe di tabella. Letteralmente ogni elemento dell'array diventa una riga, e le chiavi diventano colonne. Questa funzione è una bomba quando hai dati annidati o array di oggetti.

Sintassi

SELECT *
FROM jsonb_to_recordset('[ array JSONB ]') AS alias(colonna1 TIPO, colonna2 TIPO, ...);

• [ array JSONB ]: l'array di oggetti JSON da cui estrai i dati.
• AS alias: crei un nome temporaneo per la tabella risultante.
• colonna1 TIPO, colonna2 TIPO: decidi come chiamare le colonne e che tipo di dati useranno (tipo INTEGER, TEXT, NUMERIC).

Esempio: trasformare un array JSONB in righe

Supponiamo di avere questa tabella:

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

E nella tabella ci sono questi dati:

Ora la missione: mostrare la lista di tutti i prodotti di tutti gli ordini in formato tabellare. Ecco come si fa con 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);

Risultato:

Esempio: filtrare i dati

Alziamo il livello. Vogliamo mostrare solo i prodotti degli ordini che costano più di 100 dollari:

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;

Risultato:

Esempio: aggregare i dati

Che ne dici di calcolare la somma totale di tutti i prodotti negli ordini? Basta usare le funzioni di aggregazione:

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;

Risultato:

Note importanti

Assicurati che la struttura dell'array JSON sia uguale per tutti gli oggetti. Se un oggetto ha chiavi diverse o strutture annidate, potresti beccarti errori o comportamenti strani.

Imposta bene i tipi di dati per le colonne estratte. Per esempio, se una chiave contiene una data, usa DATE, per i numeri — NUMERIC o INTEGER.

Ricorda che jsonb_to_recordset() trasforma solo array JSONB; con oggetti singoli non funziona.

Errori tipici e come evitarli

Uso sbagliato dei tipi di dati: se nell'array JSONB ci sono valori con tipi diversi (tipo una stringa invece di un numero), ti darà errore. Meglio sistemare i dati nel formato giusto prima di usare la funzione.

Accesso a chiavi sbagliate: se una chiave manca in uno degli oggetti dell'array, avrai errore. Controlla la struttura dei dati prima di lanciare la query.

Dati mancanti: se la colonna JSONB è vuota (NULL), la funzione non restituirà risultati. In questi casi aggiungi controlli, tipo COALESCE().

Applicazioni pratiche

jsonb_to_recordset() è usatissima in casi reali, tipo gestione ordini, analisi di report, logging delle azioni utente e gestione di API esterne. Per esempio:

• Negli e-commerce puoi trasformare facilmente array di prodotti in tabelle e fare report.
• Un REST API può restituire dati in formato JSON, che puoi analizzare comodamente con PostgreSQL.
• App di analytics usano spesso questa funzione per gestire dati complessi e multilivello.