Extracting Data from Arrays: unnest(), array_length(), array_position()

Working with arrays isn't just about creating and storing them — you often need to pull out individual elements or analyze what's inside. PostgreSQL gives you a bunch of built-in functions for these operations. Let's check them out one by one.

The unnest() Function: Unpacking an Array

The unnest() function literally unpacks an array, turning its elements into separate rows. This is super handy if you want to work with array data in a table format.

Example 1: Simple Array Unpacking

Let's say we have an array with faculty names:

SELECT ARRAY['Computer Science', 'Mathematics', 'Physics'] AS faculties;

Now we want to pull out each element as a separate row. Let's use unnest():

SELECT unnest(ARRAY['Computer Science', 'Mathematics', 'Physics']) AS faculty;

Result:

Example 2: Unpacking an Array in a Table

Suppose we have a courses table:

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name TEXT,
    tags TEXT[]
);

INSERT INTO courses (course_name, tags)
VALUES
    ('Algorithms', ARRAY['Programming', 'Computer Science']),
    ('Linear Algebra', ARRAY['Mathematics', 'Algebra']),
    ('Physics Basics', ARRAY['Physics', 'General']);

Now let's extract all tags from the array:

SELECT course_name, unnest(tags) AS tag
FROM courses;

Result:

You can see that each array element becomes a separate row in the table.

The array_length() Function: Figuring Out Array Size

The second important function for working with arrays is array_length(). It returns the length of the array (meaning the number of elements) for the given dimension.

Example 1: Number of Elements in a One-Dimensional Array

Let's take an array:

SELECT ARRAY['Apple', 'Banana', 'Orange'] AS fruits;

If we want to know how many fruits are in the array:

SELECT array_length(ARRAY['Apple', 'Banana', 'Orange'], 1) AS length;

Result:

Here, 1 points to the array dimension. Arrays in PostgreSQL can have multiple dimensions (like two-dimensional arrays), but that's a topic for another time.

Example 2: Number of Elements in an Array Column

Let's see how many tags each course has:

SELECT course_name, array_length(tags, 1) AS tag_count
FROM courses;

Result:

The function just says, “Hey, there are two elements here!” — and that already makes data analysis a lot easier.

The array_position() Function: Searching for a Value in an Array

Now, imagine you want to find a specific element in an array. The array_position() function comes to the rescue: it returns the position of the first occurrence of the element.

Example 1: Searching for an Element

Let's say we have an array:

SELECT ARRAY['Red', 'Blue', 'Green', 'Yellow'] AS colors;

Let's try to find the position of "Blue":

SELECT array_position(ARRAY['Red', 'Blue', 'Green', 'Yellow'], 'Blue') AS position;

Result:

If the element isn't there, the function returns NULL. Let's check:

SELECT array_position(ARRAY['Red', 'Blue', 'Green', 'Yellow'], 'Black') AS position;

Result:

Example 2: Searching in an Array Column

You want to know which course has the tag "Computer Science". First, let's find the right rows:

SELECT course_name, array_position(tags, 'Computer Science') AS position
FROM courses;

Result:

Now add a filter to keep only the rows where the tag exists:

SELECT course_name
FROM courses
WHERE array_position(tags, 'Computer Science') IS NOT NULL;

Result:

The array_position() function lets you quickly find stuff inside an array, which makes it one of the key tools for working with arrays in PostgreSQL.

Practical Use of These Functions

• unnest() — use it to turn arrays into rows. This is important for data analysis, building reports, and working with tags.
• array_length() — perfect for checking array length. For example, you can use it for data validation: making sure the array isn't empty.
• array_position() — a great tool for searching elements, whether it's product categories, student participation in a project, or keywords in a description.

Common Mistakes When Using These Functions

1. unnest() can double the number of rows if you use it on arrays in multiple columns at once. You can fix this by adding JOIN LATERAL or CROSS JOIN, but keep an eye out for it.
2. array_length() returns NULL for an empty array. If your array might be empty, check for that separately.
3. array_position() can return NULL if the element isn't found. So it's always a good idea to handle that case in your filters (IS NOT NULL).

Real-World Examples

Arrays in PostgreSQL aren't just some theoretical thing — they're a tool that works great in real projects. For example, if you're running a blog and each post has a list of tags, arrays make it easy to filter posts by topic or even build a top list of popular categories.

Or, say, you're analyzing user behavior where people pick several courses or products they're interested in. All those preferences are easy to store in an array — and just as easy to process.

And arrays help with data validation too: with array_length() you can, for example, limit the number of elements — like making sure a user doesn't pick more than five options.