After my previous STRUCTS in BigQuery post, I could not have skipped to mention number of options when it comes to construct one.

You can choose whether to provide field type, field name or both.

There's 3 main ways:
- via tuple ('a', 1) - BQ creates a STRUCT and infers the field types from provided values
- untyped STRUCT('a', 1) - untyped here means you'd don't declare the type, but it is rather inferred from the value literal or the column provided as input
- typed STRUCT<STRING, INT64) ('a',1) - you declare the types of the fields in the structs

There's a couple of things to be kept in mind.

➡ If you don't provide a field name it would be an anonymous field, meaning you won't be able to access it by field name
➡ If you want explicit types + field names you need to declare the field names together with the types

⚠ Watch out: ordering of fields matters in a STRUCT, so for example

SELECT STRUCT(1 AS a, 2 AS b)
UNION ALL
SELECT STRUCT (2 as b, 1 as a)

won't match fields according to names!

Found it useful? Check out to my Analytics newsletter at notjustsql.com.