AdvancedString Functions

Array aggregation and unnesting patterns

The query

SQL

-- Aggregate to array
SELECT
  dept_id,
  ARRAY_AGG(first_name ORDER BY first_name) AS employee_names,
  ARRAY_AGG(salary ORDER BY salary DESC) AS salaries
FROM employees
GROUP BY dept_id;

-- Unnest: expand arrays back to rows
SELECT dept_id, UNNEST(employee_names) AS employee_name
FROM (
  SELECT dept_id, ARRAY_AGG(first_name) AS employee_names
  FROM employees GROUP BY dept_id
) t;

Tested against PostgreSQL 16

Note

“ARRAY_AGG creates arrays from grouped rows. UNNEST expands back to rows. Useful for data reshaping.

Tables referenced

employees10 cols