Ricerca nei dati dell'array: ANY, ALL, array_contains()

Gli array vengono spesso usati per salvare più valori in una sola colonna — che siano tag, numeri, ruoli o preferenze. Ma come facciamo a capire se nell'array c'è il valore che ci serve, oppure se tutti gli elementi rispettano una certa condizione? PostgreSQL ci dà strumenti comodissimi per queste verifiche: ANY, ALL e array_contains(). Vediamoli uno per uno.

Che cos'è ANY?

Immagina di avere un array con dentro vari valori, tipo ID di prodotti, numeri di telefono o una lista di hobby. Spesso ti serve controllare se nell'array c'è un certo valore. Qui entra in gioco l'operatore ANY. Ti permette di verificare se il nostro "oggetto cercato" è presente nell'array.

Sintassi di ANY

SELECT *
FROM your_table
WHERE value = ANY(array_column);

Qui:

• array_column — è l'array che stiamo controllando.
• value — è il valore che cerchiamo nell'array.

Esempio di utilizzo di ANY

Supponiamo di avere una tabella students con questa struttura:

Ci interessano gli studenti che tra gli hobby hanno il nuoto. La query sarà così:

SELECT *
FROM students
WHERE 'nuoto' = ANY(hobbies);

Risultato:

In breve, ANY controlla: "C'è questo valore nell'array?".

Fidati, ma controlla tutto! Usiamo ALL

Ora pensa alla situazione opposta: vuoi essere sicuro che tutti gli elementi dell'array rispettino una certa regola. Per questo PostgreSQL offre l'operatore ALL. Con lui puoi verificare se tutti gli elementi dell'array, ad esempio, sono uguali a un certo valore, sono maggiori di un certo numero o rispettano qualsiasi altra condizione logica.

Sintassi di ALL

SELECT *
FROM your_table
WHERE value < ALL(array_column);

Qui:

• array_column — è l'array che stiamo controllando.
• value < ALL(...) significa che tutti gli elementi dell'array devono essere maggiori del valore.

Torniamo alla nostra tabella students. Supponiamo di voler trovare gli studenti che hanno tutti gli hobby che iniziano con la lettera "l". Query:

SELECT *
FROM students
WHERE 'lettura' = ALL(hobbies);

Il risultato sarà vuoto, perché nessuno ha tutti gli hobby uguali a "lettura". Per rendere l'esempio più chiaro (e funzionante), pensiamo a dati numerici.

Supponiamo di avere una tabella orders:

Trova tutti gli ordini dove ogni voce costa meno di 400:

SELECT *
FROM orders
WHERE 400 > ALL(prices);

Risultato:

Filtrare le righe in base al contenuto dell'array

Abbiamo visto le funzioni e gli operatori base per cercare dati negli array, ma come si usano nella pratica? Vediamo qualche esempio.

Esempio 1: Trovare studenti che hanno almeno un hobby — musica

Tabella di partenza:

SELECT *
FROM students
WHERE 'musica' = ANY(hobbies);

Risultato:

Esempio 2: Trovare ordini con prezzi sopra i 100 in tutte le voci

Tabella di partenza orders:

SELECT *
FROM orders
WHERE 100 < ALL(prices);

Risultato:

Errori tipici e come evitarli

Quando lavori con gli array, soprattutto per cercare dati, ci sono alcune trappole in cui è facile cadere.

Errore: Uso sbagliato di ANY o ALL. Per esempio, se scambi gli operatori, puoi ottenere risultati strani.

-- Variante sbagliata:
SELECT *
FROM students
WHERE hobbies = ANY('lettura');

Qui l'errore è che hobbies è un array, mentre 'lettura' è una stringa. È proprio l'array che va controllato con ANY, non il contrario.

Errore: L'indicizzazione degli array parte da 1, non da 0. Se usi gli array in altre funzioni o provi a estrarre elementi a mano, occhio alle particolarità di PostgreSQL.

Errore: Mancanza di indicizzazione. Se usi spesso gli array per ricerche (ANY, ALL), indicizzare può velocizzare un sacco le query.