CODEWITHSUNDEEP
google-bigqueryunnest
ErrHuman
ErrHuman

Reputation: 345

Unnest STRUCT with double array in BigQuery

I'm trying to find a way to share some sample data ('Some_Data' below) when sharing a SQL snippet in order to allow for demonstrating the functionality.

I know I could do this with a series of UNION ALL statements, but I'd like to reduce the line count (not that this matters in this example, but imagine we have 20+ rows).

I thought I could do this using the arrays and structures and I've managed to get this working, but the code looks really messy and I was hoping someone could suggest how I could simplify this?

WITH
  Some_Data AS (
  SELECT
  [STRUCT
    (
        ["a", "b"] AS letters,
        [1, 2] AS numbers 
    )
  ] AS Data_Sample  
  )

SELECT t_numbers, t_letters FROM 
((
    SELECT letters, numbers
    FROM Some_Data  s
    CROSS JOIN UNNEST(s.Data_Sample)
)) t
CROSS JOIN UNNEST(t.numbers) as t_numbers WITH OFFSET nm 
LEFT JOIN UNNEST(t.letters) as t_letters WITH OFFSET lt 
ON nm = lt

Output (as expected):

Row t_numbers t_letters
1 1 a
2 2 b

Upvotes: 0

Views: 566

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

I was hoping someone could suggest how I could simplify this?

Consider below approach (looks to me much simpler and less verbose)

select number, letter
from Some_Data t, t.Data_Sample el, 
el.letters letter with offset
join el.numbers number with offset 
using(offset)

if applied to sample data in your question - output is

enter image description here

Upvotes: 1

Related Questions