UNNEST in BigQuery

BigQuery is a scalable and highly efficient data warehouse solution. As we move more and more existing databases into BigQuery with existing structure, some data types are little difficult to query and manipulate, such as “struct” data type.

For example, we may get a data row that may look like this.

In order to query and filter records based on Field, we can use a query similar to below –

SELECT * FROM TABLE_NAME WHERE (SELECT c.value FROM UNNEST(Field) AS c WHERE c.key='key1') = 'value1'

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s