sqladvanced

Array and UNNEST Operations

Work with array columns using UNNEST, ARRAY_AGG, and array operators in PostgreSQL.

sqlPress ⌘/Ctrl + Shift + C to copy

-- Array columns
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT,
  tags TEXT[] DEFAULT '{}'
);

INSERT INTO articles (title, tags) VALUES
  ('Intro to SQL', ARRAY['sql', 'beginner', 'database']),
  ('Advanced Postgres', ARRAY['sql', 'postgres', 'advanced']);

-- Array contains value
SELECT * FROM articles WHERE 'sql' = ANY(tags);

-- Array overlap
SELECT * FROM articles WHERE tags && ARRAY['react', 'postgres'];

-- UNNEST to rows
SELECT id, title, UNNEST(tags) AS tag FROM articles;

-- Count tag frequency
SELECT tag, COUNT(*) AS usage_count
FROM articles, UNNEST(tags) AS tag
GROUP BY tag ORDER BY usage_count DESC;

-- ARRAY_AGG
SELECT department,
  ARRAY_AGG(DISTINCT name ORDER BY name) AS members
FROM employees GROUP BY department;

-- Append / remove
UPDATE articles SET tags = tags || 'featured' WHERE id = 1;
UPDATE articles SET tags = ARRAY_REMOVE(tags, 'beginner') WHERE id = 1;