Esempi di query complesse con array: aggregazione, filtraggio, ordinamento

Oggi vediamo come spremere il massimo dagli array nelle query: raggruppare valori, filtrare in base al contenuto e persino ordinare direttamente dentro gli array. Non è teoria per la teoria — questi trucchetti li trovi in report, analytics, personalizzazione e un sacco di casi reali. È tutto semplice, basta capire il principio — ed è proprio quello che facciamo ora.

Aggregazione dei dati con array

Lavorare con gli array dà il meglio quando devi raggruppare dati. Invece di ricevere più righe — raccogli i valori che ti servono in un unico array ordinato. Così l’analisi è più semplice, i risultati sono più compatti e spesso eviti subquery inutili. Vediamo come funziona nella pratica.

Esempio 1: raggruppare dati in array con array_agg()

Quando vuoi raccogliere valori da più righe in un gruppo in un array, array_agg() è la tua migliore amica. È probabilmente la funzione più utile per lavorare con array durante l’aggregazione.

-- Abbiamo una tabella students con i campi id, name e course
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

-- Inseriamo alcune righe
INSERT INTO students (name, course) VALUES
('Alisa', 'Matematica'),
('Bob', 'Matematica'),
('Charlie', 'Fisica'),
('Dave', 'Fisica'),
('Emma', 'Matematica');

-- Raggruppiamo gli studenti per corso in array
SELECT course, array_agg(name) AS students
FROM students
GROUP BY course;

Risultato:

Raggruppare valori in array è comodo se vuoi passare i dati in un formato facilmente parsabile, tipo JSON.

Esempio 2: creare array annidati

E se avessimo un’altra tabella e volessimo raccogliere dati da due tabelle in array? Per esempio, una tabella courses con info sui docenti.

-- Creiamo la tabella dei docenti
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    teacher VARCHAR(100)
);

-- Inseriamo i dati
INSERT INTO courses (name, teacher) VALUES
('Matematica', 'Prof. Min'),
('Fisica', 'Prof. Peterson');

-- Query annidata per creare array
SELECT
    c.name AS course_name,
    array_agg(s.name) AS students,
    c.teacher
FROM
    courses c
LEFT JOIN
    students s
ON
    c.name = s.course
GROUP BY
    c.name, c.teacher;

Risultato:

Ora abbiamo una tabella comoda che mostra corsi, docenti e studenti in formato array.

Filtraggio dei dati con array

Gli array sono già uno strumento potente, ma la vera magia inizia quando impari a filtrare i dati usando gli array. Vuoi selezionare solo gli utenti che hanno una certa parola nella lista degli interessi? O ordini dove ogni prezzo supera una certa soglia? Puoi fare tutto direttamente in SQL — senza logica extra lato applicazione.

Esempio 1: filtrare righe in base agli elementi di un array

Supponiamo di voler trovare tutte le righe dove l’array contiene un certo valore, tipo cercare studenti iscritti al corso di matematica.

-- Filtriamo gli studenti iscritti ai corsi usando `ANY`
SELECT *
FROM students
WHERE course = ANY(ARRAY['Matematica', 'Fisica']);

Qui ANY ti permette di specificare un array di valori, e la query restituisce le righe dove course corrisponde almeno a uno dei valori nell’array.

Esempio 2: Verifica di intersezione tra array

Ora supponiamo di avere una tabella student_interests dove gli interessi degli studenti sono array. Vogliamo trovare gli studenti i cui interessi si incrociano con i nostri criteri.

-- Creiamo la tabella con gli interessi degli studenti
CREATE TABLE student_interests (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    interests TEXT[]
);

-- Inseriamo i dati
INSERT INTO student_interests (name, interests) VALUES
('Alisa', ARRAY['programmazione', 'musica']),
('Bob', ARRAY['sport', 'programmazione']),
('Charlie', ARRAY['lettura', 'fotografia']),
('Emma', ARRAY['musica', 'sport']);

-- Cerchiamo studenti interessati a programmazione o musica
SELECT *
FROM student_interests
WHERE interests && ARRAY['programmazione', 'musica'];

L’operatore && controlla l’intersezione tra due array. Se almeno un elemento dell’array a sinistra coincide con uno a destra, la riga passa il filtro.

Risultato:

Ordinamento degli array

A volte l’ordine dei valori dentro un array conta — soprattutto se raccogli l’array da righe diverse o vuoi preparare i dati per la visualizzazione. PostgreSQL ti permette di ordinare gli elementi direttamente nella query, senza bisogno di altro codice.

Esempio 1: ordinare i valori dentro un array

A volte serve ordinare gli elementi dentro un array. Per esempio, ordiniamo l’array degli interessi degli studenti in ordine alfabetico.

-- Ordiniamo gli elementi dell’array con la funzione `array_sort()`
SELECT 
    name,
    array_sort(interests) AS sorted_interests
FROM 
    student_interests;

Risultato:

Esempio 2: ordinare le righe in base alla lunghezza dell’array

Ora supponiamo di voler ordinare gli studenti in base al numero dei loro interessi — dai più appassionati ai più "noiosi".

-- Ordiniamo le righe in base alla lunghezza dell’array
SELECT 
    name, 
    interests, 
    array_length(interests, 1) AS interests_count
FROM 
    student_interests
ORDER BY 
    interests_count DESC;

Risultato:

Anche se tutti gli studenti hanno lo stesso numero di interessi nell’esempio, una query simile si può adattare a tabelle più grandi.