Esempi di query complesse con JSONB

Nelle lezioni precedenti abbiamo visto le basi di JSONB: come creare, modificare ed estrarre dati. Ora è il momento delle vere sfide — query complesse che ti faranno vedere tutta la potenza di questo tipo di dato.

Immagina un negozio online con un catalogo di prodotti. Ogni prodotto ha info base (nome, ID), ma le caratteristiche possono essere molto diverse: un laptop ha RAM e CPU, i vestiti — taglie e materiali, i libri — autori e generi. Salvare tutto in tabelle separate? Scomodo. In JSONB? Perfetto! Ma come trovi tutti i prodotti di un certo brand, li ordini per prezzo o calcoli statistiche per categoria? Come lavori con dati che non sono in colonne normali, ma nascosti dentro una struttura JSON?

Oggi vediamo scenari reali: dal filtraggio semplice a query complesse con raggruppamento e aggregazione. Vedrai come JSONB trasforma PostgreSQL in uno strumento super flessibile per qualsiasi tipo di dato.

Filtraggio dei dati in JSONB

Filtrare i dati è come usare un colino per il tè: tieni solo quello che ti serve e togli il resto. Con JSONB è ancora più interessante, perché puoi filtrare non solo sulle colonne normali, ma anche su dati nascosti in profondità nella struttura JSON.

Operatori per filtrare JSONB:

• @> — "JSONB-contiene". Controlla se l’oggetto JSONB contiene il sottoinsieme specificato.
• ? — "Chiave presente". Controlla se la chiave specificata esiste nell’oggetto JSONB.
• ?| — "Almeno una chiave presente". Controlla se almeno una delle chiavi specificate esiste.
• ?& — "Tutte le chiavi presenti". Controlla che tutte le chiavi specificate esistano.

Esempio: filtrare per chiave e valore. Supponiamo di avere una tabella products con una colonna details che salva info JSONB sui prodotti:

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

Esempio di dati:

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"}}');

Risultato:

Per trovare tutti i prodotti con brand "Apple":

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

Risultato:

Se vuoi trovare tutti i prodotti che hanno la chiave specs, usa l’operatore ?:

SELECT *
FROM products 
WHERE details ? 'specs';

Risultato:

Tutte le righe hanno il campo details e la chiave specs.

Ordinamento dei dati in JSONB

A volte ti serve ordinare i dati non sulle colonne normali, ma sui valori che stanno dentro JSONB. Per farlo puoi usare gli operatori ->> (estrae il valore come testo) e CAST per convertire il testo nel tipo che ti serve.

Esempio: ordiniamo i prodotti per prezzo:

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

Risultato:

Raggruppamento dei dati in JSONB

Il raggruppamento ti permette di aggregare dati e mostrare statistiche. Ad esempio, puoi vedere quanti prodotti ci sono per ogni brand.

Esempio: contiamo quanti prodotti ci sono per ogni brand:

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

Risultato:

Esempi pratici

Filtraggio e raggruppamento. Contiamo quanti prodotti costano più di 600 per ogni brand:

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

Risultato:

Ordinamento dopo il raggruppamento. Ora ordiniamo i brand per numero di prodotti:

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

Query complessa: filtraggio, ordinamento, raggruppamento

Immagina di voler trovare i brand che hanno prodotti sopra i 600 e scegliere il prodotto più economico per ogni brand. Ecco come si fa:

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;

Risultato:

Errori tipici e consigli

Errore: Uso sbagliato degli operatori. Non confondere gli operatori -> e ->>: il primo restituisce un oggetto, il secondo — un valore testo.

Errore: Problemi di performance. Se fai spesso query complesse, crea un indice GIN sulla colonna JSONB.

Errore: Problemi di tipo. I valori da JSONB sono stringhe, quindi ricordati di usare CAST.

Esempio di creazione indice:

CREATE INDEX idx_products_details ON products USING GIN (details);

Ora il filtraggio tipo details @> '{"brand": "Apple"}' andrà molto più veloce.