UNNEST e aggregazione
Trasformi gli array in righe e viceversa
UNNEST e aggregazione è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 3 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.
Che cos'è UNNEST
PostgreSQL consente di memorizzare gli array all'interno di una singola colonna. A volte, però, è necessario lavorare con ogni elemento separatamente: è qui che entra in gioco UNNEST.
UNNEST è una funzione che restituisce un insieme di righe e che espande un array in una riga per ogni elemento. Lo si può considerare il contrario dell'aggregazione: invece di riunire molte righe in una, espande un valore in molte righe.
Esempio base di UNNEST
L'utilizzo più semplice di UNNEST consiste nel passare direttamente un array letterale. Ogni elemento diventa una riga autonoma nel set di risultati.
Qui espandiamo un semplice array di numeri interi in righe singole:
SELECT UNNEST(ARRAY[10, 20, 30, 40]) AS value;UNNEST con array di testo
UNNEST funziona con qualsiasi tipo di array, incluso il testo. È utile quando si dispone di una colonna che memorizza tag, categorie o dati simili a un elenco separato da virgole, codificati come array PostgreSQL.
SELECT UNNEST(ARRAY['apple', 'banana', 'cherry']) AS fruit;UNNEST da una colonna di tabella
La vera potenza di UNNEST emerge quando lo si utilizza su una colonna di una tabella reale. Ogni riga della tabella può avere un array di lunghezza diversa e UNNEST espanderà tutti gli array in righe singole.
In questo esempio, una tabella products ha una colonna array di testo tags:
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT,
tags TEXT[]
);
INSERT INTO products (name, tags) VALUES
('Laptop', ARRAY['electronics', 'computing', 'portable']),
('Shirt', ARRAY['clothing', 'casual']),
('Blender', ARRAY['kitchen', 'electronics']);
SELECT name, UNNEST(tags) AS tag
FROM products;Contare le occorrenze dei tag
Dopo aver applicato UNNEST a una colonna array, può trattare i risultati come qualsiasi altra riga e applicare funzioni di aggregazione. Qui contiamo quanti prodotti sono associati a ciascun tag.
Il pattern è: UNNEST in una sottoquery o in una CTE, quindi GROUP BY sul valore ottenuto dall'espansione:
SELECT tag, COUNT(*) AS product_count
FROM (
SELECT UNNEST(tags) AS tag
FROM products
) AS expanded
GROUP BY tag
ORDER BY product_count DESC;UNNEST con ordinalità
A volte la posizione di un elemento all'interno dell'array è importante. PostgreSQL fornisce WITH ORDINALITY per associare un numero di riga a ogni elemento espanso, così da conoscerne l'indice originale nell'array (a partire da 1).
SELECT val, pos
FROM UNNEST(ARRAY['first', 'second', 'third']) WITH ORDINALITY AS t(val, pos);Utilizzare l'ordinalità su una tabella
WITH ORDINALITY è particolarmente utile quando si memorizzano elenchi ordinati in un array. Si consideri, ad esempio, una tabella di playlist in cui l'ordine dei brani è importante:
CREATE TABLE playlists (
id SERIAL PRIMARY KEY,
title TEXT,
tracks TEXT[]
);
INSERT INTO playlists (title, tracks) VALUES
('Morning Mix', ARRAY['Song A', 'Song B', 'Song C']);
SELECT p.title, track, position
FROM playlists p,
UNNEST(p.tracks) WITH ORDINALITY AS t(track, position)
ORDER BY p.id, position;Filtrare dopo UNNEST
Poiché UNNEST trasforma gli elementi di un array in righe, può filtrarli con una normale clausola WHERE. In questo modo può trovare tutte le righe il cui array contiene un valore specifico senza utilizzare l'operatore di contenimento degli array.
SELECT DISTINCT name
FROM products,
UNNEST(tags) AS tag
WHERE tag = 'electronics';Aggregare nuovamente in un array
Il procedimento inverso di UNNEST è ARRAY_AGG. Dopo aver espanso le righe e averle trasformate o filtrate, può raccogliere nuovamente i risultati in un array. Questo schema — espandere, elaborare, aggregare nuovamente — è un pattern comune in PostgreSQL.
SELECT ARRAY_AGG(tag ORDER BY tag) AS sorted_tags
FROM (
SELECT UNNEST(ARRAY['cherry', 'apple', 'banana']) AS tag
) AS t;Rimozione degli elementi duplicati dagli array
Un uso pratico del ciclo unnest-aggregate di andata e ritorno consiste nel rimuovere gli elementi duplicati da un array. Applichi UNNEST all'array, applichi DISTINCT, quindi raccolga nuovamente gli elementi con ARRAY_AGG:
SELECT ARRAY_AGG(DISTINCT tag ORDER BY tag) AS unique_tags
FROM UNNEST(ARRAY['sql', 'database', 'sql', 'postgresql', 'database']) AS tag;Combinazione di più colonne array
È possibile eseguire UNNEST in parallelo su più array nella clausola FROM. PostgreSQL abbina gli elementi in base alla posizione. Se gli array hanno lunghezze diverse, quello più corto produce valori NULL per le posizioni aggiuntive dell'array più lungo.
SELECT key, value
FROM UNNEST(
ARRAY['name', 'city', 'role'],
ARRAY['Alice', 'Istanbul', 'DBA']
) AS t(key, value);Verifica delle conoscenze
Verifichi la Sua comprensione di UNNEST e dell'aggregazione degli array in PostgreSQL.
Riepilogo della lezione
In questa lezione ha imparato a trasformare gli array in righe e viceversa utilizzando le funzioni integrate di PostgreSQL:
- UNNEST — espande un array in una riga per ogni elemento e funziona sia con i valori letterali sia con le colonne delle tabelle.
- WITH ORDINALITY — associa un indice di posizione a ogni elemento ottenuto da UNNEST, consentendo di conoscerne l'ordine originale nell'array.
- Filtraggio — dopo aver eseguito UNNEST, può utilizzare WHERE proprio come con qualsiasi altra riga.
- ARRAY_AGG — è l'opposto di UNNEST: raccoglie nuovamente le righe in un array, facoltativamente con ORDER BY o DISTINCT.
- UNNEST parallelo — espande più array nella clausola FROM affiancandoli e abbinandone gli elementi in base alla posizione.
Padroneggiare questo modello di trasformazione, elaborazione e riaggregazione degli array Le consente di gestire i dati array con tutta la potenza espressiva di SQL.
Domande Frequenti
La lezione «UNNEST e aggregazione» è gratuita?
Sì — il testo completo di «UNNEST e aggregazione» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.
Cosa imparerò in «UNNEST e aggregazione»?
Trasformi gli array in righe e viceversa Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.
Ho bisogno di esperienza per iniziare SQL Academy?
Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 3 di 4.
Quanto tempo richiede la lezione «UNNEST e aggregazione»?
La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.
Posso scrivere ed eseguire codice in questa lezione SQL Academy?
Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.
Tutte le lezioni di questo corso
- Nozioni di base sulle colonne array
- Cercare all'interno degli array
- UNNEST e aggregazione
- Array e tabelle normalizzate a confronto