3 ms·

Nobody mentioned nested data in BigQuery? It's a superset of SQL where fields can be arrays of structs (so basically nested tables) and there's some extra synta

Nobody mentioned nested data in BigQuery? It's a superset of SQL where fields can be arrays of structs (so basically nested tables) and there's some extra syntax to nest/unnest things, so you can often get everything you wanted in one result set.

foota 1y ago
Yes, see https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_as_struct https://cloud.google.com/bigquery/docs/reference/standard-sq... and https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest_operator https://cloud.google.com/bigquery/docs/reference/standard-sq.... Very pleasant compared to normal SQL. Table-valued functions (I think these are not exclusive to GoogleSQL?) are also very nice. Every time I see scripts with a long block of with statements part of me dies inside.
deepsun 1y ago
PostgreSQL (what article author uses) supports nested data (see Composite Types). You an even define a type yourself for type safety.
mccanne 1y ago
For sure... the use of JSON here seems orthogonal to Jamie's point of constructing complex nested values in SQL with a single backend query. Modern SQLs support all this as native types (presuming the result can fit in a homogeneous relational type, which it can in this case), e.g., Jamie's query can be written with a record expression (duckdb dialect): with theTitle as ( from title.parquet where tconst = 'tt3890160' ), principals as ( select array_agg({id:principal.nconst,name:primaryName,category:category}) from principal.parquet, person.parquet where principal.tconst = (from theTitle select tconst) and person.nconst = principal.nconst ), characters as ( select array_agg(c.character) as characters, p.u.name from principal_character.parquet c join (select unnest((from principals)) as u) p on c.character is not null and u.id=c.nconst and c.tconst=(select tconst from theTitle) group by p.u ) select { title: (select primaryTitle from theTitle), director: list_transform( list_filter((from principals), lambda elem: elem.category='director'), lambda elem: elem.name), writer: list_transform( list_filter((from principals), lambda elem: elem.category='writer'), lambda elem: elem.name), genres: (select genres from theTitle), characters: (select array_agg({name:name,characters:characters}) from characters), } as result And if you query typeof on the result, you'll get: STRUCT( title VARCHAR, director VARCHAR[], writer VARCHAR[], genres VARCHAR, characters STRUCT( "name" VARCHAR, characters VARCHAR[] )[] )

foota 1y ago

Yes, see https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_as_struct https://cloud.google.com/bigquery/docs/reference/standard-sq... and https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest_operator https://cloud.google.com/bigquery/docs/reference/standard-sq.... Very pleasant compared to normal SQL. Table-valued functions (I think these are not exclusive to GoogleSQL?) are also very nice. Every time I see scripts with a long block of with statements part of me dies inside.

deepsun 1y ago

PostgreSQL (what article author uses) supports nested data (see Composite Types). You an even define a type yourself for type safety.

mccanne 1y ago

For sure... the use of JSON here seems orthogonal to Jamie's point of constructing complex nested values in SQL with a single backend query. Modern SQLs support all this as native types (presuming the result can fit in a homogeneous relational type, which it can in this case), e.g., Jamie's query can be written with a record expression (duckdb dialect): with theTitle as ( from title.parquet where tconst = 'tt3890160' ), principals as ( select array_agg({id:principal.nconst,name:primaryName,category:category}) from principal.parquet, person.parquet where principal.tconst = (from theTitle select tconst) and person.nconst = principal.nconst ), characters as ( select array_agg(c.character) as characters, p.u.name from principal_character.parquet c join (select unnest((from principals)) as u) p on c.character is not null and u.id=c.nconst and c.tconst=(select tconst from theTitle) group by p.u ) select { title: (select primaryTitle from theTitle), director: list_transform( list_filter((from principals), lambda elem: elem.category='director'), lambda elem: elem.name), writer: list_transform( list_filter((from principals), lambda elem: elem.category='writer'), lambda elem: elem.name), genres: (select genres from theTitle), characters: (select array_agg({name:name,characters:characters}) from characters), } as result And if you query typeof on the result, you'll get: STRUCT( title VARCHAR, director VARCHAR[], writer VARCHAR[], genres VARCHAR, characters STRUCT( "name" VARCHAR, characters VARCHAR[] )[] )