SELECT AS STRUCT and SELECT AS VALUE
Senior Data Engineer • Contractor / Freelancer • GCP & AWS Certified
Ever heard about value tables in BigQuery? Well, neither have I, until I've seen them mentioned in the docs. So, while in a normal table, a row is made up of columns, in a value table the row is a STRUCT.
Say you UNNEST order_lines AS order_line. The 'order_line' here is a value table.
Now, with this out of the way, let's see how it is useful, we need to introduce SELECT AS STRUCT and SELECT AS VALUE.
➡ SELECT AS STRUCT - produces a value table but preserves the STRUCT type i.e. you're still going to have order_line.product_id or order_line.unit_price
➡ SELECT AS VALUE - produces a value table by unpacking the struct values
When can these be useful?
☑ You have a STRUCT with a lot of attributes and don't want to reference them manually
☑ You want to create a separate table out of the values in a STRUCT, without unpacking each column
☑ Use this as input for creating an array of STRUCTS
☑ You're using ARRAY_AGG to de-duplicate (see example in comments).
See below for an illustration of the differences between the two.
Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.
Enjoyed this? Here are some related articles you might find useful:
