Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
How to use expression subqueries to query nested and repeated fields in Google BigQuery
-- Using expression subqueries to query nested and repeated fields in Google BigQuery; 2020-05-29
-- @see http://www.pascallandau.com/bigquery-snippets/expression-subqueries-for-nested-repeated-fields/
WITH example as (
SELECT
1 as id,
[
STRUCT("foo" as key, "foo 1" as value),
STRUCT("bar" as key, "bar 1" as value)
] AS data,
UNION ALL
SELECT
2,
[
STRUCT("foo" as key, "foo 2" as value),
STRUCT("bar" as key, "bar 2" as value)
],
)
SELECT
*,
(SELECT value from e.data WHERE key = "bar") as bar_value
FROM
example e
@paslandau

This comment has been minimized.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment