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.