SELECT AS STRUCT and SELECT AS VALUE in BigQuery

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:

SELECT AS STRUCT and SELECT AS VALUE

Comments

BigQuery Arrays & Structs

Using ARRAY_CONCAT_AGG() 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

Using ARRAY_CONCAT_AGG() in BigQuery

More from this blog