Examples of Advanced Queries with Arrays: Aggregation, Filtering, Sorting

Today, we're gonna figure out how to squeeze the most out of arrays in your queries: grouping values, filtering by what's inside, and even sorting right inside arrays. This isn't just theory for theory's sake—these tricks pop up in reports, analytics, personalization, and tons of real-life scenarios. It's all pretty simple once you get the idea—and that's exactly what we're gonna do now.

Aggregating Data with Arrays

Working with arrays really shines when you need to group data. Instead of getting a bunch of rows, you can collect the values you want into one neat array. That makes analysis easier, keeps your results compact, and often saves you from writing extra subqueries. Let's see how this works in practice.

Example 1: Grouping Data into Arrays with array_agg()

When you want to collect values from several rows in one group into an array, array_agg() is your go-to. It's probably the most useful function for handling arrays when aggregating.

-- We have a students table with fields id, name, and course
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

-- Insert a few rows
INSERT INTO students (name, course) VALUES
('Alice', 'Mathematics'),
('Bob', 'Mathematics'),
('Charlie', 'Physics'),
('Dave', 'Physics'),
('Emma', 'Mathematics');

-- Group students by course into arrays
SELECT course, array_agg(name) AS students
FROM students
GROUP BY course;

Result:

Grouping values into arrays is super handy if you want to pass data in a format that's easy to parse, like JSON.

Example 2: Creating Nested Arrays

But what if we've got another table and want to collect data from both tables into arrays? For example, a courses table with info about teachers.

-- Create a teachers table
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    teacher VARCHAR(100)
);

-- Insert data
INSERT INTO courses (name, teacher) VALUES
('Mathematics', 'Prof. Min'),
('Physics', 'Prof. Peterson');

-- Nested query to create 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;

Result:

Now we've got a nice table showing courses, their teachers, and students in array format.

Filtering Data with Arrays

Arrays by themselves are already a powerful tool, but the real magic starts when you learn to filter data based on them. Need to pick only users whose list of interests includes a certain word? Or orders where every price is above a certain threshold? You can do all that right in SQL—no extra app logic needed.

Example 1: Filtering Rows by Array Elements

Let's say we want to find all rows where an array contains a certain value, like students enrolled in a math course.

-- Filter students enrolled in courses using `ANY`
SELECT *
FROM students
WHERE course = ANY(ARRAY['Mathematics', 'Physics']);

Here, ANY lets us specify an array of values, and the query returns rows where course matches at least one value in the array.

Example 2: Checking for Array Overlap

Now let's say we've got a student_interests table, where students' interests are stored as arrays. We want to find students whose interests overlap with our criteria.

-- Create a table with students' interests
CREATE TABLE student_interests (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    interests TEXT[]
);

-- Insert data
INSERT INTO student_interests (name, interests) VALUES
('Alice', ARRAY['programming', 'music']),
('Bob', ARRAY['sports', 'programming']),
('Charlie', ARRAY['reading', 'photography']),
('Emma', ARRAY['music', 'sports']);

-- Find students interested in programming or music
SELECT *
FROM student_interests
WHERE interests && ARRAY['programming', 'music'];

The && operator checks if two arrays overlap. If at least one element from the left array matches the right array, the row passes the filter.

Result:

Sorting Arrays

Sometimes the order of values inside an array matters—especially if you're building an array from different rows or want to prep data for display. PostgreSQL lets you sort elements right in your query, no extra processing needed.

Example 1: Sorting Values Inside an Array

Sometimes you need to sort elements inside an array. For example, let's sort students' interests alphabetically.

-- Sort array elements using the `array_sort()` function
SELECT 
    name,
    array_sort(interests) AS sorted_interests
FROM 
    student_interests;

Result:

Example 2: Sorting Rows by Array Length

Now let's say we want to order students by how many interests they have—from the most passionate to the most "boring".

-- Sort rows by array length
SELECT 
    name, 
    interests, 
    array_length(interests, 1) AS interests_count
FROM 
    student_interests
ORDER BY 
    interests_count DESC;

Result:

Even though all students have the same number of interests in this example, you can tweak a similar query for bigger tables.

2

Task

SQL SELF, level 36, lesson 3

Locked

Grouping values into arrays

Grouping values into arrays

Open