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 | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
Source: How to use expression subqueries to query nested and repeated fields in Google BigQuery