Array Functions

The Array Types page covered declaring array columns and the basic literal syntax. In practice, working with arrays means calling a handful of functions over and over: measuring and modifying them, checking for overlap, and — most importantly — expanding an array column into a set of ordinary rows so it can be joined, grouped, and aggregated like any other relational data. This page works through those functions with real examples.

Measuring and modifying an array

Function	Purpose
ARRAY_LENGTH(arr, dim)	Number of elements along the given dimension (1 for a simple array)
ARRAY_APPEND(arr, elem)	Returns a new array with elem added at the end
ARRAY_PREPEND(elem, arr)	Returns a new array with elem added at the start
ARRAY_REMOVE(arr, elem)	Returns a new array with every occurrence of elem removed
ARRAY_POSITION(arr, elem)	Index of the first occurrence of elem, or NULL if absent
CARDINALITY(arr)	Total number of elements, across all dimensions

Basic array manipulation

SQL

SELECT
  ARRAY_LENGTH(ARRAY[10, 20, 30], 1)            AS len,        -- 3
  ARRAY_APPEND(ARRAY['red', 'blue'], 'green')   AS appended,   -- {red,blue,green}
  ARRAY_PREPEND('yellow', ARRAY['red', 'blue']) AS prepended,  -- {yellow,red,blue}
  ARRAY_REMOVE(ARRAY[1, 2, 2, 3], 2)            AS removed;    -- {1,3}

UNNEST(): expanding an array into rows

UNNEST() is the single most important array function in everyday use. An array column packs multiple values into one cell, which is convenient for storage but awkward the moment you need to filter, join, or group by an individual element. UNNEST() takes an array and expands it into one row per element — turning array data back into ordinary rows that the rest of SQL already knows how to work with.

Expanding a tags array into rows

SQL

CREATE TABLE articles (
  article_id SERIAL PRIMARY KEY,
  title      TEXT NOT NULL,
  tags       TEXT[]
);

INSERT INTO articles (title, tags) VALUES
  ('Intro to Indexing',   ARRAY['postgresql', 'performance', 'sql']),
  ('JSONB Deep Dive',     ARRAY['postgresql', 'json']);

SELECT article_id, title, UNNEST(tags) AS tag
FROM articles;

 article_id | title              | tag
------------+--------------------+-------------
          1 | Intro to Indexing  | postgresql
          1 | Intro to Indexing  | performance
          1 | Intro to Indexing  | sql
          2 | JSONB Deep Dive    | postgresql
          2 | JSONB Deep Dive    | json

Once tags is expanded into one row per (article_id, tag) pair, it can be filtered, counted, or joined exactly like a normal junction-table column — no special array-aware syntax needed anywhere else in the query.

Joining unnested tags against a lookup table

SQL

SELECT a.title, t.description
FROM articles a
CROSS JOIN LATERAL UNNEST(a.tags) AS tag
JOIN tag_lookup t ON t.tag_name = tag
WHERE t.description IS NOT NULL;

ARRAY_AGG(): the reverse operation

ARRAY_AGG() is the inverse of UNNEST() — instead of expanding an array into rows, it collapses a group of rows into a single array. It is the array counterpart to functions like STRING_AGG() and jsonb_agg(), covered on the Aggregate Functions and JSONB Operations pages, and is the standard way to fold a one-to-many relationship into a single array column in a query result.

Collapsing rows back into an array

SQL

SELECT customer_id, ARRAY_AGG(order_id ORDER BY order_id) AS order_ids
FROM orders
GROUP BY customer_id;

Containment and overlap operators

Two operators from the Array Types page come up constantly once you start combining arrays with UNNEST() and joins: @> asks whether an array fully contains a given set of elements, and && asks whether two arrays share at least one element.

Containment and overlap

SQL

-- Articles tagged with both postgresql and performance
SELECT title FROM articles WHERE tags @> ARRAY['postgresql', 'performance'];

-- Articles that share at least one tag with a given interest list
SELECT title FROM articles WHERE tags && ARRAY['json', 'replication'];

Worked example: unnest plus join

Combining UNNEST() with a join is the pattern that ties everything together: it lets an array column stand in for what would otherwise require a separate junction table, while still being fully joinable. Here, each article's tags array is expanded and joined against a tags reference table to pull in a per-tag category.

Unnest + join: tag categories per article

SQL

CREATE TABLE tag_lookup (
  tag_name TEXT PRIMARY KEY,
  category TEXT NOT NULL
);

INSERT INTO tag_lookup (tag_name, category) VALUES
  ('postgresql', 'database'),
  ('performance', 'engineering'),
  ('json', 'database'),
  ('sql', 'database');

SELECT a.title, t.category, COUNT(*) AS tag_count
FROM articles a
CROSS JOIN LATERAL UNNEST(a.tags) AS tag_name
JOIN tag_lookup t ON t.tag_name = tag_name
GROUP BY a.title, t.category
ORDER BY a.title;

UNNEST() with multiple arrays

UNNEST() accepts more than one array argument at once, pairing them up positionally into a single set of rows — handy for zipping two parallel arrays (say, a list of names and a list of scores) together without a separate join.

Arrays vs. a proper junction table

UNNEST() makes array columns join-friendly, but that does not mean an array column is always the right design choice over a normalized junction table. The Relationships page covers when a real many-to-many junction table is the better fit — generally once the related data needs its own attributes, foreign keys, or independent querying.

ARRAY_APPEND / ARRAY_PREPEND / ARRAY_REMOVE modify an array without mutating the original.
UNNEST() expands an array into one row per element — the key function for making array data joinable.
ARRAY_AGG() is the reverse: collapsing grouped rows back into a single array.
@> checks containment; && checks overlap between two arrays.