Accessing nested data (UNNEST)

Concepts covered: Array Flattening

Nested types keep related values inside a single row, which is compact to store but awkward to query, since ORDER BY and CAST, like every other expression you know, operate on rows and columns. UNNEST converts the nested shape into the flat one. You have used it on a plain array; the same operator also expands maps and arrays of records, and each kind returns a different set of columns.

A MAP unnests into key and value

Pass UNNEST a map and it returns a row for each key-value pair, as 2 columns you name in the alias. Each output row keeps its parent's columns, so view_id repeats for every pair. Predict how many rows view 203 produces before you run it.

SELECT
  view_id,
  prop_key,
  prop_value
FROM view_props
CROSS JOIN UNNEST(props) AS p(prop_key, prop_value)
ORDER BY view_id, prop_key

ROW fields and arrays of records

A ROW bundles named fields of different types into a single value, much like a small record. Read a field with a dot: device.os. An ARRAY of rows is the common shape for a list of records, such as the reels watched in a session, and UNNEST spreads each record's fields into separate columns. Adding WITH ORDINALITY appends a further column holding each element's position, counted from 1.

SELECT
  session_id,
  os,
  watch_order,
  video_id,
  watch_ms
FROM sessions
CROSS JOIN UNNEST(reels) WITH ORDINALITY AS r(video_id, watch_ms, watch_order)
ORDER BY session_id, watch_order

Session s-1 becomes 3 rows, a row per reel, each carrying its session's os. The watch_order column preserves viewing order, which matters because SQL guarantees no row order without ORDER BY, and nothing else in the unnested rows records it.

Predict the row count before trusting it

UNNEST yields a row for each element, so the output holds as many rows as all the arrays combined, with every parent column repeated on each. A session with 50 reels repeats its session_id and device 50 times, which adds up before a large unnest. A parent whose array is empty or NULL produces no rows at all, so it drops out of the result.

SELECT
  video_id,
  hashtag
FROM reel_tags
CROSS JOIN UNNEST(hashtags) AS t(hashtag)
ORDER BY video_id, hashtag

Video 9002 has an empty hashtags array, so it's absent from the output. When every parent row must appear, this pattern loses rows you need; an outer form that keeps them arrives with the outer join lessons.

How many rows does this query return?

WITH sessions AS (
  SELECT
    's-7' AS session_id,
    ARRAY[11, 12, 13] AS video_ids
  UNION ALL
  SELECT
    's-8',
    CAST(ARRAY[] AS ARRAY(INTEGER))
  UNION ALL
  SELECT
    's-9',
    ARRAY[12, 14]
)
SELECT
  session_id,
  video_id
FROM sessions
CROSS JOIN UNNEST(video_ids) AS v(video_id)

• Three rows, a row for each session
• Six rows, including a NULL row for s-8
• Four rows, since the repeated video 12 is merged
• Five rows, a row for each element of every array

The alias after UNNEST names every column it produces, so the number of names is fixed by what is being expanded: 1 for an array of plain values, 2 for a map's keys and values, 1 per field for an array of rows, and 1 more with WITH ORDINALITY. A name list that does not match the column count fails the query before any row is read.

Using reel_views, whose client_props column is a MAP(VARCHAR, VARCHAR), produce a row per attribute with the columns view_id, prop_key and prop_value, ordered by view. Two tiles do not belong.

About this lesson

This section is part of the Data Types: Advanced 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.