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.