Big Query Unnesting Structs

gbq_unnest.sql

with X as (
--  select STRUCT<x int64, y string>(1, 'a') as R
   select * 
--   from unnest(split("I am the bad man, I am the bad man", " ")) as w with offset as c
  from 
  unnest([
  -- Will be named and explictly typed
--           STRUCT<x int64, y string>(1, 'a'), 
--           STRUCT<x int64, y string>(2, 'b'),
--           STRUCT<x int64, y string>(3, 'c')
  -- will be named and implicity typed
--           struct(1 as n, 'a' as v), 
--           struct(2 as n, 'b' as v), 
--           struct(3 as n, 'c' as v)
  -- will be unnamed
--     (1, 'a', [4, 5, 6]),
--     (2, 'b', [5, 6, 7]), 
--     (3, 'c', [7, 8, 9])
    -- Turns f0_ into FLOAT64 
    --,(4.0, 'd') 
    
        struct(1 as n, 'a' as v, [4, 5] as a),
        struct(2 as n, 'b' as v, [5, 6] as a), 
        struct(3 as n, 'c' as v, [7, 8] as a),
        struct(4 as n, 'd' as v, [] as a)
          ])
),
Y as (
  select n, v, a
  from X
  left join unnest(a) as a
)
select 
  * 
from Y
-- select a, sum(n) as n, count(*) as rc
-- from Y
-- group by 1
--select r.x, r.y from X
-- with X as (

--   -- make some raw data that looks like it might match
--   -- the description, with some duplicates (c) in an array 
--   select 'first' as name, ['a', 'b', 'c', 'c'] as value
--   union all
--   select 'second' as name, ['d', 'b', 'c', 'c'] as value
--   union all
--   select 'third' as name, ['a', 'b', 'd', 'c'] as value
-- ),
-- Y as (
--   -- could use count() / group by instead 
--   select 
--     name, 
--     value, 
--     row_number() over (partition by name, value) as N
--   from X
--   cross join unnest(value) as value
-- )
-- select * except(N) 
-- from Y
-- where N = 1