Constructing BigQuery STRUCTs: Tuple, Untyped, and Typed

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!

No alt text provided for this image

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

Enjoyed this? Here are some related articles you might find useful:

Constructing STRUCTS in BigQuery

Comments

BigQuery Arrays & Structs

Pay attention to cardinality & grain when UNNESTING in BigQuery!

More from this blog

pre_ and post_operations in Dataform

BigQuery Object Tables: A Practical Introduction

BigQuery BigLake Tables Explained: What They Are and When to Use Them

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

Command Palette

Comments

BigQuery Arrays & Structs

Pay attention to cardinality & grain when UNNESTING in BigQuery!

More from this blog