Array & Nested Data Types

Advanced SQL

Arrays in SQL

Arrays let you store ordered collections within a single column — tags, skills, ordered lists. They're powerful but come with trade-offs: arrays work well for simple lists, but normalized tables are better for complex queries and joins.

PostgreSQL — ARRAY[], unnest(), array_agg(), GIN indexing
BigQuery — ARRAY, UNNEST, ARRAY_AGG
Search — @>, &&, ANY(), array containment
Transformation — unnest, aggregate, slice, replace

Arrays are perfect for denormalized storage when the collection doesn't need its own joins or filtering.

Array Operations Visualized

PostgreSQL Array Fundamentals

-- Array column operations
SELECT
  id,
  tags,
  array_length(tags, 1) AS tag_count,    -- Length
  tags[1] AS first_tag,                   -- Index (1-based!)
  array_append(tags, 'new_tag') AS added,
  array_remove(tags, 'deprecated') AS removed,
  array_cat(tags, ARRAY['extra']) AS concatenated
FROM posts;

ℹ️

Key Insight: PostgreSQL arrays are 1-indexed (unlike most languages). Use array_length() for count, array_append()/array_remove() for modification, and unnest() to expand arrays into rows.

Unnesting Arrays

-- Expand array to rows
SELECT post_id, unnest(tags) AS tag FROM posts;

-- With ordinality for position tracking
SELECT
  post_id,
  tag,
  ordinality AS position
FROM posts,
LATERAL unnest(tags) WITH ORDINALITY AS t(tag, ordinality);

-- Multiple arrays in parallel
SELECT post_id, t.tag, s.score
FROM posts,
LATERAL unnest(tags) AS t(tag),
LATERAL unnest(scores) AS s(score)
WHERE array_length(tags, 1) = array_length(scores, 1);

Array Aggregation

-- Aggregate values into array
SELECT
  department_id,
  array_agg(employee_name ORDER BY salary DESC) AS employees_by_salary,
  array_agg(DISTINCT skill) AS unique_skills,
  array_remove(array_agg(skill), NULL) AS all_skills_no_nulls
FROM employees
GROUP BY department_id;

-- Aggregate with filtering
SELECT
  department_id,
  array_agg(employee_name) FILTER (WHERE salary > 80000) AS high_earners
FROM employees
GROUP BY department_id;

Array Searching

-- Check if array contains element
SELECT * FROM posts WHERE 'sql' = ANY(tags);

-- Check if array contains all elements (containment)
SELECT * FROM posts WHERE tags @> ARRAY['sql', 'interview'];

-- Check if arrays overlap
SELECT * FROM posts WHERE tags && ARRAY['sql', 'python'];

-- Array position
SELECT post_id, array_position(tags, 'sql') AS sql_position
FROM posts WHERE 'sql' = ANY(tags);

⚠️

Indexing Tip: Create a GIN index for array containment queries: CREATE INDEX idx_tags ON posts USING GIN (tags);. This significantly speeds up @> and && operations — from O(n) sequential scan to O(log n) index lookup.

Array Transformation

-- Transform array elements
SELECT
  post_id,
  array(SELECT upper(unnest(tags))) AS uppercase_tags,
  array(SELECT DISTINCT unnest(tags) ORDER BY 1) AS sorted_unique,
  array(SELECT unnest(tags) WHERE length(unnest(tags)) > 3) AS long_tags
FROM posts;

-- Array slicing
SELECT
  post_id,
  tags[1:3] AS first_three_tags,  -- Elements 1-3
  tags[2:] AS skip_first_tag,     -- Elements 2 to end
  tags[:2] AS first_two_tags      -- Elements 1-2
FROM posts;

BigQuery Array Functions

-- BigQuery ARRAY operations
SELECT
  id,
  ARRAY_LENGTH(tags) AS tag_count,
  tags[OFFSET(0)] AS first_tag,        -- 0-indexed!
  ARRAY_CONCAT(tags, ['new_tag']) AS added,
  ARRAY(
    SELECT DISTINCT tag
    FROM UNNEST(tags) AS tag
    ORDER BY tag
  ) AS unique_sorted_tags
FROM `project.dataset.posts`;

-- ARRAY_AGG with filtering
SELECT
  department_id,
  ARRAY_AGG(name ORDER BY salary DESC LIMIT 3) AS top_3_earners
FROM employees
GROUP BY department_id;

Array Comparisons

-- Compare arrays with CASE
SELECT
  post_id, tags,
  CASE
    WHEN tags = ARRAY['sql', 'interview'] THEN 'exact match'
    WHEN tags @> ARRAY['sql'] THEN 'contains sql'
    WHEN tags && ARRAY['sql', 'python'] THEN 'overlap'
    ELSE 'no match'
  END AS match_type
FROM posts;

Quiz: Test Your Knowledge

Follow-Up Questions

When should you use arrays vs normalized tables for storing collections?
How do you create and use custom array types in PostgreSQL?
What's the performance impact of GIN indexes on array columns?
How would you implement array intersection and difference operations?
Explain the difference between unnest() and LATERAL unnest().

Key Takeaways

PostgreSQL arrays are 1-indexed — tags[1] is the first element
unnest() expands arrays into rows; array_agg() aggregates rows into arrays
GIN index for @> and && operators — dramatically faster than sequential scan
ANY() checks if a value exists in an array; @> checks containment
BigQuery uses 0-indexed arrays with OFFSET(0)
Consider normalized tables when you need joins, filtering, or complex queries on array elements

Array & Nested Data Types — PostgreSQL Arrays, BigQuery ARRAYS, Collection Processing