Constructing STRUCTS in BigQuery
Senior Data Engineer • Contractor / Freelancer • GCP & AWS Certified
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.
Enjoyed this? Here are some related articles you might find useful:
