liveBook · Manning

Array

As explained in DuckDB in Action

An array is an ordered collection of values that can be accessed by position and may be represented as a nested data structure in formats such as JSON.

An array stores multiple values as one nested field. In DuckDB, unpacking an array turns each element into a separate row, making nested JSON easier to query. section 5.3

Flatten shot arrays

Create a reusable shots view by unnesting h and a, then query the resulting rows for analysis. A flattened view can aggregate expected goals and goals by team. 

FROM 'xg/shots_*.json'
SELECT unnest(h) AS row
UNION ALL  #1
FROM 'xg/shots_*.json'
SELECT unnest(a) AS row
LIMIT 3;  #2

Run the DuckDB query to see array elements become individual rows. section 5.3

How DuckDB in Action covers it

How deep it goes
Explains schema inspection, one-level unnesting, recursive expansion, STRUCT projection, and explicit type casting.
Examples
Demonstrates football shot arrays, flattened STRUCT rows, schema descriptions, and team-level expected-goals aggregation.
What you can do after
Apply it via DuckDB to inspect nested arrays, flatten elements, correct types, and build analytical views.
How it connects
Connects arrays with JSON, STRUCT, unnest, UNION ALL, views, and schema inference.
Cover of DuckDB in Action
liveBookmanning.com
Read DuckDB in Action on liveBook Get the book at manning.com

Also explained in

Compare how 40 books explain array →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of DuckDB in Action. The section links point to the parts of the book it draws on.