Datawise
BigQuery Arrays & Structs

SELECT AS STRUCT and SELECT AS VALUE

Constantin LunguUpdated 2 min read

Photo by Greg Rosenke on Unsplash

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.

Input data: three orders (order_id 1 to 3, stores STORE_UK, STORE_FR and STORE_UK, customers 1001 and 1002 from UK and FR), each with two nested order lines of product_id, order_quantity and unit_price.

SELECT AS VALUE order_line

FROM input_data
LEFT JOIN UNNEST(order_lines) AS order_line

BigQuery results of SELECT AS VALUE: six rows with plain columns product_id, order_quantity and unit_price, for example 10001, 5, 40.0 and 10002, 2, 50.0.

SELECT AS STRUCT order_line

FROM input_data
LEFT JOIN UNNEST(order_lines) AS order_line

BigQuery results of SELECT AS STRUCT: the same six rows, but the column headers keep the struct prefix (o... product_id, order_quantity, or... unit_price), circled in red.

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