ARRAY: Ordered Collections

An array is 1 column holding an ordered list of values that share a type. The list can be empty, hold 1 value or hold 200, and each row decides its own length. Positions start at 1 in the SQL used here. Two shapes cover most uses: a list of scalars, such as a listing's amenities, and a list of structs, such as its photos, which is a child table folded into the parent row.

CREATE TABLE listings (
  listing_id BIGINT,
  title VARCHAR,
  amenities ARRAY(VARCHAR),
  photos ARRAY(ROW(url VARCHAR, caption VARCHAR))
)

Reading an array in place covers 3 operations: how long it is, what sits at a position, and whether it contains a value.

SELECT
  listing_id,
  cardinality(amenities) AS amenity_count,
  element_at(amenities, 1) AS first_amenity,
  contains(amenities, 'dishwasher') AS has_dishwasher
FROM listings

UNNEST: back to rows

The guest's dishwasher filter runs on the array in place, with WHERE contains(amenities, 'dishwasher'). A question across listings, such as which amenities are most common, wants 1 row per amenity instead. UNNEST produces exactly that, 1 output row per element, and the CROSS JOIN pairs each element with its parent listing:

SELECT
  l.listing_id,
  a.amenity
FROM listings l
CROSS JOIN UNNEST(l.amenities) AS a(amenity)

Listing 503 contributes 0 rows and vanishes from the output, which is correct for counting amenities and a trap for counting listings. From the unnested rows, the most common amenities is an aggregate over those rows:

SELECT
  a.amenity,
  count(*) AS listings_with_it
FROM listings l
CROSS JOIN UNNEST(l.amenities) AS a(amenity)
GROUP BY a.amenity
ORDER BY listings_with_it DESC

Order is part of the data

The photos array is ordered because the host chose which picture comes first. Unnesting an array of structs spreads each struct's fields into columns, and WITH ORDINALITY adds the position so the order survives the trip to rows:

SELECT
  l.listing_id,
  p.url,
  p.pos
FROM listings l
CROSS JOIN UNNEST(l.photos) WITH ORDINALITY AS p(url, caption, pos)

Position 1 is the cover photo. Had the photos lived in a separate table, that fact would need a sort_order column, an ORDER BY on every read, and a uniqueness rule on (listing_id, sort_order) to keep 2 photos off position 1. The array gives all 3 by construction.

What an array will not enforce

• Uniqueness: ARRAY['wifi', 'wifi'] is a valid value. Deduplicate on write with array_distinct.
• A ceiling: an embedded review list can reach 20,000 elements and make every read of that row expensive.
• Referential checks: an amenity is a string, and 'Wifi' and 'wifi' are 2 different values to contains.

Listing 503 has amenities = ARRAY[]. What does CROSS JOIN UNNEST(l.amenities) produce for it?

• One row with a NULL amenity, so the listing stays countable in the result
• One row with an empty string, since the array itself exists on the row
• An error, because UNNEST requires at least 1 element before it can expand
• Zero rows, so the listing disappears from the result entirely

About this lesson

This section is part of the Nested Data lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.