TopolearnJSON and Parquet data is often nested, and DuckDB can process it without flattening everything first.
DuckDB has first-class nested types:
LIST(e.g.INTEGER[]): ordered, variable length; literal[1, 2, 3]; indexing is 1-based,l[1]STRUCT: named fields; literal{'name': 'Ann', 'age': 34}; access withs.nameors['name']MAP: key/value pairs with uniform types;MAP {'a': 1};m['a']ARRAY(e.g.FLOAT[3]): fixed-length lists, used for embeddings and vector functions
unnest(list) turns a list into rows; unnest(struct) turns fields into columns, and unnest(..., recursive := true) flattens deeply. Going the other way, list(x) (alias array_agg) aggregates rows into a list, and struct_pack builds structs.
List functions avoid unnesting entirely: len, list_contains, list_sort, list_distinct, and higher-order functions with lambdas: list_transform(l, x -> x * 2), list_filter(l, x -> x > 0), list_reduce. There is even Python-style list comprehension syntax: [x * 2 FOR x IN l IF x > 0].
Reading JSON with read_json produces structs and lists automatically, so SELECT payload.user.id FROM 'events.json' works out of the box.
Note that recent versions are introducing Python-style lambda x: x * 2 syntax; the arrow form still works but may produce deprecation warnings in newer releases.
Example
import duckdb
duckdb.sql("""
CREATE TABLE posts AS SELECT * FROM (VALUES
(1, ['sql', 'duckdb'], {'name': 'Ann', 'age': 34}),
(2, ['python'], {'name': 'Bob', 'age': 19})) t(id, tags, author)
""")
print(duckdb.sql("SELECT id, author.name, len(tags) FROM posts").fetchall())
# [(1, 'Ann', 2), (2, 'Bob', 1)]
print(duckdb.sql("SELECT id, unnest(tags) AS tag FROM posts ORDER BY ALL").fetchall())
# [(1, 'duckdb'), (1, 'sql'), (2, 'python')]
print(duckdb.sql("SELECT [upper(t) FOR t IN tags IF t != 'sql'] FROM posts").fetchall())
# [(['DUCKDB'],), (['PYTHON'],)]Remember
- LIST, STRUCT, MAP and ARRAY are native column types
unnestexpands lists to rows;list()aggregates rows to a list- List lambdas and comprehensions transform nested data without unnesting
- List indexes start at 1
Watch out for
- Using 0-based indexing out of habit:
l[0]is not the first element.