Ejemplos de consultas avanzadas con arrays: agregación, filtrado, ordenación

Hoy vamos a ver cómo sacarle el máximo partido a los arrays en tus consultas: agrupar valores, filtrar por contenido e incluso ordenar directamente dentro de los arrays. No es teoría por teoría — estos trucos aparecen en informes, analítica, personalización y un montón de situaciones reales. Es fácil si pillas el concepto — y justo eso vamos a hacer ahora.

Agregación de datos con arrays

Trabajar con arrays brilla especialmente cuando necesitas agrupar datos. En vez de recibir varias filas — juntamos los valores que queremos en un array bien ordenado. Así el análisis es más sencillo, los resultados más compactos y muchas veces te ahorras subconsultas innecesarias. Vamos a ver cómo funciona en la práctica.

Ejemplo 1: agrupar datos en arrays con array_agg()

Cuando quieres juntar valores de varias filas en un grupo dentro de un array, array_agg() es tu colega. Es probablemente la función más útil para trabajar con arrays al agregar.

-- Tenemos una tabla students con los campos id, name y course
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

-- Insertamos varias filas
INSERT INTO students (name, course) VALUES
('Alicia', 'Matemáticas'),
('Bob', 'Matemáticas'),
('Charlie', 'Física'),
('Dave', 'Física'),
('Emma', 'Matemáticas');

-- Agrupamos estudiantes por curso en arrays
SELECT course, array_agg(name) AS students
FROM students
GROUP BY course;

Resultado:

Agrupar valores en arrays es muy útil si quieres pasar los datos en un formato fácil de procesar, por ejemplo, en JSON.

Ejemplo 2: crear arrays anidados

¿Y si tenemos otra tabla y queremos juntar datos de dos tablas en arrays? Por ejemplo, una tabla courses con info sobre los profesores.

-- Creamos la tabla de profesores
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    teacher VARCHAR(100)
);

-- Insertamos datos
INSERT INTO courses (name, teacher) VALUES
('Matemáticas', 'Prof. Min'),
('Física', 'Prof. Peterson');

-- Consulta anidada para crear arrays
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;

Resultado:

Ahora tenemos una tabla cómoda que muestra los cursos, sus profes y los estudiantes en formato array.

Filtrado de datos con arrays

Los arrays ya son una herramienta potente, pero la magia de verdad empieza cuando aprendes a filtrar datos usando ellos. ¿Quieres elegir solo los usuarios que tienen una palabra concreta en su lista de intereses? ¿O pedidos donde cada precio supera un umbral? Todo eso se puede hacer directamente en SQL — sin lógica extra en la app.

Ejemplo 1: filtrar filas por elementos de un array

Supón que quieres encontrar todas las filas donde el array contiene un valor concreto, por ejemplo, buscamos estudiantes apuntados al curso de matemáticas.

-- Filtramos estudiantes apuntados a cursos usando `ANY`
SELECT *
FROM students
WHERE course = ANY(ARRAY['Matemáticas', 'Física']);

Aquí ANY te deja poner un array de valores, y la consulta devuelve las filas donde course coincide con alguno de los valores del array.

Ejemplo 2: Comprobar intersección de arrays

Ahora imagina que tenemos una tabla student_interests, donde los intereses de los estudiantes están en arrays. Queremos encontrar estudiantes cuyos intereses se crucen con nuestros criterios.

-- Creamos la tabla con intereses de estudiantes
CREATE TABLE student_interests (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    interests TEXT[]
);

-- Insertamos datos
INSERT INTO student_interests (name, interests) VALUES
('Alicia', ARRAY['programación', 'música']),
('Bob', ARRAY['deporte', 'programación']),
('Charlie', ARRAY['lectura', 'fotografía']),
('Emma', ARRAY['música', 'deporte']);

-- Buscamos estudiantes interesados en programación o música
SELECT *
FROM student_interests
WHERE interests && ARRAY['programación', 'música'];

El operador && comprueba la intersección de dos arrays. Si al menos un elemento del array de la izquierda coincide con el de la derecha, la fila pasa el filtro.

Resultado:

Ordenación de arrays

A veces el orden de los valores dentro del array importa — sobre todo si estás juntando arrays de varias filas o quieres preparar los datos para mostrarlos. PostgreSQL te deja ordenar los elementos directamente en la consulta, sin procesar nada fuera.

Ejemplo 1: ordenar valores dentro de un array

A veces necesitas ordenar los elementos dentro de un array. Por ejemplo, vamos a ordenar el array de intereses de los estudiantes alfabéticamente.

-- Ordenamos los elementos del array con la función `array_sort()`
SELECT 
    name,
    array_sort(interests) AS sorted_interests
FROM 
    student_interests;

Resultado:

Ejemplo 2: ordenar filas por la longitud del array

Ahora imagina que queremos ordenar a los estudiantes por la cantidad de intereses que tienen — de los más "frikis" a los más "aburridos".

-- Ordenamos filas por la longitud del array
SELECT 
    name, 
    interests, 
    array_length(interests, 1) AS interests_count
FROM 
    student_interests
ORDER BY 
    interests_count DESC;

Resultado:

Aunque en el ejemplo todos los estudiantes tienen el mismo número de intereses, una consulta parecida se puede modificar para tablas más grandes.

2

Tarea

SQL SELF, nivel 36, lección 3

Bloqueada

Agrupación de valores en arrays

Agrupación de valores en arrays

Abrir