How to use expression subqueries to query nested and repeated fields in Google BigQuery

expression-subqueries-for-nested-repeated-fields.sql

-- 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
  
  

Source: How to use expression subqueries to query nested and repeated fields in Google BigQuery