Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save paslandau/03c73ee5eef2ce217af82a8f7edcb125 to your computer and use it in GitHub Desktop.
Save paslandau/03c73ee5eef2ce217af82a8f7edcb125 to your computer and use it in GitHub Desktop.
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
Copy link
Author

paslandau commented May 29, 2020

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