Búsqueda de datos en un array: ANY, ALL, array_contains()

Los arrays se usan mucho para guardar varios valores en una sola columna — ya sean etiquetas, números, roles o preferencias. Pero, ¿cómo saber si un array contiene el valor que buscas, o al revés — si todos los elementos cumplen cierta condición? PostgreSQL te da herramientas muy cómodas para estas comprobaciones: ANY, ALL y array_contains(). Vamos a ver cada una por separado.

¿Qué es ANY?

Imagina que tienes un array donde hay varios valores, por ejemplo, IDs de productos, números de teléfono o una lista de hobbies. Muchas veces necesitas comprobar si en el array está cierto valor. Aquí entra el operador ANY. Te permite ver si tu "objeto buscado" está en el array.

Sintaxis de ANY

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

Aquí:

• array_column — es el array que estamos comprobando.
• value — es el valor que buscas dentro del array.

Ejemplo de uso de ANY

Supón que tienes una tabla students con esta estructura:

Nos interesan los estudiantes que tienen natación entre sus hobbies. La consulta sería así:

SELECT *
FROM students
WHERE 'natación' = ANY(hobbies);

Resultado:

En resumen, ANY comprueba: "¿Está este valor en el array?".

¡Confía, pero revisa todo! Usando ALL

Ahora imagina lo contrario: necesitas asegurarte de que todos los elementos del array cumplen cierta regla. Para eso PostgreSQL tiene el operador ALL. Con él puedes comprobar si todos los elementos del array, por ejemplo, son iguales a un valor, son mayores que cierto número o cumplen cualquier otra condición lógica.

Sintaxis de ALL

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

Aquí:

• array_column — es el array que estamos comprobando.
• value < ALL(...) significa que todos los elementos del array deben ser mayores que el valor.

Volvamos a nuestra tabla students. Supón que queremos encontrar estudiantes cuyos hobbies todos empiezan por la letra "l". Consulta:

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

El resultado será vacío, porque nadie tiene todos los hobbies iguales a "lectura". Para que el ejemplo sea más claro (y funcione), pensemos en datos numéricos.

Supón que tienes una tabla orders:

Buscar todos los pedidos donde cada posición cuesta menos de 400:

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

Resultado:

Filtrar filas por el contenido del array

Ya hemos visto las funciones y operadores básicos para buscar datos en arrays, pero ¿cómo se usan en la práctica? Vamos a ver algunos ejemplos de uso.

Ejemplo 1: Buscar estudiantes que tengan al menos un hobby que sea música

Tabla original:

SELECT *
FROM students
WHERE 'música' = ANY(hobbies);

Resultado:

Ejemplo 2: Buscar pedidos con precios mayores de 100 en todas las posiciones

Tabla original orders:

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

Resultado:

Errores típicos y cómo evitarlos

Cuando trabajas con arrays, sobre todo para buscar datos, hay varias trampas en las que puedes caer.

Error: Uso incorrecto de ANY o ALL. Por ejemplo, si te confundes con los operadores, puedes obtener resultados inesperados.

-- Variante incorrecta:
SELECT *
FROM students
WHERE hobbies = ANY('lectura');

En este ejemplo el error es que hobbies es un array y 'lectura' es una cadena. Es el array el que debe comprobarse con ANY, no al revés.

Error: La indexación de arrays empieza en 1, no en 0. Si usas arrays en otras funciones o intentas sacar elementos manualmente, ten en cuenta cómo funciona PostgreSQL.

Error: Falta de indexación. Si usas arrays para búsquedas frecuentes (ANY, ALL), poner índices puede acelerar mucho las consultas.